/// <summary> /// 修改数据库 /// </summary> /// <param name="sql"></param> /// <param name="tb"></param> private void databaseupdate(string sql, DataTable tb) { //数据更行到数据库 System.Data.SqlClient.SqlConnection connect = new System.Data.SqlClient.SqlConnection(); connect.ConnectionString = FrmMdiMain.Database.ConnectionString;// " server=x6x8-20100320QL\\SQLEXPRESS;database=trasen_Emr_test;UID=sa;Password=sa8920993"; connect.Open(); System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = new System.Data.SqlClient.SqlCommand(sql, connect); System.Data.SqlClient.SqlCommandBuilder sqlcom = new System.Data.SqlClient.SqlCommandBuilder(adapter); //DataTable newtb = new DataTable(); // newtb.TableName = "tb"; DataSet ds = new DataSet(); adapter.TableMappings[0].ColumnMappings.Add("", ""); adapter.TableMappings.Add("df", "fdf"); adapter.Fill(ds); //ds.Tables[0].Rows[2]["note"] = "开户银行2"; ds.Tables[1].Rows[0]["bbid"] = 1; adapter.InsertCommand = sqlcom.GetInsertCommand(); adapter.DeleteCommand = sqlcom.GetDeleteCommand(); adapter.UpdateCommand = sqlcom.GetUpdateCommand(); int i = adapter.Update(ds); tb.AcceptChanges(); sqlcom.RefreshSchema(); connect.Close(); }
/// <summary> /// 修改数据库 /// </summary> /// <param name="sql"></param> /// <param name="tb"></param> public static void databaseupdate(string sql, DataTable tb) { //数据更行到数据库 System.Data.SqlClient.SqlConnection connect = new System.Data.SqlClient.SqlConnection(); connect.ConnectionString = FrmMdiMain.Database.ConnectionString;// " server=x6x8-20100320QL\\SQLEXPRESS;database=trasen_Emr_test;UID=sa;Password=sa8920993"; connect.Open(); System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = new System.Data.SqlClient.SqlCommand(sql, connect); System.Data.SqlClient.SqlCommandBuilder sqlcom = new System.Data.SqlClient.SqlCommandBuilder(adapter); //DataSet ds = new DataSet(); //System.Data.SqlClient.SqlTransaction sqltra = connect.BeginTransaction(); //adapter.TableMappings[0].ColumnMappings.Add("", ""); // adapter.TableMappings.Add("df", "fdf"); //adapter.Fill(ds); //ds.Tables[0].Rows[2]["note"] = "开户银行2"; //ds.Tables[1].Rows[0]["bbid"] = 1; //tb.PrimaryKey = new DataColumn[] { tb.Columns["Bsid"] }; //tb.Rows[0]["reason"] = "1"; //tb.Rows[1]["reason"] = "1"; //tb.Rows[2]["reason"] = "1"; //tb.Columns["Bsid"].ColumnName = "path_step_item_id"; //tb.Columns["Parent_id"].ColumnName = "step_item_kind_id"; //tb.Columns["order_spec"].ColumnName = "notes"; DataTable tbnew = tb.GetChanges(DataRowState.Modified); DataTable tbdel = tb.GetChanges(DataRowState.Deleted); adapter.InsertCommand = sqlcom.GetInsertCommand(); adapter.DeleteCommand = sqlcom.GetDeleteCommand(); adapter.UpdateCommand = sqlcom.GetUpdateCommand(); int i = 0; if (tb.GetChanges() != null) { i = adapter.Update(tb); } tb.AcceptChanges(); sqlcom.RefreshSchema(); //tb.Columns["path_step_item_id"].ColumnName = "Bsid"; //tb.Columns["step_item_kind_id"].ColumnName = "Parent_id"; //tb.Columns["notes"].ColumnName = "order_spec"; // sqltra.Commit(); connect.Close(); }
/// <summary> /// 修改数据库 /// </summary> /// <param name="sql"></param> /// <param name="tb"></param> private void databaseupdate(string sql, DataTable tb) { //数据更行到数据库 System.Data.SqlClient.SqlConnection connect = new System.Data.SqlClient.SqlConnection(); connect.ConnectionString = TrasenFrame.Forms.FrmMdiMain.Database.ConnectionString;// " server=x6x8-20100320QL\\SQLEXPRESS;database=trasen_Emr_test;UID=sa;Password=sa8920993"; connect.Open(); System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(); adapter.SelectCommand = new System.Data.SqlClient.SqlCommand(sql, connect); System.Data.SqlClient.SqlCommandBuilder sqlcom = new System.Data.SqlClient.SqlCommandBuilder(adapter); DataTable newtb = new DataTable(); newtb.TableName = "tb"; adapter.Fill(newtb); adapter.InsertCommand = sqlcom.GetInsertCommand(); adapter.DeleteCommand = sqlcom.GetDeleteCommand(); adapter.UpdateCommand = sqlcom.GetUpdateCommand(); int i = adapter.Update(tb); tb.AcceptChanges(); sqlcom.RefreshSchema(); connect.Close(); }
public void Execute(string connectionString, string query) { if (this._dataSet != null) { this._dataAdapter.Update(this._dataSet); } this._dataSet = null; this._dataAdapter = new System.Data.SqlClient.SqlDataAdapter(query, connectionString); this._dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; System.Data.SqlClient.SqlCommandBuilder commandBuilder = new System.Data.SqlClient.SqlCommandBuilder(this._dataAdapter); try { this._dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); this._dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); this._dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); } catch (InvalidOperationException exception) { string s = exception.Message; //TODO throw an application specific exception. MessageBox.Show("No primary key is defined for this query.\nThe results are Read-Only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.dataGrid.ReadOnly = true; } this._dataSet = new DataSet(); this._dataAdapter.Fill(this._dataSet); this.dataGrid.DataSource = this._dataSet.Tables[0]; this._dataSet.Tables[0].RowChanged += new DataRowChangeEventHandler(DataTable_RowChanged); this._dataSet.Tables[0].RowDeleted += new DataRowChangeEventHandler(DataTable_RowDeleted); }
static void compareAndUpdateData() { string conStr = @"Data Source=.\SQLExpress;Initial Catalog=FactoryFloorKeyStore_2;Integrated Security=True"; string selectCommandText = "select * from ProductKeyInfo"; string dataSetXmlFile = @"D:\backup\RDBMSXmlTest2_2.xml"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(conStr); System.Data.SqlClient.SqlCommand selectCmd = new System.Data.SqlClient.SqlCommand(selectCommandText); selectCmd.Connection = conn; System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(selectCmd); System.Data.SqlClient.SqlCommandBuilder builder = new System.Data.SqlClient.SqlCommandBuilder(adapter); if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } System.Data.DataSet dataSet = new System.Data.DataSet(); dataSet.ReadXml(dataSetXmlFile); dataSet.AcceptChanges(); System.Data.DataSet dataSet2 = new System.Data.DataSet(); adapter.Fill(dataSet2); //dataSet2.Merge(dataSet); //dataSet.Merge(dataSet2); //dataSet2 = dataSet.Clone(); List <string> dpkids = new List <string>(); foreach (System.Data.DataRow row in dataSet.Tables[0].Rows) { foreach (System.Data.DataRow row2 in dataSet2.Tables[0].Rows) { if (row["ProductKeyID"].ToString() == row2["ProductKeyID"].ToString()) { dpkids.Add(row["ProductKeyID"].ToString()); } } } int count = dataSet.Tables[0].Rows.Count;//dataSet2.Tables[0].Rows.Count; //for (int i = 0; i < count; i++) //{ // if (!dpkids.Contains(dataSet2.Tables[0].Rows[i]["ProductKeyID"].ToString())) // { // dataSet2.Tables[0].Rows[i].Delete(); // //dataSet2.Tables[0].Rows[i].AcceptChanges(); // Console.WriteLine(dataSet2.Tables[0].Rows[i].RowState); // //i--; // //count--; // } // else // { // dataSet2.Tables[0].Rows[i]["CreatedBy"] = "Rally"; // Console.WriteLine(dataSet2.Tables[0].Rows[i].RowState); // } //} for (int i = 0; i < count; i++) { //if (!dpkids.Contains(dataSet.Tables[0].Rows[i]["ProductKeyID"].ToString())) //{ // dataSet.Tables[0].Rows[i].Delete(); // //dataSet2.Tables[0].Rows[i].AcceptChanges(); // Console.WriteLine(dataSet.Tables[0].Rows[i].RowState); // //i--; // //count--; //} //else //{ // dataSet.Tables[0].Rows[i]["CreatedBy"] = "Rally"; // //dataSet.Tables[0].Rows[i].SetModified(); // Console.WriteLine(dataSet.Tables[0].Rows[i].RowState); //} } //dataSet2.Tables[0].AcceptChanges(); //dataSet2.AcceptChanges(); //int result = adapter.Update(dataSet); Console.WriteLine(builder.GetDeleteCommand().CommandText); Console.WriteLine(builder.GetInsertCommand().CommandText); Console.WriteLine(builder.GetUpdateCommand().CommandText); //int result = adapter.Update(dataSet2); //int result = adapter.Update(dataSet); foreach (System.Data.DataColumn col in dataSet2.Tables[0].PrimaryKey) { Console.WriteLine(col.ColumnName); } if (conn.State != System.Data.ConnectionState.Closed) { conn.Close(); } //Console.WriteLine(result); Console.Read(); }
public static void UpdateDataTable(System.Data.DataTable dt, string strSQL) { using (System.Data.Common.DbConnection connection = GetConnection()) { using (System.Data.Common.DbDataAdapter daInsertUpdate = new System.Data.SqlClient.SqlDataAdapter()) { using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand()) { cmdSelect.CommandText = strSQL; System.Data.Common.DbCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(); cb.DataAdapter = daInsertUpdate; daInsertUpdate.SelectCommand = cmdSelect; daInsertUpdate.UpdateCommand = cb.GetUpdateCommand(); daInsertUpdate.DeleteCommand = cb.GetDeleteCommand(); daInsertUpdate.InsertCommand = cb.GetInsertCommand(); daInsertUpdate.Update(dt); } // End Using cmdSelect } // End Using daInsertUpdate } // End Using con }
public static void InsertUpdateDataTable(string strTableName, System.Data.DataTable dt) { string connectionString = GetConnectionString(); string strSQL = string.Format("SELECT * FROM [{0}] WHERE 1 = 2 ", strTableName.Replace("]", "]]")); using (System.Data.Common.DbConnection connection = GetConnection()) { using (System.Data.Common.DbDataAdapter daInsertUpdate = new System.Data.SqlClient.SqlDataAdapter()) { using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand()) { cmdSelect.CommandText = strSQL; System.Data.Common.DbCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(); cb.DataAdapter = daInsertUpdate; daInsertUpdate.SelectCommand = cmdSelect; daInsertUpdate.InsertCommand = cb.GetInsertCommand(); daInsertUpdate.UpdateCommand = cb.GetUpdateCommand(); daInsertUpdate.DeleteCommand = cb.GetDeleteCommand(); daInsertUpdate.Update(dt); } // End Using cmdSelect } // End Using daInsertUpdate } // End Using connection }