Dataset 多表更新自动生成变更代码

最新推荐文章于 2022-04-25 13:46:17 发布

尘莫央

最新推荐文章于 2022-04-25 13:46:17 发布

阅读量464

点赞数

分类专栏： C#

本文链接：https://blog.csdn.net/xwfswh/article/details/54380153

版权

C# 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

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); 
         }