/// <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
}