Bulk Insert using Parameterized Row Constructor in ASP.Net with C#.Net


Row Constructor for sql server 2008 or higher


protected void Page_Load(object sender, EventArgs e)
{
RowConstructor_Parameterized_Insert(GetData());
}

private DataTable GetData()
{
DataTable dt = new DataTable();
dt.TableName = "MyTab";

dt.Columns.Add("ID", System.Type.GetType("System.Decimal"));
dt.Columns.Add("NAME", System.Type.GetType("System.String"));
dt.Columns.Add("ADDRESS", System.Type.GetType("System.String"));

for (int PK = 0; PK < 100; PK++)
{
DataRow dr = dt.NewRow();
dr["ID"] = PK;
dr["NAME"] = "NAME" + PK;
dr["ADDRESS"] = "ADDRESS" + PK;

dt.Rows.Add(dr);
dt.AcceptChanges();
}

return dt;
}

public static int ExecuteQuery(string Query, string[] Parameters, object[] Parameter_Values)
{
int ret = -1;
SqlConnection con;
SqlCommand cmd;
try
{
con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ToString());
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = Query;

for (int PK = 0; PK < Parameters.Length; PK++)
{
cmd.Parameters.Add(new SqlParameter(Parameters[PK], Parameter_Values[PK]));
}

con.Open();
ret = cmd.ExecuteNonQuery();
//For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.
con.Close();
}
catch (Exception ex)
{
}
return ret;
}

private static string Form_Insert_Query(string[] strarray, int L_Count, bool Intial_Insert)
{
StringBuilder Query = new StringBuilder();
string[] strParameters_New_Insert = strarray.Select(s => "@" + s + "_" + L_Count).ToArray();
string Query_Temp = string.Concat("(", string.Join(",", strParameters_New_Insert), ")");
if (Intial_Insert)
{
Query.Append(string.Concat("INSERT INTO TABROWCONSTRUCTION(", string.Join(",", strarray), ") ", " VALUES"));
Query.Append(Query_Temp);
}
else
{
Query.Append(@", ");
Query.Append(Query_Temp);
}

return Query.ToString();
}

private void RowConstructor_Parameterized_Insert(DataTable dt_Upload)
{
int Loop_times = 0, Row_Count = dt_Upload.Rows.Count, ret = 0, MyObjParmVal = 0, Total_No_Of_Cols = 0;
const int Row_Constructor_Split_Count = 50;

if (dt_Upload.Rows.Count > 0)
{
try
{
StringBuilder query = new StringBuilder();
int No_of_times = Row_Count / Row_Constructor_Split_Count;
int reminder = Row_Count % Row_Constructor_Split_Count;
DataRow dr_Uploadlot = null;
string[] strParameters_Ins = new string[]
{
"ID",
"NAME",
"ADDRESS"
};

Total_No_Of_Cols = strParameters_Ins.Length;

for (int KU = 0; KU < No_of_times; KU++)
{
query.Clear();
Loop_times = KU * Row_Constructor_Split_Count;

object[] objParameterValues = new object[Row_Constructor_Split_Count * Total_No_Of_Cols];
string[] strParameters = new string[Row_Constructor_Split_Count * Total_No_Of_Cols];

dr_Uploadlot = dt_Upload.Rows[Loop_times];
query.Append(Form_Insert_Query(strParameters_Ins, Loop_times, true));

MyObjParmVal = 0;

objParameterValues[MyObjParmVal] = dr_Uploadlot["ID"].ToString().Trim().Trim();
objParameterValues[MyObjParmVal + 1] = dr_Uploadlot["NAME"].ToString().Trim();
objParameterValues[MyObjParmVal + 2] = dr_Uploadlot["ADDRESS"].ToString().Trim();

strParameters[MyObjParmVal] = "@ID_" + Loop_times;
strParameters[MyObjParmVal + 1] = "@NAME_" + Loop_times;
strParameters[MyObjParmVal + 2] = "@ADDRESS_" + Loop_times;

for (int PK = (Loop_times) + 1; PK < (KU + 1) * Row_Constructor_Split_Count; PK++)
{
dr_Uploadlot = dt_Upload.Rows[PK];
query.Append(Form_Insert_Query(strParameters_Ins, PK, false));

MyObjParmVal = MyObjParmVal + Total_No_Of_Cols;

objParameterValues[MyObjParmVal] = dr_Uploadlot["ID"].ToString().Trim();
objParameterValues[MyObjParmVal + 1] = dr_Uploadlot["NAME"].ToString().Trim();
objParameterValues[MyObjParmVal + 2] = dr_Uploadlot["ADDRESS"].ToString().Trim();

strParameters[MyObjParmVal] = "@ID_" + PK;
strParameters[MyObjParmVal + 1] = "@NAME_" + PK;
strParameters[MyObjParmVal + 2] = "@ADDRESS_" + PK;
}

ret = ExecuteQuery(query.ToString(), strParameters, objParameterValues);
if (ret != -1 && ret > 0)
{
//success.
}
else
{
//failed.
}

query.Clear();
}
if (reminder != 0)
{
Loop_times = Row_Constructor_Split_Count * No_of_times;
object[] objParameterValues = new object[reminder * Total_No_Of_Cols];
string[] strParameters = new string[reminder * Total_No_Of_Cols];

dr_Uploadlot = dt_Upload.Rows[Loop_times];
query.Clear();
query.Append(Form_Insert_Query(strParameters_Ins, Loop_times, true));

MyObjParmVal = 0;

objParameterValues[MyObjParmVal] = dr_Uploadlot["ID"].ToString().Trim();
objParameterValues[MyObjParmVal + 1] = dr_Uploadlot["NAME"].ToString().Trim();
objParameterValues[MyObjParmVal + 2] = dr_Uploadlot["ADDRESS"].ToString().Trim();

strParameters[MyObjParmVal] = "@ID_" + Loop_times;
strParameters[MyObjParmVal + 1] = "@NAME_" + Loop_times;
strParameters[MyObjParmVal + 2] = "@ADDRESS_" + Loop_times;

for (int PK = (Loop_times) + 1; PK < Row_Count; PK++)
{
dr_Uploadlot = dt_Upload.Rows[PK];
query.Append(Form_Insert_Query(strParameters_Ins, PK, false));

MyObjParmVal = MyObjParmVal + Total_No_Of_Cols;

objParameterValues[MyObjParmVal] = dr_Uploadlot["ID"].ToString().Trim();
objParameterValues[MyObjParmVal + 1] = dr_Uploadlot["NAME"].ToString().Trim();
objParameterValues[MyObjParmVal + 2] = dr_Uploadlot["ADDRESS"].ToString().Trim();

strParameters[MyObjParmVal] = "@ID_" + PK;
strParameters[MyObjParmVal + 1] = "@NAME_" + PK;
strParameters[MyObjParmVal + 2] = "@ADDRESS_" + PK;
}

ret = ExecuteQuery(query.ToString().Trim(), strParameters, objParameterValues);
if (ret != -1 && ret > 0)
{
//success.
}
else
{
//failed.
}
query.Clear();
}
}
catch (Exception ex)
{
}
}
else
{
//No Data in DataTable
}
}


You Can Download the Working Code From here.
Posted by
Labels: ,