public void ExecuteQuery(DataTable dst, string tableName)
{
sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapterForEdit);
adapterForEdit.InsertCommand = objCommandBuilder.GetInsertCommand();
int i = 0;
string uCommand = "update " + tableName + " set ";
foreach (DataColumn clmn in dst.Columns)
{
if (i == dst.Columns.Count - 1)
{
uCommand += "[" + clmn.ColumnName + "]" + " = @p3 ";
}
i++;
}
uCommand += "where ";
i = 0;
string dCommand = "delete from " + tableName + " where ";
foreach (DataColumn clmn in dst.Columns)
{
if (i == 0 || i == 1)
{
uCommand += "[" + clmn.ColumnName + "] = @p" + i + " and ";
dCommand += "[" + clmn.ColumnName + "] = @p" + i + " and ";
}
else if (i == 2)
{
uCommand += "[" + clmn.ColumnName + "] = @p" + i + "";
dCommand += "[" + clmn.ColumnName + "] = @p" + i + "";
}
i++;
}
SqlCommand updateCommand = new SqlCommand(uCommand, sqlConn);
SqlCommand deleteCommand = new SqlCommand(dCommand, sqlConn);
i = 0;
foreach (DataColumn clmn in dst.Columns)
{
updateCommand.Parameters.Add("@p" + i, SqlDbTypeConvertor.ToSqlDbType(clmn.DataType));
updateCommand.Parameters[i].SourceColumn = clmn.ColumnName;
deleteCommand.Parameters.Add("@p" + i, SqlDbTypeConvertor.ToSqlDbType(clmn.DataType));
deleteCommand.Parameters[i].SourceColumn = clmn.ColumnName;
i++;
}
adapterForEdit.UpdateCommand = updateCommand;
adapterForEdit.DeleteCommand = deleteCommand;
int res = adapterForEdit.Update(dst);
sqlConn.Close();
}
public void ExecuteQuery(DataTable dst, string tableName, string columnName1, string columnName2)
{
sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapterForEdit);
adapterForEdit.InsertCommand = objCommandBuilder.GetInsertCommand();
int i = 0;
string uCommand = "update " + tableName + " set ";
string dCommand = "delete from " + tableName + " where " + columnName1 + " = @p1 and " + columnName2 + " = @p2";
foreach (DataColumn clmn in dst.Columns)
{
if (clmn.ColumnName != columnName1 && clmn.ColumnName != columnName2)
{
if (i != dst.Columns.Count - 1)
{
uCommand += "[" + clmn.ColumnName + "] = @p" + i + ", ";
}
else
{
uCommand += "[" + clmn.ColumnName + "] = @p" + i + " ";
}
i++;
}
}
//if (tableName == "tblAddShifrSkvVar")
uCommand = uCommand.Remove(uCommand.Count() - 2, 1);
uCommand += "where " + columnName1 + " = @p" + i + " and " + columnName2 + " = @p" + (i + 1);
SqlCommand updateCommand = new SqlCommand(uCommand, sqlConn);
SqlCommand deleteCommand = new SqlCommand(dCommand, sqlConn);
i = 0;
Type type1 = typeof(Int32);
Type type2 = typeof(Int32);
foreach (DataColumn clmn in dst.Columns)
{
if (clmn.ColumnName != columnName1 && clmn.ColumnName != columnName2)
{
updateCommand.Parameters.Add("@p" + i, SqlDbTypeConvertor.ToSqlDbType(clmn.DataType));
//if (clmn.ColumnName == columnName)
updateCommand.Parameters[i].SourceColumn = clmn.ColumnName;
i++;
}
else if (clmn.ColumnName == columnName1)
{
type1 = clmn.DataType;
}
else
{
type2 = clmn.DataType;
}
//sqlParameter.SqlDbType = clmn.DataType;
}
deleteCommand.Parameters.Add("@p1", SqlDbTypeConvertor.ToSqlDbType(type1), 55, columnName1);
deleteCommand.Parameters.Add("@p2", SqlDbTypeConvertor.ToSqlDbType(type2), 55, columnName2);
updateCommand.Parameters.Add("@p" + i, SqlDbTypeConvertor.ToSqlDbType(type1), 55, columnName1);
updateCommand.Parameters.Add("@p" + (i + 1), SqlDbTypeConvertor.ToSqlDbType(type2), 55, columnName2);
adapterForEdit.UpdateCommand = updateCommand;
adapterForEdit.DeleteCommand = deleteCommand;
//adapterForEdit.UpdateCommand = objCommandBuilder.GetUpdateCommand();
//adapterForEdit.DeleteCommand = objCommandBuilder.GetDeleteCommand(); //CommandText = "DELETE FROM WHERE (([id] = @p1))"
int res = adapterForEdit.Update(dst);
sqlConn.Close();
//return mainDataset;
}