/// <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;
}
}
/// <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;
}
protected void gridStudent_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
SqlConnection connection = SqlHelper.GetConnection();
var dataAdapter = new SqlDataAdapter(selectCommandText, connection);
var builder = new SqlCommandBuilder(dataAdapter);
dataAdapter.DeleteCommand = builder.GetDeleteCommand();
DataSet dataSet = SqlHelper.GetDataSetBySqlCommand(selectCommandText, connection);
string studentID = (gridStudent.Rows[e.RowIndex].Cells[1].Controls[0] as TextBox).Text;
string name = (gridStudent.Rows[e.RowIndex].Cells[2].Controls[0] as TextBox).Text;
string gender = (gridStudent.Rows[e.RowIndex].Cells[3].Controls[0] as TextBox).Text;
string dayOfBirth = (gridStudent.Rows[e.RowIndex].Cells[4].Controls[0] as TextBox).Text;
string address = (gridStudent.Rows[e.RowIndex].Cells[5].Controls[0] as TextBox).Text;
string department = (gridStudent.Rows[e.RowIndex].Cells[6].Controls[0] as TextBox).Text;
//获取 DataSet 中与点击了 “编辑” 按钮的这行对应的数据行
DataRow row = dataSet.Tables[0].Rows[e.RowIndex];
//修改其中各字段的值
row["StudentID"] = studentID;
row["Name"] = name;
row["Gender"] = gender;
row["DayOfBirth"] = dayOfBirth;
row["Address"] = address;
row["Department"] = department;
//提交到数据库中
dataAdapter.Update(dataSet);
gridStudent.EditIndex = -1;
ShowData();
}
/// <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 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);
}
}
//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 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();
}
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;
}
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);
}
/// <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) ");
}
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
}
}
private void buttonX1_Click(object sender, EventArgs e) // 删除所选
{
ArrayList lsIdToDel = new ArrayList();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Selected)
{
object v = row.Cells["ItemID"].Value;
if (v == null) continue;
int itemID = Convert.ToInt32(v);
lsIdToDel.Add(itemID);
}
}
foreach (int i in lsIdToDel)
{
DataRow RowToDel = m_tbl.Rows.Find(i);
if (RowToDel != null)
RowToDel.Delete();
}
int nAffectedRowCount = 0;
try
{
string sql = "SELECT * FROM tbl_icon_item";
SqlDataAdapter adp = new SqlDataAdapter(sql, Conn);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
adp.DeleteCommand = cmdBuilder.GetDeleteCommand();
nAffectedRowCount = adp.Update(m_tbl);
m_tbl.AcceptChanges();
MessageBox.Show("数据删除成功，删除行数: " + nAffectedRowCount.ToString() + " 。",
"成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException ex)
{
MessageBox.Show("数据更新时产生异常: " + ex.ToString() + "\r\n\r\n新增数据将不被保存",
"删除数据", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
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);
}
}
}
}
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;
}
[Test] // GetDeleteCommand ()
public void GetDeleteCommand1_CheckParameters ()
{
SqlCommand cmd = null;
try {
SqlDataAdapter adapter = new SqlDataAdapter (
"select id, type_varchar from string_family",
conn);
SqlCommandBuilder cb = new SqlCommandBuilder (adapter);
cmd = cb.GetDeleteCommand ();
Assert.AreEqual (3, cmd.Parameters.Count, "#1");
Assert.AreEqual (SqlDbType.Int, cmd.Parameters ["@p2"].SqlDbType, "#2");
Assert.AreEqual (1, cmd.Parameters ["@p2"].Value, "#3");
} finally {
if (cmd != null)
cmd.Dispose ();
}
}
public void Refresh()
{
Filter2Sql();
Data = new DataTable();
SqlCommand columnsSelect = new SqlCommand(this._combinedSql, ExportConfiguration.SqlConnection);
DataAdapter = new SqlDataAdapter(columnsSelect);
SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(DataAdapter);
DataAdapter.UpdateCommand = sqlBuilder.GetUpdateCommand();
DataAdapter.InsertCommand = sqlBuilder.GetInsertCommand();
DataAdapter.DeleteCommand = sqlBuilder.GetDeleteCommand();
DataAdapter.Fill(Data);
OnPropertyChanged("Data");
}
public void Test_DataAdapter()
{
#region "是否测试 SqlDataAdapter.SelectCommand, SqlDataAdapter.UpdateCommand, SqlDataAdapter.InsertCommand, SqlDataAdapter.DeleteCommand 属性?"
if (this.tracer.WaitInputAChar("是否测试 SqlDataAdapter.SelectCommand, SqlDataAdapter.UpdateCommand, SqlDataAdapter.InsertCommand, SqlDataAdapter.DeleteCommand 属性?") == 'y')
{
try
{
using (DataSet dataSet = new DataSet())
{
this.tracer.WriteLine("说明: 没有 SelectCommand 的 DataAdapter 使用 Fill 方法将导致异常");
SqlDataAdapter adapter = new SqlDataAdapter();
try
{ adapter.Fill(dataSet); }
catch (Exception err)
{ this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); }
}
using (DataSet dataSet = new DataSet())
{
this.tracer.WriteLine("说明: 使用多个 select 语句返回多张表");
SqlConnection connection = new SqlConnection(this.connectionString);
this.tracer.WriteLine("创建拥有两个 select 语句的 SqlCommand");
SqlCommand selectCommand = new SqlCommand("select name, age, classname from student;select name from class", connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectCommand;
adapter.Fill(dataSet);
int index = 1;
foreach (DataTable table in dataSet.Tables)
{
this.tracer.WriteLine(string.Format("表: {0}", table.TableName));
foreach (DataRow row in table.Rows)
{
this.tracer.Write(string.Format("行 [{0}]:", index++));
foreach (DataColumn column in table.Columns)
this.tracer.Write(string.Format("{0} = <{1}>;", column.ColumnName, row[column]));
this.tracer.WriteLine();
}
}
}
using (DataSet dataSet = new DataSet())
{
this.tracer.WriteLine("说明: 更新 DataSet 时, 如果存在 RowState 为 Added, Modified, Deleted 的 DataRow, 则 SqlDataAdapter 需要对应的 InsertCommand, UpdateCommand, DeleteCommand");
SqlConnection connection = new SqlConnection(this.connectionString);
connection.Open();
SqlCommand selectCommand = new SqlCommand("select name, age, classname from student", connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectCommand;
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.Fill(dataSet);
this.tracer.WriteLine("对 student 修改, 删除, 新建");
dataSet.Tables[0].Rows[0]["name"] = "haha";
dataSet.Tables[0].Rows[1].Delete();
dataSet.Tables[0].Rows.Add("我的学生", 10, "二年级一班");
using (SqlTransaction transaction = connection.BeginTransaction())
{
adapter.UpdateCommand.Transaction = transaction;
adapter.InsertCommand.Transaction = transaction;
adapter.DeleteCommand.Transaction = transaction;
this.tracer.WriteLine("更新数据库");
adapter.Update(dataSet);
this.tracer.WriteLine("回滚");
transaction.Rollback();
}
connection.Close();
}
}
catch (Exception err)
{ this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); }
this.tracer.WaitPressEnter();
}
#endregion
}
//this builds commands to update, insert, or delete from the database.
private void cbuilder(string table, DataSet cb_dset)
{
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
dataAdapter.UpdateCommand = builder.GetUpdateCommand();
//MessageBox.Show(dataAdapter.UpdateCommand.CommandText);
dataAdapter.InsertCommand = builder.GetInsertCommand();
//MessageBox.Show(dataAdapter.InsertCommand.CommandText);
dataAdapter.DeleteCommand = builder.GetDeleteCommand();
//MessageBox.Show(dataAdapter.DeleteCommand.CommandText);
try
{
dataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(dataAdapter_RowUpdated);
int rowsupdated = dataAdapter.Update(cb_dset, table);
dataAdapter.RowUpdated -= new SqlRowUpdatedEventHandler(dataAdapter_RowUpdated);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Update_DeleteRow ()
{
conn = new SqlConnection (ConnectionManager.Singleton.ConnectionString);
conn.Open ();
DataTable dt = new DataTable ();
adapter = new SqlDataAdapter ("SELECT * FROM employee", conn);
SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
adapter.DeleteCommand = builder.GetDeleteCommand ();
adapter.Fill (dt);
DateTime now = DateTime.Now;
DateTime doj = new DateTime (now.Year, now.Month, now.Day, now.Hour,
now.Minute, now.Second);
DateTime dob = new DateTime (now.Year, now.Month, now.Day, now.Hour,
now.Minute, now.Second);
dob.Subtract (new TimeSpan (20 * 365, 0, 0, 0));
try {
DataRow newRow = dt.NewRow ();
newRow ["id"] = 6002;
newRow ["fname"] = "boston";
newRow ["dob"] = dob;
newRow ["doj"] = doj;
newRow ["email"] = "*****@*****.**";
dt.Rows.Add (newRow);
adapter.Update (dt);
foreach (DataRow row in dt.Rows)
if (((int) row ["id"]) == 6002)
row.Delete ();
adapter.Update (dt);
SqlCommand cmd = conn.CreateCommand ();
cmd.CommandText = "SELECT id, fname, lname, dob, doj, email FROM employee WHERE id = 6002";
dr = cmd.ExecuteReader ();
Assert.IsFalse (dr.Read ());
dr.Close ();
} finally {
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
//request = Select * de la table qu'on veut modifier
//authors = ce que l'ont veut inserer ou modifer
private int UpdateByCommandBuilder(string request, DataTable authors)
{
int result;
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand sqlCommand = new SqlCommand(request, sqlConnection);
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;
result = sqlDataAdapter.Update(authors);
}
return result;
}
/// <summary>
/// update table by DataSet
/// </summary>
/// <param name="dataSet">DataSet</param>
/// <param name="tableName">tableName</param>
/// <param name="deleteCommandText">deleteCommandText</param>
/// <param name="updateCommandText">updateCommandText</param>
/// <param name="needUpdateColName">needUpdateColName,use for updateCommand and deleteCommandText</param>
/// <param name="prikeyColName">prikeyColName,use for updateCommand and deleteCommandText</param>
public int UpdateDataset(DataSet dataSet, string tableName, string deleteCommandText, string updateCommandText,
List<string> needUpdateColName, List<string> prikeyColName)
{
int result = 0;
string sSql = "Select * from " + tableName;
if (!this.IsInTransaction && !this.keepConnectionOpen && this.sqlConn == null)
{
this.sqlConn = new SqlConnection(this.DBConnString);
}
if (this.sqlConn.State == ConnectionState.Closed)
this.sqlConn.Open();
try
{
// Create a SqlDataAdapter, and dispose of it after we are done
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sSql, this.sqlConn))
{
dataAdapter.UpdateBatchSize = 10;
if (this.IsInTransaction)
{
dataAdapter.SelectCommand.Transaction = this.sqltrans;
}
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
commandBuilder.QuotePrefix = "[";
commandBuilder.QuoteSuffix = "]";
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
if (string.IsNullOrEmpty(deleteCommandText))
{
dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
}
else
{
dataAdapter.DeleteCommand = new SqlCommand(deleteCommandText, this.sqlConn);
int priKeyCount = 0;
foreach (SqlParameter mParam in commandBuilder.GetDeleteCommand().Parameters)
{
if (priKeyCount < prikeyColName.Count && prikeyColName.Contains(mParam.SourceColumn))
{
dataAdapter.DeleteCommand.Parameters.Add(string.Format("@{0}", mParam.SourceColumn),
mParam.SqlDbType, mParam.Size, mParam.SourceColumn);
priKeyCount++;
}
if (priKeyCount == prikeyColName.Count)
{
break;
}
}
dataAdapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
}
if (string.IsNullOrEmpty(updateCommandText))
{
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
}
else
{
dataAdapter.UpdateCommand = new SqlCommand(updateCommandText, this.sqlConn);
int priKeyCount = 0;
int updateColCount = 0;
foreach (SqlParameter mParam in commandBuilder.GetUpdateCommand().Parameters)
{
if (priKeyCount < prikeyColName.Count && prikeyColName.Contains(mParam.SourceColumn.ToUpper()))
{
dataAdapter.UpdateCommand.Parameters.Add(string.Format("@{0}", mParam.SourceColumn),
mParam.SqlDbType, mParam.Size, mParam.SourceColumn);
priKeyCount++;
}
if (updateColCount < needUpdateColName.Count && needUpdateColName.Contains(mParam.SourceColumn.ToUpper()))
{
dataAdapter.UpdateCommand.Parameters.Add(string.Format("@{0}", mParam.SourceColumn),
mParam.SqlDbType, mParam.Size, mParam.SourceColumn);
updateColCount++;
}
if (priKeyCount == prikeyColName.Count && updateColCount == needUpdateColName.Count)
{
break;
}
}
dataAdapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
}
if (this.IsInTransaction)
{
dataAdapter.InsertCommand.Transaction = this.sqltrans;
dataAdapter.UpdateCommand.Transaction = this.sqltrans;
dataAdapter.DeleteCommand.Transaction = this.sqltrans;
}
result = dataAdapter.Update(dataSet, tableName);
dataSet.AcceptChanges();
return result;
}
}
catch (Exception e)
{
if (this.needLog)
{
StringBuilder sbparam = new StringBuilder();
sbparam.Append(tableName + ";\n");
Log.LogErr(sbparam.ToString(), e);
}
throw;
}
finally
{
if (!this.IsInTransaction && !this.KeepConnectionOpen)
{
this.sqlConn.Close();
}
}
}
/// <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();
}
}
}
[Test] // GetDeleteCommand ()
public void GetDeleteCommand1_AutoIncrement ()
{
if (ClientVersion == 7)
Assert.Ignore ("Key information is not available for temporary tables.");
SqlCommand cmd = null;
try {
cmd = conn.CreateCommand ();
cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
cmd.ExecuteNonQuery ();
string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds);
Assert.AreEqual (1, ds.Tables.Count);
Assert.AreEqual (4, ds.Tables [0].Columns.Count);
SqlCommandBuilder cb = new SqlCommandBuilder (da);
cmd = cb.GetDeleteCommand ();
Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
"(([id] = @p1) AND ([counter] = @p2) AND " +
"((@p3 = 1 AND [value] IS NULL) OR ([value] = @p4)))",
cmd.CommandText, "#1");
Assert.AreEqual (4, cmd.Parameters.Count, "#2");
} finally {
if (cmd != null)
cmd.Dispose ();
}
}
/// <summary>
/// 利用 CommandBuilder，根据 DataAdapter 对象的 SelectCommand 属性，自动生成其余的三个 SqlCommand 对象
/// </summary>
private void InitializeDataAdapter()
{
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
dataAdapter.DeleteCommand = builder.GetDeleteCommand();
dataAdapter.UpdateCommand = builder.GetUpdateCommand();
dataAdapter.InsertCommand = builder.GetInsertCommand();
}
public void QuoteSuffix_DeleteCommand_Generated ()
{
SqlCommand cmd = null;
try {
string selectQuery = "select id, lname from employee where id = 3";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
SqlCommandBuilder cb = new SqlCommandBuilder (da);
cmd = cb.GetDeleteCommand ();
Assert.AreEqual ("]", cb.QuoteSuffix, "#1");
try {
cb.QuoteSuffix = "\"";
Assert.Fail ("#2");
} catch (InvalidOperationException ex) {
// The QuotePrefix and QuoteSuffix properties
// cannot be changed once an Insert, Update, or
// Delete command has been generated
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#3");
Assert.IsNull (ex.InnerException, "#4");
Assert.IsNotNull (ex.Message, "#5");
}
Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
cb.RefreshSchema ();
cb.QuoteSuffix = "\"";
} finally {
if (cmd != null)
cmd.Dispose ();
}
}
[Test] // GetDeleteCommand (Boolean)
public void GetDeleteCommand2_AutoIncrement ()
{
SqlCommand cmd = null;
try {
cmd = conn.CreateCommand ();
cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
cmd.ExecuteNonQuery ();
string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds);
Assert.AreEqual (1, ds.Tables.Count);
Assert.AreEqual (4, ds.Tables [0].Columns.Count);
SqlCommandBuilder cb;
cb = new SqlCommandBuilder (da);
cmd = cb.GetDeleteCommand (true);
Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
"(([id] = @Original_id) AND ([counter] = @Original_counter) " +
"AND ((@IsNull_value = 1 AND [value] IS NULL) " +
"OR ([value] = @Original_value)))",
cmd.CommandText, "#A1");
Assert.AreEqual (4, cmd.Parameters.Count, "#A2");
cmd = cb.GetDeleteCommand (false);
Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
"(([id] = @Original_id) AND ([counter] = @Original_counter) " +
"AND ((@IsNull_value = 1 AND [value] IS NULL) " +
"OR ([value] = @Original_value)))",
cmd.CommandText, "#B1");
Assert.AreEqual (4, cmd.Parameters.Count, "#B2");
cb = new SqlCommandBuilder (da);
cmd = cb.GetDeleteCommand (false);
Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
"(([id] = @p1) AND ([counter] = @p2) " +
"AND ((@p3 = 1 AND [value] IS NULL) " +
"OR ([value] = @p4)))",
cmd.CommandText, "#C1");
Assert.AreEqual (4, cmd.Parameters.Count, "#C2");
cmd = cb.GetDeleteCommand (true);
Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE " +
"(([id] = @Original_id) AND ([counter] = @Original_counter) " +
"AND ((@IsNull_value = 1 AND [value] IS NULL) " +
"OR ([value] = @Original_value)))",
cmd.CommandText, "#D1");
Assert.AreEqual (4, cmd.Parameters.Count, "#D2");
} finally {
if (cmd != null)
cmd.Dispose ();
}
}
// 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;
}
}
}
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 "";
}
[Test] // GetDeleteCommand ()
public void GetDeleteCommand2 ()
{
SqlCommand cmd = null;
try {
string selectQuery = "select id, fname, lname, id+2 as next_id from employee where id = 3";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
Assert.AreEqual (1, ds.Tables.Count);
SqlCommandBuilder cb;
cb = new SqlCommandBuilder (da);
cmd = cb.GetDeleteCommand (true);
Assert.AreEqual ("DELETE FROM [employee] WHERE " +
"(([id] = @Original_id) AND ([fname] = " +
"@Original_fname) AND ((@IsNull_lname = 1 " +
"AND [lname] IS NULL) OR ([lname] = " +
"@Original_lname)))", cmd.CommandText, "#A1");
Assert.AreSame (conn, cmd.Connection, "#A2");
AssertDeleteParameters (cmd, true, "#A3:");
Assert.AreSame (cmd, cb.GetDeleteCommand (true), "#A4");
cmd = cb.GetDeleteCommand (false);
Assert.AreEqual ("DELETE FROM [employee] WHERE " +
"(([id] = @Original_id) AND ([fname] = " +
"@Original_fname) AND ((@IsNull_lname = 1 " +
"AND [lname] IS NULL) OR ([lname] = " +
"@Original_lname)))", cmd.CommandText, "#B1");
Assert.AreSame (conn, cmd.Connection, "#B2");
AssertDeleteParameters (cmd, true, "#B3:");
Assert.AreSame (cmd, cb.GetDeleteCommand (false), "#B4");
cb = new SqlCommandBuilder (da);
cmd = cb.GetDeleteCommand (false);
Assert.AreEqual ("DELETE FROM [employee] WHERE " +
"(([id] = @p1) AND ([fname] = @p2) AND " +
"((@p3 = 1 AND [lname] IS NULL) OR " +
"([lname] = @p4)))", cmd.CommandText, "#C1");
Assert.AreSame (conn, cmd.Connection, "#C2");
AssertDeleteParameters (cmd, false, "#C3:");
Assert.AreSame (cmd, cb.GetDeleteCommand (false), "#C4");
cmd = cb.GetDeleteCommand (true);
Assert.AreEqual ("DELETE FROM [employee] WHERE " +
"(([id] = @Original_id) AND ([fname] = " +
"@Original_fname) AND ((@IsNull_lname = 1 " +
"AND [lname] IS NULL) OR ([lname] = " +
"@Original_lname)))", cmd.CommandText, "#D1");
Assert.AreSame (conn, cmd.Connection, "#D2");
AssertDeleteParameters (cmd, true, "#D3:");
Assert.AreSame (cmd, cb.GetDeleteCommand (false), "#D4");
} finally {
if (cmd != null)
cmd.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);
}
}