Problem
Um die Auto-Increment ID zu erhalten werden desöfteren 2 Statements abgesetzt, obwohl das INSERT-Statement direkt die Auto-Increment ID zurückgeben kann
Ansatz – Approach
Anstelle von comm.ExecuteNonQuery() sollte man lieber comm.ExecuteScalar() mit SELECT SCOPE_IDENTITY() kombinieren.
Lösung – Solution
public string insertMainTherapyData(string serial_no
,string therapy_start
,string machine_type
,string file_source
,string dialog_version
,string tlc_version
,string versions
,DateTime uploaded_on
,string uploaded_by
,bool processed
,string user_original_file
,string user_country
,string user_comment
,string user_upload_reason
,string user_location)
{
string sqlStatement=@"
INSERT INTO [dbo].[therapy]
([serial_no]
,[therapy_start]
,[machine_type]
,[file_source]
,[dialog_version]
,[tlc_version]
,[versions]
,[uploaded_on]
,[uploaded_by]
,[processed]
,[user_original_file]
,[user_country]
,[user_comment]
,[user_upload_reason]
,[user_location])
VALUES
(@serial_no
,@therapy_start
,@machine_type
,@file_source
,@dialog_version
,@tlc_version
,@versions
,@uploaded_on
,@uploaded_by
,@processed
,@user_original_file
,@user_country
,@user_comment
,@user_upload_reason
,@user_location);
SELECT SCOPE_IDENTITY()
";
int myID = -1;
SqlConnection conn = new SqlConnection(MyConfigurationManager.prdSqlServerString);
try
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sqlStatement;
comm.Parameters.AddWithValue("serial_no", serial_no);
comm.Parameters.AddWithValue("therapy_start", therapy_start);
comm.Parameters.AddWithValue("machine_type", machine_type);
comm.Parameters.AddWithValue("file_source", file_source);
comm.Parameters.AddWithValue("dialog_version", dialog_version);
comm.Parameters.AddWithValue("tlc_version", tlc_version);
comm.Parameters.AddWithValue("versions", versions);
comm.Parameters.AddWithValue("uploaded_on", uploaded_on);
comm.Parameters.AddWithValue("uploaded_by", uploaded_by);
comm.Parameters.AddWithValue("processed", processed);
comm.Parameters.AddWithValue("user_original_file", user_original_file);
comm.Parameters.AddWithValue("user_country", user_country);
comm.Parameters.AddWithValue("user_comment", user_comment);
comm.Parameters.AddWithValue("user_upload_reason", user_upload_reason);
comm.Parameters.AddWithValue("user_location", user_location);
myID = Convert.ToInt32(comm.ExecuteScalar());
}
catch (Exception ex)
{
return "ERROR: "+ex.Message;
}
finally
{
conn.Close();
}
return myID.ToString();
}
