DataTable 批更新到数据库中的操作

记下一次DataTable的数据的批量性更新到数据库的过珵：

从第一个系统自带的更新，运行后没报错，在数据库中也没有更新。

第二个是网络中常见的批更新，但效果一样是运行正常，数据库中没有更新。

最后只能手动生成N条记录后更新到数据库中。

private void employyAttRecordBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            //try
            //{
            //    this.Validate();
            //    this.employyAttRecordBindingSource.EndEdit();
            //    this.tableAdapterManager.UpdateAll(this.hrDataSet);
            //    MessageBox.Show("保存成功");
            //}
            //catch (SqlException ex)
            //{
            //    MessageBox.Show("失败：" + ex.Message);
            //}
            //-----------------------------------------------------------------------------------------
            //DataBase db = new DataBase();
            //string sqlstring;
            //sqlstring = "select recordid,worktime,otm,att1,att2,att3,att4,att5,att6,id from employyattrecord";
            sqlstring = "Update employyattrecord set worktime=@worktime,otm=@otm,Att1=@Att1,Att2=@Att2,Att3=@Att3,Att4=@Att4,Att5=@Att5,Att6=@Att6 Where RecordID=@RecordID";
            //DataTable dt = new DataTable();
            //dt.Columns.Add("RecordID", System.Type.GetType("System.Int32"));
            //dt.Columns.Add("WorkTime", System.Type.GetType("System.Int32"));
            //dt.Columns.Add("OTm", System.Type.GetType("System.Int32"));
            //dt.Columns.Add("Att1", System.Type.GetType("System.TimeSpan"));
            //dt.Columns.Add("Att2", System.Type.GetType("System.TimeSpan"));
            //dt.Columns.Add("Att3", System.Type.GetType("System.TimeSpan"));
            //dt.Columns.Add("Att4", System.Type.GetType("System.TimeSpan"));
            //dt.Columns.Add("Att5", System.Type.GetType("System.TimeSpan"));
            //dt.Columns.Add("Att6", System.Type.GetType("System.TimeSpan"));
            //dt.Columns.Add("id", System.Type.GetType("System.String"));
            

            //for (int i = 0; i < employyAttRecordDataGridView.Rows.Count; i++)
            //{
            //    DataRow dr = dt.NewRow();
            //    dr[0] = employyAttRecordDataGridView.Rows[i].Cells["RecordID"].Value.ToString();
            //    dr[1] = employyAttRecordDataGridView.Rows[i].Cells["WorkTimeCol"].Value.ToString();
            //    dr[2] = employyAttRecordDataGridView.Rows[i].Cells["OTmCol"].Value.ToString();
            //    if (employyAttRecordDataGridView.Rows[i].Cells["Att1Col"].Value.ToString() != string.Empty) dr[3] = employyAttRecordDataGridView.Rows[i].Cells["Att1Col"].Value.ToString();
            //    if (employyAttRecordDataGridView.Rows[i].Cells["Att2Col"].Value.ToString() != string.Empty) dr[4] = employyAttRecordDataGridView.Rows[i].Cells["Att2Col"].Value.ToString();
            //    if (employyAttRecordDataGridView.Rows[i].Cells["Att3Col"].Value.ToString() != string.Empty) dr[5] = employyAttRecordDataGridView.Rows[i].Cells["Att3Col"].Value.ToString();
            //    if (employyAttRecordDataGridView.Rows[i].Cells["Att4Col"].Value.ToString() != string.Empty) dr[6] = employyAttRecordDataGridView.Rows[i].Cells["Att4Col"].Value.ToString();
            //    if (employyAttRecordDataGridView.Rows[i].Cells["Att5Col"].Value.ToString() != string.Empty) dr[7] = employyAttRecordDataGridView.Rows[i].Cells["Att5Col"].Value.ToString();
            //    if (employyAttRecordDataGridView.Rows[i].Cells["Att6Col"].Value.ToString() != string.Empty) dr[8] = employyAttRecordDataGridView.Rows[i].Cells["Att6Col"].Value.ToString();
            //    dr[9] = employyAttRecordDataGridView.Rows[i].Cells["ID"].Value.ToString();
            //    dt.Rows.Add(dr);
            //}
            //MessageBox.Show(db.SetDataTable(dt, sqlstring).ToString());
            //-----------------------------------------------------------------------------------------------------------------------------

            List<string> mysqls = new List<string>();
            string sqlstring;
            DataBase db = new DataBase();
            for (int i = 0; i < employyAttRecordDataGridView.Rows.Count; i++)
            {
                sqlstring="update employyAttRecord set ";
                sqlstring += "worktime=" + employyAttRecordDataGridView.Rows[i].Cells["WorkTimeCol"].Value.ToString();
                sqlstring += ",otm=" + employyAttRecordDataGridView.Rows[i].Cells["OTmCol"].Value.ToString();
                if (employyAttRecordDataGridView.Rows[i].Cells["Att1Col"].Value.ToString() != string.Empty) sqlstring += ",Att1='" + employyAttRecordDataGridView.Rows[i].Cells["Att1Col"].Value.ToString()+"'";
                if (employyAttRecordDataGridView.Rows[i].Cells["Att2Col"].Value.ToString() != string.Empty) sqlstring += ",Att2='" + employyAttRecordDataGridView.Rows[i].Cells["Att2Col"].Value.ToString() + "'";
                if (employyAttRecordDataGridView.Rows[i].Cells["Att3Col"].Value.ToString() != string.Empty) sqlstring += ",Att3='" + employyAttRecordDataGridView.Rows[i].Cells["Att3Col"].Value.ToString() + "'";
                if (employyAttRecordDataGridView.Rows[i].Cells["Att4Col"].Value.ToString() != string.Empty) sqlstring += ",Att4='" + employyAttRecordDataGridView.Rows[i].Cells["Att4Col"].Value.ToString() + "'";
                if (employyAttRecordDataGridView.Rows[i].Cells["Att5Col"].Value.ToString() != string.Empty) sqlstring += ",Att5='" + employyAttRecordDataGridView.Rows[i].Cells["Att5Col"].Value.ToString() + "'";
                if (employyAttRecordDataGridView.Rows[i].Cells["Att6Col"].Value.ToString() != string.Empty) sqlstring += ",Att6='" + employyAttRecordDataGridView.Rows[i].Cells["Att6Col"].Value.ToString() + "'";
                sqlstring += " where recordid=" + employyAttRecordDataGridView.Rows[i].Cells["RecordID"].Value.ToString();
                mysqls.Add(sqlstring);
                    
            }
            db.ExecDataBySqls(mysqls);

        }

/// <summary>
        /// 多条Transact-SQL语句提交数据
        /// </summary>
        /// <param name="strSqls">使用List泛型封装多条SQL语句</param>
        /// <returns>bool值(提交是否成功)</returns>
        public bool ExecDataBySqls(List<string> strSqls)
        {
            bool booIsSucceed;

            if (m_Conn.State == ConnectionState.Closed)
            {
                m_Conn.Open();
            }

            SqlTransaction sqlTran = m_Conn.BeginTransaction();

            try
            {
                m_Cmd.Transaction = sqlTran;

                foreach (string item in strSqls)
                {
                    m_Cmd.CommandType = CommandType.Text;
                    m_Cmd.CommandText = item;
                    m_Cmd.ExecuteNonQuery();
                }

                sqlTran.Commit();
                booIsSucceed = true;  //表示提交数据库成功
            }
            catch
            {
                sqlTran.Rollback();
                booIsSucceed = false;  //表示提交数据库失败！
            }
            finally
            {
                m_Conn.Close();
                strSqls.Clear();
            }

            return booIsSucceed;
        }

 /// <summary>
        /// 将DataTable中的数据更新到数据库中da.UpdateCommand
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="sqlstring">初始化SqldataAdapter 中的SelectCommand SQL语句</param>
        public int SetDataTable(DataTable dt,string sqlstring)
        {
            
            int affect = 0;
            try
            {
                m_Cmd.CommandType = CommandType.Text;
                m_Cmd.CommandText = sqlstring;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = m_Cmd;  
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                da.UpdateCommand = cb.GetUpdateCommand();
                m_Conn.Open();
                affect=da.Update(dt);
                m_Conn.Close();
                
            }
            catch (SqlException ex)
            {
                
                throw ex;
                
            }
            finally
            {
                m_Conn.Close();
            }
            return affect;
        }