Because the number of data rows that may be added is not fixed, you could put the data in the array and then store the contents in the data table in turn.
You can refer to the following simple example:
public static void test() {
String conn = "data source=.; database=TestDB; integrated security=SSPI";
List<string[]> param = new List<String[]>() {
new string[] { "FirstName1", "LastName1", "FullName1" },
new string[] { "FirstName2", "LastName2", "FullName2" },
new string[] { "FirstName3", "LastName3", "FullName3" },
new string[] { "FirstName4", "LastName4", "FullName4" }
};
UserCreation(conn, param);
}
public static string UserCreation(string conn, List<String[]> param)
{
using (SqlConnection con = new SqlConnection(conn))
{
String query = "INSERT INTO [TABLEUSER] (FirstName,LastName,FullName )" +
" VALUES (@FirstName,@LastName,@FullName)";
con.Open();
using (SqlCommand command = new SqlCommand(query, con))
{
for (int i = 0; i < param.Count; i++)
{
int result = 0;
command.Parameters.Clear();
command.Parameters.Add("@FirstName", param[i][0]);
command.Parameters.Add("@LastName", param[i][1]);
command.Parameters.Add("@FullName", param[i][2]);
result = command.ExecuteNonQuery();
if (result < 1) return $" error in insert ({result})";
}
con.Close();
}
}
return String.Empty;
}
Hope this can help you.
Best regards,
Xudong Peng
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
I suggest you could store the data in a custom class object, and then insert the data traversal into the data table.
More details,you could refer to below codes:
Data.cs
public class Data
{
public String FirstName { set; get; }
public String LastName { set; get; }
public String FullName { set; get; }
public int index { set; get; }
public String id { set; get; }
}
Test.cs
public static void test() {
String conn = "data source=.; database=TestDB; integrated security=SSPI";
List<Data> param = new List<Data>() {
new Data(){FirstName="FirstName1",LastName="LastName1",FullName="FullName1",index=1,id="abc" },
new Data(){FirstName="FirstName2",LastName="LastName2",FullName="FullName2",index=4,id="def" },
new Data(){FirstName="FirstName3",LastName="LastName3",FullName="FullName3",index=5,id="ghi" },
new Data(){FirstName="FirstName4",LastName="LastName4",FullName="FullName5",index=6,id="jkl" },
};
UserCreation(conn, param);
}
public static string UserCreation(string conn, List<Data> param)
{
using (SqlConnection con = new SqlConnection(conn))
{
String query = "INSERT INTO [test] (FirstName,LastName,FullName,[index],id )" +
" VALUES (@FirstName,@LastName,@FullName,@index,@id)";
con.Open();
using (SqlCommand command = new SqlCommand(query, con))
{
for (int i = 0; i < param.Count; i++)
{
int result = 0;
command.Parameters.Clear();
command.Parameters.Add("@FirstName", param[i].FirstName);
command.Parameters.Add("@LastName", param[i].LastName);
command.Parameters.Add("@FullName", param[i].FullName);
command.Parameters.Add("@index", param[i].index);
command.Parameters.Add("@id", param[i].id);
result = command.ExecuteNonQuery();
if (result < 1) return $" error in insert ({result})";
}
con.Close();
}
}
return String.Empty;
}
Hope this can help you.
Best regards,
Xudong Peng
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
if by efficient you mean performant, then you need to execute in a batch. unfortunately unlike other database engines, sqlserver does not have array insert, you you need to build the the batch on the client.
public static string UserCreation(string conn, List<Data> param)
{
if (param.Count == 0) return "no data":
using (SqlConnection con = new SqlConnection(conn))
{
con.Open();
using (SqlCommand command = new SqlCommand(query, con))
{
var query = "INSERT INTO [test] (FirstName,LastName,FullName,[index],id ) VALUES " ;
var sep = "";
for (int i = 0; i < param.Count; i++)
{
query += $@"{sep}(@FirstName{i},@LastName{i},@FullName{i},@index{i},@id{i})";
command.Parameters.Add("@FirstName" + i.ToString(), param[i].FirstName);
command.Parameters.Add("@LastName" + i.ToString(), param[i].LastName);
command.Parameters.Add("@FullName" + i.ToString(), param[i].FullName);
command.Parameters.Add("@index" + i.ToString(), param[i].index);
command.Parameters.Add("@id" + i.ToString(), param[i].id);
sep = ",";
}
command.CommandText = query;
var result = command.ExecuteNonQuery();
if (result < param.Count) return $" error in insert ({result})"; //assume no triggers
con.Close();
}
}
return String.Empty;
}
if you are using a more modern sqlserver, you can use json to pass the array of values
public static string UserCreation(string conn, List<Data> param)
{
if (param.Count == 0) return "no data":
using (SqlConnection con = new SqlConnection(conn))
{
con.Open();
using (SqlCommand command = new SqlCommand(query, con))
{
var query = "INSERT INTO [test] (FirstName,LastName,FullName,[index],id ) "
+ "SELECT * from OPENJSON(@data) WITH ( "
+ "FirstName NVARCHAR(50) '$.FirstName', "
+ "LastName NVARCHAR(50) '$.LastName', "
+ "FullName NVARCHAR(50) '$.FullName', "
+ "[index] INT '$.index', "
+ "id NVARCHAR(50) '$.Id');";
command.CommandText = query;
command.Parameters.Add("@data" , JsonConvert.SerializeObject(data));
var result = command.ExecuteNonQuery();
if (result < param.Count) return $" error in insert ({result})"; //assume no triggers
con.Close();
}
}
return String.Empty;
}
Member
281 Points
1002 Posts
Whats the most efficient way to run multiple insert statements to a sql table using c# ?
May 28, 2020 12:35 AM|robby32|LINK
I perform the following:
public string UserCreation(string conn, string firstname,string lastname, string fullname)
{
using (SqlConnection con = new SqlConnection(conn))
{
String query = "INSERT INTO [TABLEUSER] (FirstName,LastName,FullName )" +
" VALUES (@FirstName,@LastName,@FullName)";
using (SqlCommand command = new SqlCommand(query, con))
{
command.Parameters.Add("@FirstName", firstname);
command.Parameters.Add("@LastName", lastname);
command.Parameters.Add("@FullName", fullname);
con.Open();
var result = command.ExecuteNonQuery();
con.Close();
if (result >= 1) return string.Empty;
return $" error in insert ({result})";
}
}
}
and i call it multiple time like so
UserCreation(conn, "FName1", "FSurname1". "FullName1");
UserCreation(conn, "FName2", "FSurname2". "FullName2");
UserCreation(conn, "FName3", "FSurname3". "FullName3");
UserCreation(conn, "FName4", "FSurname4". "FullName4");
Is there a way I can pass the parameters all in one go and call the UserCreation once ?
thanks
Contributor
2080 Points
664 Posts
Re: Whats the most efficient way to run multiple insert statements to a sql table using c# ?
May 28, 2020 05:20 AM|XuDong Peng|LINK
Hi, robby32
Because the number of data rows that may be added is not fixed, you could put the data in the array and then store the contents in the data table in turn.
You can refer to the following simple example:
public static void test() { String conn = "data source=.; database=TestDB; integrated security=SSPI"; List<string[]> param = new List<String[]>() { new string[] { "FirstName1", "LastName1", "FullName1" }, new string[] { "FirstName2", "LastName2", "FullName2" }, new string[] { "FirstName3", "LastName3", "FullName3" }, new string[] { "FirstName4", "LastName4", "FullName4" } }; UserCreation(conn, param); } public static string UserCreation(string conn, List<String[]> param) { using (SqlConnection con = new SqlConnection(conn)) { String query = "INSERT INTO [TABLEUSER] (FirstName,LastName,FullName )" + " VALUES (@FirstName,@LastName,@FullName)"; con.Open(); using (SqlCommand command = new SqlCommand(query, con)) { for (int i = 0; i < param.Count; i++) { int result = 0; command.Parameters.Clear(); command.Parameters.Add("@FirstName", param[i][0]); command.Parameters.Add("@LastName", param[i][1]); command.Parameters.Add("@FullName", param[i][2]); result = command.ExecuteNonQuery(); if (result < 1) return $" error in insert ({result})"; } con.Close(); } } return String.Empty; }Hope this can help you.
Best regards,
Xudong Peng
Member
281 Points
1002 Posts
Re: Whats the most efficient way to run multiple insert statements to a sql table using c# ?
May 28, 2020 06:49 AM|robby32|LINK
This will only work if all input is string. If i have input such as
Contributor
2080 Points
664 Posts
Re: Whats the most efficient way to run multiple insert statements to a sql table using c# ?
May 28, 2020 09:11 AM|XuDong Peng|LINK
Hi, robby32
I suggest you could store the data in a custom class object, and then insert the data traversal into the data table.
More details,you could refer to below codes:
Data.cs
public class Data { public String FirstName { set; get; } public String LastName { set; get; } public String FullName { set; get; } public int index { set; get; } public String id { set; get; } }Test.cs
public static void test() { String conn = "data source=.; database=TestDB; integrated security=SSPI"; List<Data> param = new List<Data>() { new Data(){FirstName="FirstName1",LastName="LastName1",FullName="FullName1",index=1,id="abc" }, new Data(){FirstName="FirstName2",LastName="LastName2",FullName="FullName2",index=4,id="def" }, new Data(){FirstName="FirstName3",LastName="LastName3",FullName="FullName3",index=5,id="ghi" }, new Data(){FirstName="FirstName4",LastName="LastName4",FullName="FullName5",index=6,id="jkl" }, }; UserCreation(conn, param); } public static string UserCreation(string conn, List<Data> param) { using (SqlConnection con = new SqlConnection(conn)) { String query = "INSERT INTO [test] (FirstName,LastName,FullName,[index],id )" + " VALUES (@FirstName,@LastName,@FullName,@index,@id)"; con.Open(); using (SqlCommand command = new SqlCommand(query, con)) { for (int i = 0; i < param.Count; i++) { int result = 0; command.Parameters.Clear(); command.Parameters.Add("@FirstName", param[i].FirstName); command.Parameters.Add("@LastName", param[i].LastName); command.Parameters.Add("@FullName", param[i].FullName); command.Parameters.Add("@index", param[i].index); command.Parameters.Add("@id", param[i].id); result = command.ExecuteNonQuery(); if (result < 1) return $" error in insert ({result})"; } con.Close(); } } return String.Empty; }Hope this can help you.
Best regards,
Xudong Peng
Member
281 Points
1002 Posts
Re: Whats the most efficient way to run multiple insert statements to a sql table using c# ?
May 28, 2020 09:27 AM|robby32|LINK
yes , custom class works well.
All-Star
58174 Points
15647 Posts
Re: Whats the most efficient way to run multiple insert statements to a sql table using c# ?
May 28, 2020 03:56 PM|bruce (sqlwork.com)|LINK
if by efficient you mean performant, then you need to execute in a batch. unfortunately unlike other database engines, sqlserver does not have array insert, you you need to build the the batch on the client.
public static string UserCreation(string conn, List<Data> param) { if (param.Count == 0) return "no data": using (SqlConnection con = new SqlConnection(conn)) { con.Open(); using (SqlCommand command = new SqlCommand(query, con)) { var query = "INSERT INTO [test] (FirstName,LastName,FullName,[index],id ) VALUES " ; var sep = ""; for (int i = 0; i < param.Count; i++) { query += $@"{sep}(@FirstName{i},@LastName{i},@FullName{i},@index{i},@id{i})"; command.Parameters.Add("@FirstName" + i.ToString(), param[i].FirstName); command.Parameters.Add("@LastName" + i.ToString(), param[i].LastName); command.Parameters.Add("@FullName" + i.ToString(), param[i].FullName); command.Parameters.Add("@index" + i.ToString(), param[i].index); command.Parameters.Add("@id" + i.ToString(), param[i].id); sep = ","; } command.CommandText = query; var result = command.ExecuteNonQuery(); if (result < param.Count) return $" error in insert ({result})"; //assume no triggers con.Close(); } } return String.Empty; }if you are using a more modern sqlserver, you can use json to pass the array of values
public static string UserCreation(string conn, List<Data> param) { if (param.Count == 0) return "no data": using (SqlConnection con = new SqlConnection(conn)) { con.Open(); using (SqlCommand command = new SqlCommand(query, con)) { var query = "INSERT INTO [test] (FirstName,LastName,FullName,[index],id ) " + "SELECT * from OPENJSON(@data) WITH ( " + "FirstName NVARCHAR(50) '$.FirstName', " + "LastName NVARCHAR(50) '$.LastName', " + "FullName NVARCHAR(50) '$.FullName', " + "[index] INT '$.index', " + "id NVARCHAR(50) '$.Id');"; command.CommandText = query; command.Parameters.Add("@data" , JsonConvert.SerializeObject(data)); var result = command.ExecuteNonQuery(); if (result < param.Count) return $" error in insert ({result})"; //assume no triggers con.Close(); } } return String.Empty; }