I am creating an app that creates the database when you start the program , and the first script that I make is this:
public void updateAttachment(String PID)
{
String connection = Constants.localServerConnectionSQL.LocalServerConnectionSQLName;
SqlConnection Connection = new SqlConnection();
Connection = new SqlConnection(connection);
Connection.Open();
Connection.ChangeDatabase(PID);
string script = @"CREATE PROCEDURE updateAttachment
@ATID TEXT, @NAME TEXT,@CPID TEXT,
@CREATORID text,@B64CONTENT text,
@FILESIZE int,@SERVEROFFLINE int,
@ISFAVOURITE int,@LASTOPENDATE date
AS UPDATE dbo.ATTACHMENT
SET NAME = @NAME, ATID = @ATID, CPID = @CPID,
CreatorID = @CREATORID,B64CONTENT = @B64CONTENT,
FILESIZE = @FILESIZE,SERVEROFFLINE = @SERVEROFFLINE,
ISFAVOURITE = @ISFAVOURITE,
LASTOPENDATE = @LASTOPENDATE
WHERE ATID LIKE @ATID";
SqlCommand command = new SqlCommand(script, Connection);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
And then I'm going to call the use of stored procedures using this method :
public void updateAttachments(String PID, AttachmentDownload attachment)
{
using (SqlConnection conn = new SqlConnection(Constants.localServerConnectionSQL.LocalServerConnectionSQLName))
{
conn.Open();
conn.ChangeDatabase(PID);
SqlCommand cmd = new SqlCommand("dbo.updateAttachment", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CPID", attachment.CPID));
cmd.Parameters.Add(new SqlParameter("@NAME", attachment.Name));
cmd.Parameters.Add(new SqlParameter("@ATID", attachment.ATID));
cmd.Parameters.Add(new SqlParameter("@CREATORID", attachment.CreatorID));
cmd.Parameters.Add(new SqlParameter("@B64CONTENT", attachment.B64Content));
cmd.Parameters.Add(new SqlParameter("@FILESIZE", attachment.FileSize));
cmd.Parameters.Add(new SqlParameter("@SERVEROFFLINE", attachment.ServeOffline));
cmd.Parameters.Add(new SqlParameter("@ISFAVOURITE", attachment.IsFavourite));
cmd.Parameters.Add(new SqlParameter("@LASTOPENDATE", attachment.LastOpenDate));
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
log.Info("Errore di scrittura Store Procedure: insertMeetings - Errore.: " + ex.Message);
throw ex;
}
}
}
But when I launch the script , I do not by any kind of error but nevertheless I'm not updating the records ... where ATID equals attachment.ATID . Am I wrong sql server language ?
UPDATE i remove the quote WHERE ATID LIKE '@ATID' in WHERE ATID LIKE @ATID but i have this error:
The transaction log for database 'mydbname' is full due to 'ACTIVE_TRANSACTION'
how it solve ?
-- SOLVED / SOLUTION --
The script of creation database:
String str = "CREATE DATABASE [" + PID + "] ON PRIMARY (NAME = MyDatabase_Data, FILENAME = '" + Path + ".mdf', SIZE = 10MB, MAXSIZE = 15MB, FILEGROWTH = 10%) LOG ON (NAME = [" + PID + "Log_Log], FILENAME = '"+Path+ "Log.ldf', SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 10%);";
i have changed the script db creation from
"SIZE = 1MB, MAXSIZE = 5MB"
to
"SIZE = 3MB, MAXSIZE = 10MB"
and now it work. the problem was log file space on disk.
