{"id":2662,"date":"2018-03-22T15:48:39","date_gmt":"2018-03-22T14:48:39","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=2662"},"modified":"2018-03-22T15:48:39","modified_gmt":"2018-03-22T14:48:39","slug":"c-net-ms-sql-server-nach-insert-direkt-die-auto-increment-id-erhalten-ohne-zweite-abfrage","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=2662","title":{"rendered":"C#.NET + MS SQL Server : Nach INSERT direkt die Auto-Increment ID erhalten ohne zweite Abfrage"},"content":{"rendered":"

Problem<\/h1>\n

Um die Auto-Increment ID zu erhalten werden des\u00f6fteren 2 Statements abgesetzt, obwohl das INSERT-Statement direkt die Auto-Increment ID zur\u00fcckgeben kann<\/p>\n

Ansatz – Approach<\/h1>\n

Anstelle von comm.ExecuteNonQuery() sollte man lieber comm.ExecuteScalar() mit SELECT SCOPE_IDENTITY() kombinieren.<\/p>\n

L\u00f6sung – Solution <\/h1>\n
\r\npublic string insertMainTherapyData(string serial_no\r\n                    ,string therapy_start\r\n                    ,string machine_type\r\n                    ,string file_source\r\n                    ,string dialog_version\r\n                    ,string tlc_version\r\n                    ,string versions\r\n                    ,DateTime uploaded_on\r\n                    ,string uploaded_by\r\n                    ,bool processed\r\n                    ,string user_original_file\r\n                    ,string user_country\r\n                    ,string user_comment\r\n                    ,string user_upload_reason\r\n                    ,string user_location)\r\n{\r\n    string sqlStatement=@"\r\n        INSERT INTO [dbo].[therapy]\r\n                    ([serial_no]\r\n                    ,[therapy_start]\r\n                    ,[machine_type]\r\n                    ,[file_source]\r\n                    ,[dialog_version]\r\n                    ,[tlc_version]\r\n                    ,[versions]\r\n                    ,[uploaded_on]\r\n                    ,[uploaded_by]\r\n                    ,[processed]\r\n                    ,[user_original_file]\r\n                    ,[user_country]\r\n                    ,[user_comment]\r\n                    ,[user_upload_reason]\r\n                    ,[user_location])\r\n        VALUES\r\n                    (@serial_no\r\n                    ,@therapy_start\r\n                    ,@machine_type\r\n                    ,@file_source\r\n                    ,@dialog_version\r\n                    ,@tlc_version\r\n                    ,@versions\r\n                    ,@uploaded_on\r\n                    ,@uploaded_by\r\n                    ,@processed\r\n                    ,@user_original_file\r\n                    ,@user_country\r\n                    ,@user_comment\r\n                    ,@user_upload_reason\r\n                    ,@user_location);\r\n            SELECT SCOPE_IDENTITY()\r\n            ";\r\n    int myID = -1;\r\n    SqlConnection conn = new SqlConnection(MyConfigurationManager.prdSqlServerString);\r\n    try\r\n    {\r\n        conn.Open();\r\n        SqlCommand comm = new SqlCommand();\r\n        comm.Connection = conn;\r\n        comm.CommandText = sqlStatement;\r\n        comm.Parameters.AddWithValue("serial_no", serial_no);\r\n        comm.Parameters.AddWithValue("therapy_start", therapy_start);\r\n        comm.Parameters.AddWithValue("machine_type", machine_type);\r\n        comm.Parameters.AddWithValue("file_source", file_source);\r\n        comm.Parameters.AddWithValue("dialog_version", dialog_version);\r\n        comm.Parameters.AddWithValue("tlc_version", tlc_version);\r\n        comm.Parameters.AddWithValue("versions", versions);\r\n        comm.Parameters.AddWithValue("uploaded_on", uploaded_on);\r\n        comm.Parameters.AddWithValue("uploaded_by", uploaded_by);\r\n        comm.Parameters.AddWithValue("processed", processed);\r\n        comm.Parameters.AddWithValue("user_original_file", user_original_file);\r\n        comm.Parameters.AddWithValue("user_country", user_country);\r\n        comm.Parameters.AddWithValue("user_comment", user_comment);\r\n        comm.Parameters.AddWithValue("user_upload_reason", user_upload_reason);\r\n        comm.Parameters.AddWithValue("user_location", user_location);\r\n        myID = Convert.ToInt32(comm.ExecuteScalar());\r\n    }\r\n    catch (Exception ex)\r\n    {\r\n        return "ERROR: "+ex.Message;\r\n    }\r\n    finally\r\n    {\r\n        conn.Close();\r\n    }\r\n\r\n    return myID.ToString();\r\n}\r\n<\/pre>\n