/// <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();
}
private void inputAbsBox_KeyDown(object sender, KeyEventArgs e)
{
if (e.Key == Key.Enter)
{
var value = ItemRow[ColumnName];
ColumnValue.Absolute = (T)Convert.ChangeType(value, typeof(T));
ItemRow[ColumnName] = value;
var parent = Application.Current.Windows[0] as MainWindow;
var builder = new System.Data.SqlClient.SqlCommandBuilder(inventory.Adapter);
inventory.Adapter.UpdateCommand = builder.GetUpdateCommand();
inventory.Update(ItemRow);
parent.DropClickMenuIfActive();
}
}
/// <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);
}
private void inputBox_KeyDown(object sender, KeyEventArgs e)
{
if (e.Key == Key.Enter)
{
var value = ItemRow.Quantity;
if (changeType)
{
value = int.Parse(inputBox.Text);
}
else
{
value += int.Parse(inputBox.Text);
}
ItemRow.Quantity = value;
var parent = Application.Current.Windows[0] as MainWindow;
var builder = new System.Data.SqlClient.SqlCommandBuilder(inventory.Adapter);
inventory.Adapter.UpdateCommand = builder.GetUpdateCommand();
inventory.Update(ItemRow);
parent.DropClickMenuIfActive();
}
}
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();
}
/// <summary>
/// 打印后更新打印状态
/// </summary>
///
private bool UpdatePrintState(string labelNo, string TimeID, string LongDt, List <string> ls)
{
//MessageBox.Show(string.Format("exec [dbo].[bl_IVRprintLog] '{0}','{1}','{2}','{3}','{4}','{5}','{6}' ", labelNo, piv.userID, "", TimeID, piv.dateTimePicker1.Value.ToString("yyyy-MM-dd") + " " + piv.comboBox1.SelectedItem + " 批次：" + piv.comboBox2.SelectedItem, "8888" + LongDt, "9999" + LongDt));
try
{
string sb1 = string.Format(" select DEmployeeName from DEmployee where DEmployeeID='{0}' ", piv.userID);
string sb2 = string.Format(" select IVRecordID,IVStatus,PrintDT,PrinterID,PrinterName from IVRecord where IVStatus=0 and LabelNo in({0})", labelNo);
string DEmployeeName = piv.dbHelp.GetPIVAsDB(sb1).Tables[0].Rows[0][0].ToString();
string PrintDT = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(piv.dbHelp.DatebasePIVAsInfo()))
{
using (System.Data.SqlClient.SqlCommand scm = new System.Data.SqlClient.SqlCommand(sb2, sc))
{
using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(scm))
{
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
ds.Tables[0].PrimaryKey = new DataColumn[] { ds.Tables[0].Columns["IVRecordID"] };
foreach (DataRow dr in ds.Tables[0].Rows)
{
dr["IVStatus"] = 3;
dr["PrintDT"] = PrintDT;
dr["PrinterID"] = piv.userID;
dr["PrinterName"] = DEmployeeName;
}
using (System.Data.SqlClient.SqlCommandBuilder scb = new System.Data.SqlClient.SqlCommandBuilder(sda))
{
scb.GetUpdateCommand();
using (DataSet cds = ds.GetChanges(DataRowState.Modified))
{
int cs = 0;
a : int ret = scb.DataAdapter.Update(cds);
if (ret <= 0)
{
if (cs < 5)
{
cs = cs + 1;
piv.NowST = string.Format("更新瓶签失败,正在{0}/5次重试", cs);
goto a;
}
else
{
MessageBox.Show("当次打印未成功更新瓶签状态，请作废此批标签！！！");
return(false);
}
}
}
}
}
}
}
}
StringBuilder sb = new StringBuilder(2048);
sb.AppendLine("IF(OBJECT_ID('IVRecord_Print_AllEmp') is null) BEGIN ");
sb.AppendLine("CREATE TABLE [dbo].[IVRecord_Print_AllEmp]([LabelNo] [varchar](32) NOT NULL,[DrugQRCode] [varchar](100) NOT NULL,[ArrDrugUserCode] [varchar](50) NULL,");
sb.AppendLine("[PZDrugUserCode] [varchar](50) NULL,[PackDrugUserCode] [varchar](50) NULL, CONSTRAINT [PK_IVRecord_Print_AllEmp] PRIMARY KEY CLUSTERED ([LabelNo] ASC,[DrugQRCode] ASC) ");
sb.AppendLine("WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END ");
piv.dbHelp.SetPIVAsDB(sb.ToString());
using (System.Data.SqlClient.SqlCommand scm1 =
new System.Data.SqlClient.SqlCommand(
string.Format("select * from [IVRecord_Print] where LabelNo in ({0})", labelNo), sc),
scm2 = new System.Data.SqlClient.SqlCommand("select * from [IVRecord_Print_AllEmp] where 1!=1 ", sc))
{
using (System.Data.SqlClient.SqlDataAdapter sda1 = new System.Data.SqlClient.SqlDataAdapter(scm1), sda2 = new System.Data.SqlClient.SqlDataAdapter(scm2))
{
using (DataSet ds1 = new DataSet(), ds2 = new DataSet())
{
sda1.Fill(ds1);
sda2.Fill(ds2);
ds2.Tables[0].PrimaryKey = new DataColumn[] { ds2.Tables[0].Columns[0], ds2.Tables[0].Columns[1] };
foreach (string s in ls)
{
DataRow dr = ds1.Tables[0].NewRow();
dr["LabelNo"] = s;
dr["PrintDT"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
dr["PrintCode"] = piv.userID;
dr["ReprintExplain"] = piv.WhyRePrint;
dr["PrintCount"] = ds1.Tables[0].Select(string.Format("LabelNo='{0}'", s)).Length;
dr["PrintNO"] = TimeID;
dr["Condition"] = piv.dateTimePicker1.Value.ToString("yyyy-MM-dd") + " " + piv.comboBox1.SelectedItem + " 批次：" + piv.comboBox2.SelectedItem;
dr["DrugQRCode"] = "8888" + LongDt;
dr["OrderQRCode"] = "9999" + LongDt;
dr["DrugType"] = 0;
ds1.Tables[0].Rows.Add(dr);
DataRow dre = ds2.Tables[0].NewRow();
dre["LabelNo"] = s;
dre["DrugQRCode"] = "8888" + LongDt;
dre["ArrDrugUserCode"] = piv.ArrDrugUserCode;
dre["PZDrugUserCode"] = piv.PZDrugUserCode;
dre["PackDrugUserCode"] = piv.PackDrugUserCode;
ds2.Tables[0].Rows.Add(dre);
}
using (System.Data.SqlClient.SqlCommandBuilder scb1 = new System.Data.SqlClient.SqlCommandBuilder(sda1), scb2 = new System.Data.SqlClient.SqlCommandBuilder(sda2))
{
scb1.GetInsertCommand();
scb1.DataAdapter.Update(ds1.GetChanges(DataRowState.Added));
scb2.GetInsertCommand();
scb2.DataAdapter.Update(ds2.GetChanges(DataRowState.Added));
}
}
}
}
piv.dbHelp.SetPIVAsDB(string.Format("UPDATE [IVRecord] SET IVStatus=3,PrinterID='{0}',PrinterName='{1}',PrintDT=GETDATE() WHERE IVStatus=0 AND EXISTS(SELECT 'X' FROM IVRecord_Print IVP WHERE IVP.LabelNo=IVRecord.LabelNo AND DrugQRCode !='' )", piv.userID, DEmployeeName));
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
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
}