PS:为了实现通用架构方案,可支持datagridview类的数据直接修改后自动保存.
/// 多表DataSet保存
/// </summary>
/// <param name="das">DataSet</param>
/// <param name="tableName">表名</param>
/// <param name="ID">索引字段</param>
/// <param name="Fields">要变更的数据字段集合,以,分隔</param>
/// <returns></returns>
public bool SaveData(DataSet das,string tableName, string ID, string Fields)
{
using (SqlConnection sqlCon = new SqlConnection(conStr))
{
try
{
DataSet dsChange = das.GetChanges();
string sql = "";
if (dsChange == null)
{
return false;
}
else
{
string[] sFields=Fields.Split(',');
foreach (DataRow dr in dsChange.Tables[0].Rows)
{
if (dr.RowState == DataRowState.Deleted)
{
//删除时需取初始值
//string id = dtDeleted.Rows[0]["id", DataRowVersion.Original].ToString();
//删除方法
sql += "Delete from " + tableName + " where " + ID + "=" + dr[ID, DataRowVersion.Original].ToString() + " \r\n";
}
else if (dr.RowState == DataRowState.Modified)
{
//更新方法
string usql="";
string uvalue = "";
for (int i = 0; i < sFields.Length; i++)
{
if (dr.Table.Columns[sFields[i]].DataType == typeof(string) || dr.Table.Columns[sFields[i]].DataType == typeof(DateTime))
uvalue = "'" + dr[sFields[i]].ToString() + "'";
else if (dr.Table.Columns[sFields[i]].DataType == typeof(bool))
uvalue = (Convert.ToBoolean(dr[sFields[i]])?"1":"0");
else uvalue = dr[sFields[i]].ToString();
if (!string.IsNullOrWhiteSpace(usql)) usql += ",";
usql += sFields[i] + "=" + uvalue;
}
sql += "update " + tableName + " set "+usql+" where " + ID + "=" + dr[ID].ToString() + " \r\n";
}
else if (dr.RowState == DataRowState.Added)
{
//新增方法
string ufield = "";
string uvalue = "";
for (int i = 0; i < sFields.Length; i++)
{
if (!string.IsNullOrWhiteSpace(uvalue)) uvalue += ",";
if (dr.Table.Columns[sFields[i]].DataType == typeof(string) || dr.Table.Columns[sFields[i]].DataType == typeof(DateTime))
uvalue += "'" + dr[sFields[i]].ToString() + "'";
else if (dr.Table.Columns[sFields[i]].DataType == typeof(bool))
uvalue = (Convert.ToBoolean(dr[sFields[i]])?"1":"0");
else uvalue += dr[sFields[i]].ToString();
if (!string.IsNullOrWhiteSpace(ufield)) ufield += ",";
ufield += sFields[i];
}
sql += "insert into " + tableName + " (" + ufield + ") values (" + uvalue + ") \r\n";
}
}
MessageBox.Show(sql);
}
return true;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
}
//初始数据源:
private void Form1_Load(object sender, EventArgs e)
{
//加载dataset
string dataSQL="select A.ID,b.id,b.Code,A.ComputerName,A.[Description],A.IPAddress,A.Modal,A.Oper,A.DoTime,DoUser from Sys_Log A left join Sys_Modal B on a.Modal=b.Name";
dataSet = GetDs(dataSQL);
gridControl1.DataSource = dataSet.Tables[0];
}
//调用测试
private void button8_Click(object sender, EventArgs e)
{
string Fields = "ComputerName,Description,IPAddress,Oper,DoTime,DoUser";
SaveData(dataSet, "Sys_Log", "ID", Fields);
}