/// <summary>
/// Permet de mettre a jour la base de donnees
/// </summary>
/// <param name="request">requete ayant permis d'avoir la table de base</param>
/// <param name="table">la table dans laquelle il y a les modifications</param>
/// <returns>Le nombre de lignes traitees</returns>
private int UpdateCommandBuilder(string request, DataTable table) {
int res = 0;
using(SqlConnection sqlConnection = new SqlConnection(ConnectString)) {
sqlConnection.Open();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
SqlCommand sqlCommand = new SqlCommand(request, sqlConnection, sqlTransaction);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand();
sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();
sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
try {
res = sqlDataAdapter.Update(table);
sqlTransaction.Commit();
} catch (System.Data.SqlClient.SqlException exc) {
sqlTransaction.Rollback();
}
}
return res;
}
public void test()
{
try
{
DataTable dt = new DataTable();
conn.Connection.Open();
SqlCommand comm = new SqlCommand("select * from user_login", conn.Connection);
SqlDataAdapter adapter = new SqlDataAdapter(comm);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
//将结果存储在DataTable中
adapter.Fill(dt);
//对DataTable任意修改
//....code...
//dt.Rows[0].Delete();
//缺少这个就会更新失败
adapter.UpdateCommand = cmdBuilder.GetUpdateCommand();
//更新数据库
adapter.Update(dt);
dt.AcceptChanges();
}
finally
{
if (conn.Connection.State == ConnectionState.Open)
conn.Connection.Close();
}
}
public static void BuildCommandObjects(string conString, string cmdtxt, ref SqlCommand insertCmd, ref SqlCommand updateCmd, ref SqlCommand deleteCmd)
{
if ((conString == null) || (conString.Trim().Length == 0)) throw new ArgumentNullException( "conString" );
if ((cmdtxt == null) || (cmdtxt.Length == 0)) throw new ArgumentNullException( "cmdtxt" );
try
{
using (SqlConnection sqlConnection = new SqlConnection(conString))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdtxt, sqlConnection))
{
using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter))
{
insertCmd = cmdBuilder.GetInsertCommand();
updateCmd = cmdBuilder.GetUpdateCommand();
deleteCmd = cmdBuilder.GetDeleteCommand();
}
}
}
}
catch //(Exception ex)
{
throw;// new MyException(string.Format("Building command objects for table {0} failed", tableName), ex);
}
}
/// <summary>
/// Permet de faire une requète de type UPDATE ou INSERT INTO
/// </summary>
/// <param name="rqt"> Requète</param>
/// <param name="dt"> Database à modifier</param>
/// <returns></returns>
public int Update(string rqt, DataTable dt)
{
if (sqlConnect != null)
{
SqlTransaction trans = sqlConnect.BeginTransaction();
SqlCommand sqlCmd = new SqlCommand(rqt, sqlConnect, trans);
SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
SqlCommandBuilder build = new SqlCommandBuilder(sqlDA);
sqlDA.UpdateCommand = build.GetUpdateCommand();
sqlDA.InsertCommand = build.GetInsertCommand();
sqlDA.DeleteCommand = build.GetDeleteCommand();
sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
try
{
int res = sqlDA.Update(dt);
trans.Commit();
return res;
}
catch (DBConcurrencyException)
{
trans.Rollback();
}
}
return 0;
}
private static void AtualizarPrecosViaAdapter(SqlConnection conn)
{
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Produto";
using (var da = new SqlDataAdapter(cmd))
{
var builder = new SqlCommandBuilder(da);
builder.ConflictOption = ConflictOption.CompareRowVersion;
da.UpdateCommand = builder.GetUpdateCommand();
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
var preco = double.Parse(row["Preco"].ToString());
preco += 10;
row["Preco"] = preco;
}
da.Update(ds);
}
}
/// <summary>
/// 保存表修改到数据库
/// </summary>
/// <returns>保存的行数</returns>
public int SaveTable()
{
int nRet = 0;
if (mTableChanged || true)
{
try
{
mAdapter = new SqlDataAdapter(mSqlCmd, mConn);
SqlCommandBuilder cmd = new SqlCommandBuilder(mAdapter);
mAdapter.UpdateCommand = cmd.GetUpdateCommand();
DataTable tbl = mTable.GetChanges();
if (tbl != null && tbl.Rows.Count > 0)
{
nRet = mAdapter.Update(tbl);
}
mTable.AcceptChanges();
mTableChanged = false;
}
catch(Exception ex)
{
throw ex;
}
}
return nRet;
}
/// <summary>
/// 保存数据表所做的更改（带事务）
/// </summary>
/// <param name="conn">SqlTransaction</param>
/// <param name="trans">SqlTransaction</param>
/// <param name="strSQL">需要更新的表的SQL语句</param>
/// <param name="dt">需要更新的DataTable</param>
public static void SaveChangesOnTrans(SDC.SqlConnection conn, SDC.SqlTransaction trans, string strSQL, SD.DataTable dt)
{
//this.Validate();
try
{
using (sqlcmd = new SDC.SqlCommand(strSQL, conn, trans))
{
using (sqladp = new SDC.SqlDataAdapter(sqlcmd))
{
//sqladp.InsertCommand.Transaction = trans;
using (sqlcmdbd = new SDC.SqlCommandBuilder(sqladp))
{
sqlcmdbd.ConflictOption = SD.ConflictOption.CompareRowVersion;
sqladp.InsertCommand = sqlcmdbd.GetInsertCommand();
sqladp.UpdateCommand = sqlcmdbd.GetUpdateCommand();
sqladp.DeleteCommand = sqlcmdbd.GetDeleteCommand();
sqladp.Update(dt);
}
}
}
}
catch (SDC.SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public void test()
{
SqlConnection cn = new SqlConnection();
DataSet MesInscriptionsDataSet = new DataSet();
SqlDataAdapter da;
SqlCommandBuilder cmdBuilder;
//Set the connection string of the SqlConnection object to connect
//to the SQL Server database in which you created the sample
//table.
cn.ConnectionString = Splash.STR_CON;
cn.Open();
//Initialize the SqlDataAdapter object by specifying a Select command
//that retrieves data from the sample table.
da = new SqlDataAdapter("select * from Inscription", cn);
//Initialize the SqlCommandBuilder object to automatically generate and initialize
//the UpdateCommand, InsertCommand, and DeleteCommand properties of the SqlDataAdapter.
cmdBuilder = new SqlCommandBuilder(da);
//Populate the DataSet by running the Fill method of the SqlDataAdapter.
da.Fill(MesInscriptionsDataSet, "Inscription");
//Display the Update, Insert, and Delete commands that were automatically generated
//by the SqlCommandBuilder object.
tbx_debug.Text = "Update command Generated by the Command Builder : \r\n";
tbx_debug.Text += "================================================= \r\n";
tbx_debug.Text += cmdBuilder.GetUpdateCommand().CommandText;
tbx_debug.Text += " \r\n";
tbx_debug.Text += "Insert command Generated by the Command Builder : \r\n";
tbx_debug.Text += "================================================== \r\n";
tbx_debug.Text += cmdBuilder.GetInsertCommand().CommandText;
tbx_debug.Text += " \r\n";
tbx_debug.Text += "Delete command Generated by the Command Builder : \r\n";
tbx_debug.Text += "================================================== \r\n";
tbx_debug.Text += cmdBuilder.GetDeleteCommand().CommandText;
tbx_debug.Text += " \r\n";
//Write out the value in the CustName field before updating the data using the DataSet.
tbx_debug.Text += "Année before Update : " + MesInscriptionsDataSet.Tables["Inscription"].Rows[0]["année"] + "\r\n";
//Modify the value of the CustName field.
MesInscriptionsDataSet.Tables["Inscription"].Rows[0]["année"] = "2099" + "\r\n";
//Post the data modification to the database.
da.Update(MesInscriptionsDataSet, "Inscription");
tbx_debug.Text += "Année updated successfully \r\n";
//Close the database connection.
cn.Close();
//Pause
Console.ReadLine();
}
//Generic SQL Delete method
public int Delete(string strTable, string strWhere)
{
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;
DataRow[] aDr = null;
string strSQL = "";
int intResult = 0;
try
{
if (CreateConnection())
{
strSQL = "SELECT * FROM " + strTable;
da = new SqlDataAdapter();
//Get the dataset using the Select query that has been composed
ds = GetDataSet(strTable, strSQL, ref da);
//use the command builder to generate the Delete and Update commands that we'll need later
cb = new SqlCommandBuilder(da);
da.DeleteCommand = cb.GetDeleteCommand();
da.UpdateCommand = cb.GetUpdateCommand();
//Get the row to Delete using where clause, if none supplied get all rows
if (strWhere.Length > 0)
{
aDr = ds.Tables[strTable].Select(strWhere);
}
else
{
aDr = ds.Tables[strTable].Select();
}
if (aDr != null)
{
//Loop through each row and Delete it
foreach (DataRow dr in aDr)
{
dr.Delete();
}
}
//Update table and determine number of rows affected
intResult = da.Update(ds, strTable);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
CloseConnection();
}
return intResult;
}
public object Execute(SqlCommand Command){
/* It will let user close connection when finished */
SqlDataAdapter ResultantAdapter = new SqlDataAdapter(Command);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(ResultantAdapter);
ResultantAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand(true);
ResultantAdapter.DeleteCommand = cmdBuilder.GetDeleteCommand(true);
ResultantAdapter.InsertCommand = cmdBuilder.GetInsertCommand(true);
return ResultantAdapter;
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
Adp = new SqlDataAdapter("select * from Product_Avail where title=@p and PROD='N'", con);
Adp.SelectCommand.Parameters.AddWithValue("@p", ddtitle.Text);
DataSet ds = new DataSet();
Adp.Fill(ds, "prod");
DataRow dr = ds.Tables["prod"].Rows[0];
dr[4] = "Y";
SqlCommandBuilder cb = new SqlCommandBuilder(Adp);
Adp.UpdateCommand = cb.GetUpdateCommand();
Adp.Update(ds, "prod");
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Product Moved To Production!');window.location='Homepage.aspx';</script>'");
}
catch (SqlException ex)
{
switch (ex.Number)
{
case 4060: // Invalid Database
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully !');window.location='Homepage.aspx';</script>'");
break;
case 18456: // Login Failed
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
break;
case 547: // ForeignKey Violation
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
break;
case 2627: // Unique Index/ Primary key Violation/ Constriant Violation
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully!');window.location='Homepage.aspx';</script>'");
break;
case 2601: // Unique Index/Constriant Violation
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
break;
default:
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
break;
}
}
catch (IndexOutOfRangeException)
{
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
}
}
public void InitializeDataAdapter()
{
connection.ConnectionString = connectionString ;
SqlCommand selectCommand = new SqlCommand("SELECT * FROM Student", connection);
dataAdapter.SelectCommand = selectCommand;
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
dataAdapter.DeleteCommand = builder.GetDeleteCommand();
dataAdapter.UpdateCommand = builder.GetUpdateCommand();
dataAdapter.InsertCommand = builder.GetInsertCommand();
}
/// <summary>
/// 这个静态构造器将读取web.config中定义的全部连接字符串.
/// 链接和适配器将同时指向同一db, 因此只需要创建一次.
/// </summary>
static DBProcess()
{
string constr = ConfigurationManager.ConnectionStrings["MyConn"]
.ConnectionString;
conn = new SqlConnection(constr);
string command = "select * from tb_personInfo";
adapter = new SqlDataAdapter(command, conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
builder.GetDeleteCommand(true);
builder.GetInsertCommand(true);
builder.GetUpdateCommand(true);
}
private void btnCommandBuilder_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=.\SqlExpress;Integrated Security=true;Initial Catalog=Northwind");
string strSQL = "Select * from Categories";
SqlDataAdapter dt = new SqlDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
dt.Fill(ds);
SqlCommandBuilder cb = new SqlCommandBuilder(dt);
Console.WriteLine(cb.GetUpdateCommand().CommandText.ToString());
conn.Close();
grvDataView.DataSource = ds.Tables[0];
//grvDataView.DataBind();
}
public RedTable(string tablename, string query, RedContext context, string searchQuery=null )
: base(tablename)
{
this.query = query;
context.Provider.OpenConnection();
dataAdapter = new SqlDataAdapter(query, context.Provider.Connection);
var builder = new SqlCommandBuilder(dataAdapter);
FillAdapter(context);
dataAdapter.UpdateCommand = builder.GetUpdateCommand();
context.Provider.CloseConnection();
ComboBoxes = new Dictionary<string, RedComboBox>();
columnHeaders = new Dictionary<string, string>();
SearchQuery = searchQuery;
}
/// <summary>
/// 保存数据表所做的更改（不带事务）
/// </summary>
/// <param name="ConnStr">连接字符串</param>
/// <param name="strSQL">SQL查询语句</param>
/// <param name="dt">DataGridView的DataSource的DataTable</param>
public static void SaveChanges(string ConnStr, string strSQL, SD.DataTable dt)
{
//this.Validate();
try
{
using (sqlconn = new SDC.SqlConnection(ConnStr))
{
sqlconn.Open();
using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn))
{
using (sqladp = new SDC.SqlDataAdapter(sqlcmd))
{
using (sqlcmdbd = new SDC.SqlCommandBuilder(sqladp))
{
sqlcmdbd.ConflictOption = SD.ConflictOption.CompareAllSearchableValues;
sqladp.InsertCommand = sqlcmdbd.GetInsertCommand();
sqladp.UpdateCommand = sqlcmdbd.GetUpdateCommand();
sqladp.DeleteCommand = sqlcmdbd.GetDeleteCommand();
sqladp.Update(dt);
}
}
}
}
}
catch (SDC.SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlconn != null)
{
if (sqlconn.State != SD.ConnectionState.Closed)
{
sqlconn.Close();
}
sqlconn.Dispose();
}
}
}
public void updateAll()
{
if (ds.HasChanges())
{
SqlConnection conexion = new SqlConnection(strConexion);
conexion.Open();
SqlDataAdapter ad = new SqlDataAdapter("select * from usuarios", conexion); // se hace la select para generar automáticamente le comando select, update y delete
SqlCommandBuilder sqlcb = new SqlCommandBuilder(ad);
ad.InsertCommand = sqlcb.GetInsertCommand();
ad.UpdateCommand = sqlcb.GetUpdateCommand();
ad.DeleteCommand = sqlcb.GetDeleteCommand();
conexion.Close();
}
}
public void ExtractTableParameters(string TableName, IDbDataAdapter adapter,
out DatabaseCache InsertCache,
out DatabaseCache DeleteCache,
out DatabaseCache UpdateCache,
out DatabaseCache IsExistCache,
out DataTable dt
)
{
adapter.SelectCommand.CommandText = "select top 1 * from " + TableName;
DataSet ds = new DataSet();
dt = adapter.FillSchema(ds, SchemaType.Source)[0];
dt.TableName = TableName;
SqlCommandBuilder builder = new SqlCommandBuilder(adapter as SqlDataAdapter);
builder.ConflictOption = ConflictOption.OverwriteChanges;
//builder.SetAllValues = false;
SqlCommand InsertCmd = builder.GetInsertCommand(true);
builder.ConflictOption = ConflictOption.OverwriteChanges;
InsertCache = new DatabaseCache(InsertCmd.CommandText, InsertCmd.Parameters);
InsertCache.CurrentTable = dt;
foreach (DataColumn c in dt.Columns)
{
if (c.AutoIncrement)
{
InsertCache.IsHaveAutoIncrement = true;
InsertCache.SQL += ";Select @@IDENTITY;";
break;
}
}
SqlCommand UpdateCmd = builder.GetUpdateCommand(true);
UpdateCache = new DatabaseCache(UpdateCmd.CommandText, UpdateCmd.Parameters);
UpdateCache.CurrentTable = dt;
SqlCommand DeleteCmd = builder.GetDeleteCommand(true);
DeleteCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
DeleteCache.CurrentTable = dt;
IsExistCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
IsExistCache.CurrentTable = dt;
IsExistCache.SQL = IsExistCache.SQL.Replace("DELETE FROM [" + TableName + "]", "Select count(1) from [" + TableName + "] with(nolock) ");
}
private void Save(object sender, RoutedEventArgs e)
{
try
{
using (SqlConnection MyConn = new SqlConnection(myConnectionString))
{
SqlCommandBuilder builder = new SqlCommandBuilder(Mysqlad);
Mysqlad.UpdateCommand = builder.GetUpdateCommand();
int i = Mysqlad.Update(dataset.Tables[usr_table]);
MessageBox.Show(i.ToString() + " updated ");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void updateAll()
{
if (ds.HasChanges())
{
SqlConnection conexion = new SqlConnection(strConexion);
conexion.Open();
SqlDataAdapter ad = new SqlDataAdapter("select * from usuarios left join Historiales on usuarios.NssUsuario = Historiales.usuario", conexion); // se hace la select para generar automáticamente le comando select, update y delete
SqlCommandBuilder sqlcb = new SqlCommandBuilder(ad);
ad.InsertCommand = sqlcb.GetInsertCommand();
ad.UpdateCommand = sqlcb.GetUpdateCommand();
ad.DeleteCommand = sqlcb.GetDeleteCommand();
ad.Update(ds.Usuarios);
conexion.Close();
ds.AcceptChanges(); // Elimina las marcas de inserción, actualización o borrado del dataset
}
}
public static void SetLastID(string tblname, string field, int id, SqlConnection Conn)
{
string sql = string.Format("SELECT * FROM sys_id_pool WHERE tablename='{0}' AND fieldname='{1}'", tblname, field);
DataTable tbl = Helper.GetDataTable(sql, Conn);
DataRow row = null;
try
{
SqlDataAdapter adp = GetAdp(sql);
if (tbl.Rows.Count <= 0)
{
row = tbl.NewRow();
row.BeginEdit();
row["tablename"] = tblname;
row["fieldname"] = field;
row["lastid"] = id;
row.EndEdit();
tbl.Rows.Add(row);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
adp.InsertCommand = cmdBuilder.GetInsertCommand();
}
else
{
row = tbl.Rows[0];
row["lastid"] = id;
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
adp.UpdateCommand = cmdBuilder.GetUpdateCommand();
}
int val = adp.Update(tbl);
tbl.AcceptChanges();
}
catch (Exception ex)
{
string err = string.Format("在 SetLastID() 时发生错误。\r\n\r\n错误信息为：{0}", ex.Message);
MessageBox.Show(err, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
tbl.RejectChanges();
}
}
private bool EditRow(string ip, string machinaename, string username, DateTime dtStart, DateTime dtEnd, string smodname, string action, string comment)
{
try
{
DataRow row = m_datatable.Rows.Find(m_findkey);
if (row == null) return false;
row.BeginEdit();
row["userip"] = ip;
row["usermachinename"] = machinaename;
row["username"] = username;
if (dtStart != DateTime.MinValue)
row["starttime"] = dtStart;
if (dtEnd != DateTime.MinValue)
row["endtime"] = dtEnd;
if (smodname != null)
row["modname"] = smodname;
if (action != null)
row["useraction"] = action;
if (comment != null)
row["comment"] = comment;
row.EndEdit();
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(m_adp);
m_adp.UpdateCommand = cmdBuilder.GetUpdateCommand();
int val = m_adp.Update(m_datatable);
m_datatable.AcceptChanges();
if (val == 0)
{
Helper.AddLog("log 失败");
return false;
}
}
catch (Exception ex)
{
m_datatable.RejectChanges();
Helper.AddLog(ex.Message);
return false; ;
}
return true;
}
///// <summary>
///// permet de mettre à jour la table course
///// </summary>
public void MajCourse(int i, string pHippodrome, DateTime pDate, int pDistance)
{
// on remplit la propriété dt (contenant la Data Table concernée) avec les champs textbox (sauf l'id bien sûr!!!)
dt.Rows[i]["hippodrome"] = pHippodrome;
dt.Rows[i]["date"] = pDate;
dt.Rows[i]["distance"] = pDistance;
// on utilise le command builder qui a généré automatiquement les ordres insert, delete et update d'après le contenu du da
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);
commandBuilder.GetUpdateCommand();
try
{
da.Update(dt); // écrit dans la base de données
dt.AcceptChanges(); //
}
catch (Exception ex)
{
dt.RejectChanges(); // on annule l ajout dans la dataTable car il s'est mal passé
throw new Exception(" Erreur suppression SqlServeur \n" + ex.Message);
}
}
public void UpdateAllPriceTbl()
{
try
{
int cannotfindcount = 0;
string cannotfindmsg = "\r\n\r\n找不到以下材料：\r\n\r\n";
System.Data.DataTable tblPrice = GetDataTable("SELECT * FROM CraftMaterialPrice", Conn);
System.Data.DataTable tblOther = GetDataTable("SELECT * FROM Other", Conn);
foreach (DataRow row in tblPrice.Rows)
{
int craftprice = Convert.ToInt32(row["Price"]);
int craftid = Convert.ToInt32(row["ID"]);
string craftname = row["Name"].ToString();
DataRow rowOther = tblOther.Rows.Find(craftid);
if (rowOther == null)
{
string strTmp = string.Format("{0} {1} {2}\r\n", craftname, craftid, craftprice);
cannotfindmsg += strTmp;
cannotfindcount++;
}
else
{
rowOther["Price"] = craftprice;
}
}
SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM Other", Conn);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
adp.UpdateCommand = cmdBuilder.GetUpdateCommand();
int val = adp.Update(tblOther);
tblOther.AcceptChanges();
cannotfindmsg = cannotfindcount > 0 ? cannotfindmsg : string.Empty;
MessageBox.Show("更新Other表成功。" + cannotfindmsg, "提示");
}
catch (Exception ex)
{
MessageBox.Show("更新Other表失败！\r\n错误信息为：" + ex.Message);
}
}
public void Change(DataTable tablechange)
{
if (string.IsNullOrWhiteSpace(tablechange.TableName))
{
throw new Exception("ChangeSQL tablename error");
}
SqlDataReader read = null;
//SqlCommand cmd = null;
SqlDataAdapter adapter = null;
try
{
this.sqlMutex.WaitOne();
if (conn == null)
{
this.Open();
}
adapter = new System.Data.SqlClient.SqlDataAdapter();
adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("SELECT * FROM " + tablechange.TableName, this.conn);
System.Data.SqlClient.SqlCommandBuilder cmdBldr = new System.Data.SqlClient.SqlCommandBuilder(adapter);
adapter.UpdateCommand = cmdBldr.GetUpdateCommand();
//System.Data.SqlClient.SqlCommand cmd = cmdBldr.GetUpdateCommand();
//Console.WriteLine(cmd.ToString());
adapter.Update(tablechange);
}
finally
{
try { adapter.Dispose(); } catch { }
if (transac == null)
{
this.Close();
}
this.sqlMutex.ReleaseMutex();
}
}
private void ButtonSaveChanges_Click(object sender, RoutedEventArgs e)
{
try
{
MainWindow.connection.Open();
SqlDataAdapter da = new SqlDataAdapter(@"SELECT * From [" + this.TablesOfDB.SelectedItem.ToString() + "];", MainWindow.connection);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.Update(dt);
MessageBox.Show("Table saved.");
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
finally
{
MainWindow.connection.Close();
}
}
private void btnComandBuilder2_Click(object sender, EventArgs e)
{
String sql;
//CommandBuilder doesn't support multiple table queries
sql = @"Select Orders.OrderDate, Customers.CompanyName from Orders ";
sql+="Join Customers On Orders.CustomerID=Customers.CustomerID";
sql = @"SELECT* FROM Categories";
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SqlExpress;Integrated Security=true;Initial Catalog=Northwind"))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
{
//This is supported only against single tables.
using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
{
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
Console.WriteLine(builder.GetUpdateCommand().CommandText);
Console.WriteLine(builder.GetInsertCommand().CommandText);
Console.WriteLine(builder.GetDeleteCommand().CommandText);
}
}
}
}
protected void btnSaveProduct_Click(object sender, EventArgs e)
{
try
{
Adp = new SqlDataAdapter("select * from Products where Product_ID=@p", con);
Adp.SelectCommand.Parameters.AddWithValue("@p", a);
DataSet ds = new DataSet();
Adp.Fill(ds, "prod");
DataRow dr = ds.Tables["prod"].Rows[0];
//dr[0] = txtProductID.Text;
dr[1] = txtProductName.Text;
dr[2] = txtDescription.Text;
dr[3] = txtEffDate.Text;
//change this to ONLY DATE!
dr[4] = txtEndDate.Text;
dr[5] = ddlServiceType.SelectedValue;
dr[6] = "N";
dr[8] = DateTime.Now.ToString();
SqlCommandBuilder cb = new SqlCommandBuilder(Adp);
Adp.UpdateCommand = cb.GetUpdateCommand();
Adp.Update(ds, "prod");
}
catch (SqlException ex)
{
Console.WriteLine("Couldnot update" + ex.Message);
}
catch (FormatException fx)
{
Console.WriteLine("Couldnot update" + fx.Message);
}
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Product Modified Successfully!');window.location='NewPCR.aspx';</script>'");
}
private void VehicleInfo_Load(object sender, EventArgs e)
{
//取数据~~
vehicleInfo = vehicle_Management_SystemDataSet1.Vehicle;
string queryString = "select * from " + vehicleInfo.TableName;
adapter = new SqlDataAdapter(
queryString, Properties.Settings.Default.Vechicle_Management_SystemConnectionString);
SqlCommandBuilder builer = new SqlCommandBuilder(adapter);
adapter.InsertCommand = builer.GetInsertCommand();
adapter.DeleteCommand = builer.GetDeleteCommand();
adapter.UpdateCommand = builer.GetUpdateCommand();
adapter.Fill(vehicleInfo);
//讲英文列名改为中文~~
vehicleInfo.Columns["vehicle_id"].ColumnName = "车辆编号";
vehicleInfo.Columns["type"].ColumnName = "车辆型号";
vehicleInfo.Columns["capacity"].ColumnName = "车容量";
vehicleInfo.Columns["plate_id"].ColumnName = "车牌号";
vehicleInfo.Columns["buyingCar_id"].ColumnName = "购车批次";
vehicleInfo.Columns["parking_place"].ColumnName = "停车地点";
vehicleInfo.Columns["enable"].ColumnName = "是否停用";
//数据与控件的绑定~~
bindingSource1.DataSource = vehicleInfo;
bindingNavigator1.BindingSource = bindingSource1;
dataGridView1.DataSource = bindingSource1;
//属性显示顺序~~
dataGridView1.Columns["车牌号"].DisplayIndex = 1;
//不允许用户直接在最下面的行添加新行
dataGridView1.AllowUserToAddRows = false;
//不允许用户直接按Delete键删除行
dataGridView1.AllowUserToDeleteRows = false;
//设为只读，不可直接更改~~
dataGridView1.ReadOnly = true;
}
public override DataTable GetRelatedDataTable(DataRow dataRow, string relatedTableName)
{
DataObject dataObject = (from p in _dictionary.dataObjects
where p.tableName == dataRow.Table.TableName
select p).FirstOrDefault();
DataObject relatedDataObject = (from p in _dictionary.dataObjects
where p.tableName == relatedTableName
select p).FirstOrDefault();
string relationshipType = (from p in dataObject.dataRelationships
where p.relatedObjectName == relatedDataObject.objectName
select p.relationshipType.ToString()).FirstOrDefault();
IList<string> dataPropertyNames = (from p in dataObject.dataRelationships
where p.relatedObjectName == relatedDataObject.objectName
select p.propertyMaps.FirstOrDefault().dataPropertyName).ToList<string>();
IList<string> relatedPropertyNames = (from p in dataObject.dataRelationships
where p.relatedObjectName == relatedDataObject.objectName
select p.propertyMaps.FirstOrDefault().relatedPropertyName).ToList<string>();
string query = string.Empty;
string tempqry = string.Empty;
string qrySeparator = "";
for (int i = 0; i < relatedPropertyNames.Count; i++)
{
if (tempqry.Length > 0)
qrySeparator = " and ";
tempqry = qrySeparator + relatedPropertyNames[i] + " = '" + dataRow[dataPropertyNames[i]] + "'";
}
try
{
if (relationshipType.ToUpper() == "ONETOONE")
query = "select Top 1 * from " + relatedTableName + " where " + tempqry;
else
query = "select * from " + relatedTableName + " where " + tempqry;
ConnectToSqL();
_adapter = new SqlDataAdapter();
_adapter.SelectCommand = new SqlCommand(query, _conn);
_command = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = _command.GetUpdateCommand();
DataSet dataSet = new DataSet();
_adapter.Fill(dataSet, relatedTableName);
DataTable dataTable = dataSet.Tables[relatedTableName];
return dataTable;
}
catch (Exception ex)
{
logger.Info("Error while retrieving the data: " + ex.Message);
throw ex;
}
finally
{
disconnectSqL();
}
}
public String Update_SQL_Data(SqlDataAdapter dataAdapter, DataTable dtbTmp)
{
int row;
// Create SQL Command builder
SqlCommandBuilder cb = new SqlCommandBuilder(dataAdapter);
try
{
cb.GetUpdateCommand();
dataAdapter.DeleteCommand = cb.GetDeleteCommand(true);
dataAdapter.UpdateCommand = cb.GetUpdateCommand(true);
//dataAdapter.UpdateCommand.CommandTimeout = 200;
dataAdapter.InsertCommand = cb.GetInsertCommand(true);
row = dataAdapter.Update(dtbTmp);
dtbTmp.AcceptChanges();
}
catch (Exception ex)
{
// Bắt lỗi
return ex.Message;
}
return "";
}
// Sauvegarde tous les changements effectué dans le dataset
public void SaveDataSet(string tableName, DataSet dataSet)
{
if (dataSet.HasChanges() == false)
return;
switch (connType)
{
case ConnectionType.DATABASE_MSSQL:
{
try
{
var conn = new SqlConnection(connString);
var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn);
var builder = new SqlCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
DataSet changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
PrintDatasetErrors(changes);
dataSet.AcceptChanges();
}
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table " + tableName, ex);
}
break;
}
case ConnectionType.DATABASE_ODBC:
{
try
{
var conn = new OdbcConnection(connString);
var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn);
var builder = new OdbcCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table ", ex);
}
break;
}
case ConnectionType.DATABASE_MYSQL:
{
return;
}
case ConnectionType.DATABASE_OLEDB:
{
try
{
var conn = new OleDbConnection(connString);
var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn);
var builder = new OleDbCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table", ex);
}
break;
}
}
}
/// <summary>
/// 加载数据（返回DataTable）
/// </summary>
/// <param name="ConnStr">连接字符串</param>
/// <param name="strSQL">SQL语句</param>
/// <param name="strTableName">DataTable表名</param>
/// <param name="isTable">是否对应有物理表，需要有更新、保存等命令</param>
public virtual SD.DataTable GetData(string ConnStr, string strSQL, string strTableName, bool isTable)
{
SD.DataTable dt = new SD.DataTable(strTableName);
try
{
using (sqlconn = new SDC.SqlConnection(ConnStr))
{
//sqlconn.Open();
using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn))
{
//if (sqlcmd == null)
//{
// using (dt = new SD.DataTable(strTableName))
// {
// return dt;
// }
//}
sqlcmd.CommandTimeout = 7200;
using (sqladp = new SDC.SqlDataAdapter(sqlcmd))
{
if (isTable)
{
using (sqlcmdbd = new SDC.SqlCommandBuilder(sqladp))
{
sqlcmdbd.ConflictOption = SD.ConflictOption.CompareAllSearchableValues;
sqladp.InsertCommand = sqlcmdbd.GetInsertCommand();
sqladp.UpdateCommand = sqlcmdbd.GetUpdateCommand();
sqladp.DeleteCommand = sqlcmdbd.GetDeleteCommand();
sqladp.Fill(dt);
return(dt);
}
}
else
{
sqladp.Fill(dt);
return(dt);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlconn != null)
{
if (sqlconn.State != SD.ConnectionState.Closed)
{
sqlconn.Close();
}
sqlconn.Dispose();
}
}
}
static void Main(string[] args)
{
string theConnectionString = "Data Source=86BBAJI3MJ0T1JY;Initial Catalog=VideoGameStoreDB;Integrated Security=SSPI;";
SqlConnection theConnection = new SqlConnection(theConnectionString);
theConnection.Open();//打开数据库连接
if (theConnection.State == ConnectionState.Open)
Console.WriteLine("Database Connection is open!\n");
SqlCommand theCommend = new SqlCommand();
theCommend.Connection = theConnection;
theCommend.CommandText = "SELECT * FROM product";
try
{
theCommend.CommandType = CommandType.Text;
SqlDataAdapter theDataAdapter = new SqlDataAdapter(theCommend);
SqlCommandBuilder theCommendBuilder = new SqlCommandBuilder(theDataAdapter);
Console.WriteLine(theCommendBuilder.GetInsertCommand().CommandText + "\n\n");
Console.WriteLine(theCommendBuilder.GetUpdateCommand().CommandText + "\n\n");
Console.WriteLine(theCommendBuilder.GetDeleteCommand().CommandText + "\n\n");
}
catch (SqlException sqlexception)
{
Console.WriteLine(sqlexception);
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
}