Row Constructor for SqlSerever 2005 or lower
private void
RowConstructor_Insert_ALL_2005(DataTable
dt_Comp)//It will also works for lower version
sqlservers like sql server 2005 or lower
{
System.Text.StringBuilder query = new System.Text.StringBuilder();
int ret = 0;
if (dt_Comp.Rows.Count > 0)
{
query.Clear();
DataRow dr;
query.Append(string.Format("INSERT INTO TabRowConstruction(ID,NAME,ADDRESS)
"));
for (int
PK = 0; PK < dt_Comp.Rows.Count-1; PK++)
{
dr = dt_Comp.Rows[PK];
query.Append(string.Format(" SELECT '{0}','{1}','{2}' UNION ALL ",
dr["ID"], dr["NAME"], dr["ADDRESS"]));
}
dr = dt_Comp.Rows[dt_Comp.Rows.Count-1];
query.Append(string.Format(" SELECT '{0}','{1}','{2}' ", dr["ID"],
dr["NAME"], dr["ADDRESS"]));
ret = ExecuteQuery(query.ToString());
if (ret > 0)
{
//Success
}
else
{
//Failed
}
}
else
{
//No Data in DataTable
}
}
Row Constructor for sql server 2008 or higher
private void
RowConstructor_Insert_ALL(DataTable dt_Comp)
{
System.Text.StringBuilder query = new System.Text.StringBuilder();
int ret = 0;
if (dt_Comp.Rows.Count > 0)
{
query.Clear();
DataRow dr = dt_Comp.Rows[0];
query.Append(string.Format("INSERT INTO TabRowConstruction(ID,NAME,ADDRESS)
"
+ "VALUES('{0}','{1}','{2}' ) ", dr["ID"], dr["NAME"],
dr["ADDRESS"]));
for (int
PK = 1; PK < dt_Comp.Rows.Count; PK++)
{
dr = dt_Comp.Rows[PK];
query.Append(string.Format(",
('{0}','{1}','{2}' ) ", dr["ID"],
dr["NAME"], dr["ADDRESS"]));
}
ret = ExecuteQuery(query.ToString());
if (ret > 0)
{
//Success
}
else
{
//Failed
}
}
else
{
//No Data in DataTable
}
}
Row constructor for easy insertion without packet size exceptions
private void
RowConstructor_Insert_100(DataTable dt_Comp)
{
//Divide all data into set of 100 rows and execute it due
to Data Base allowed size issue.
System.Text.StringBuilder query = new System.Text.StringBuilder();
int ret = 0;
if (dt_Comp.Rows.Count > 0)
{
int No_of_times = dt_Comp.Rows.Count /
100;
int reminder = dt_Comp.Rows.Count % 100;
for (int
KU = 0; KU < No_of_times; KU++)
{
query.Clear();
DataRow dr = dt_Comp.Rows[KU * 100];
query.Append(string.Format("INSERT INTO TabRowConstruction(ID,NAME,ADDRESS)
"
+ "VALUES('{0}','{1}','{2}' ) ", dr["ID"], dr["NAME"],
dr["ADDRESS"]));
for (int
PK = (KU * 100) + 1; PK < (KU + 1) * 100; PK++)
{
dr = dt_Comp.Rows[PK];
query.Append(string.Format(",
('{0}','{1}','{2}' ) ", dr["ID"],
dr["NAME"], dr["ADDRESS"]));
}
ret = ExecuteQuery(query.ToString());
if (ret > 0)
{
//Success
}
else
{
//Failed
}
query.Clear();
}
if (reminder != 0)
{
DataRow dr = dt_Comp.Rows[100 *
No_of_times];
query.Clear();
query.Append(string.Format("INSERT INTO TabRowConstruction(ID,NAME,ADDRESS)
"
+ "VALUES('{0}','{1}','{2}'
) ", dr["ID"], dr["NAME"], dr["ADDRESS"]));
for (int
PK = (100 * No_of_times) + 1; PK < dt_Comp.Rows.Count; PK++)
{
dr = dt_Comp.Rows[PK];
query.Append(string.Format(",
('{0}','{1}','{2}' ) ", dr["ID"],
dr["NAME"], dr["ADDRESS"]));
}
ret = ExecuteQuery(query.ToString());
if (ret > 0)
{
//Success
}
else
{
//Failed
}
query.Clear();
}
}
else
{
//No Data in DataTable
}
}