public void Update(string strSelectSQL, DataTable objTable)
{
OleDbDataAdapter adapter1;
OleDbCommandBuilder builder1;
if (!this.m_bIsInTransaction)
{
OleDbCommand command1 = new OleDbCommand(strSelectSQL, this.m_objConn);
if (this.m_Timeout >= 30)
{
command1.CommandTimeout = this.m_Timeout;
}
adapter1 = new OleDbDataAdapter(command1);
builder1 = new OleDbCommandBuilder(adapter1);
adapter1.InsertCommand = builder1.GetInsertCommand();
adapter1.DeleteCommand = builder1.GetDeleteCommand();
adapter1.UpdateCommand = builder1.GetUpdateCommand();
adapter1.Update(objTable);
builder1.Dispose();
command1.Dispose();
adapter1.Dispose();
}
else
{
adapter1 = new OleDbDataAdapter(this.m_objCommand);
builder1 = new OleDbCommandBuilder(adapter1);
adapter1.InsertCommand = builder1.GetInsertCommand();
adapter1.DeleteCommand = builder1.GetDeleteCommand();
adapter1.UpdateCommand = builder1.GetUpdateCommand();
adapter1.Update(objTable);
builder1.Dispose();
adapter1.Dispose();
}
}
public Form1()
{
InitializeComponent();
adapter_grupa = new OleDbDataAdapter("select * from Grupa", @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\petar\Documents\SKOLA\Treca godina\TVP\Projekat_septembar\Prodavnica\Prodavnica.mdb");
adapter_artikal = new OleDbDataAdapter("select * from Artikal", @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\petar\Documents\SKOLA\Treca godina\TVP\Projekat_septembar\Prodavnica\Prodavnica.mdb");
adapter_racun = new OleDbDataAdapter("select * from Racun", @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\petar\Documents\SKOLA\Treca godina\TVP\Projekat_septembar\Prodavnica\Prodavnica.mdb");
OleDbCommandBuilder cmdBuild = new OleDbCommandBuilder(adapter_artikal);
adapter_artikal.InsertCommand = cmdBuild.GetInsertCommand();
adapter_artikal.UpdateCommand = cmdBuild.GetUpdateCommand();
adapter_artikal.DeleteCommand = cmdBuild.GetDeleteCommand();
cmdBuild = new OleDbCommandBuilder(adapter_racun);
adapter_racun.InsertCommand = cmdBuild.GetInsertCommand();
adapter_racun.UpdateCommand = cmdBuild.GetUpdateCommand();
adapter_racun.DeleteCommand = cmdBuild.GetDeleteCommand();
OleDbDataAdapter da = new OleDbDataAdapter("select * from Grupa ;select * from Artikal; select * from Racun", @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\petar\Documents\SKOLA\Treca godina\TVP\Projekat_septembar\Prodavnica\Prodavnica.mdb");
ds = new DataSet();
adapter_grupa.Fill(ds, "Grupa");
adapter_artikal.Fill(ds, "Artikal");
adapter_racun.Fill(ds, "Racun");
cbKategorija.DataSource = ds.Tables["Grupa"];
cbKategorija.DisplayMember = "Naziv";
cbKategorija.ValueMember = "id_grupa";
PopuniRacun();
}
//-----------------------------
//DataGridView에 데이터 채우기
//-----------------------------
private void LoadData()
{
try
{
string sql = "select * from addrbook";
LocalConn = Common_DB.DBConnection();
LocalConn.Open();
adapter = new OleDbDataAdapter(sql, LocalConn);
cb = new OleDbCommandBuilder(adapter);
adapter.DeleteCommand = cb.GetDeleteCommand();
adapter.InsertCommand = cb.GetInsertCommand();
adapter.UpdateCommand = cb.GetUpdateCommand();
ds = new DataSet();
adapter.Fill(ds, "ADDRBOOK");
dataGrid1.DataSource = ds.Tables["ADDRBOOK"];
}
catch (Exception e1)
{
MessageBox.Show("주소록 저장 오류~" + e1.ToString());
Log.WriteLine("FrmDataGridView", e1.ToString());
}
finally
{
LocalConn.Close();
}
}
// <Snippet1>
public static DataSet UpdateRows(string connectionString,
string queryString, string tableName)
{
DataSet dataSet = new DataSet();
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(queryString, connection);
OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
connection.Open();
adapter.Fill(dataSet, tableName);
//code to modify data in DataSet here
cb.GetDeleteCommand();
//Without the OleDbCommandBuilder this line would fail
adapter.Update(dataSet, tableName);
connection.Close();
}
return(dataSet);
}
// Disconnected
//פעולה המעדכנת את הדטהבייס בהתאם לדטהסט
public static void Update(DataSet ds, string com, string name)
{
OleDbConnection cn = new OleDbConnection(Connect.GetConnectionString());
OleDbCommand command = new OleDbCommand();
command.Connection = cn;
command.CommandText = com;
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
try
{
cn.Open();
adapter.Update(ds, name);
}
catch (Exception e)
{
throw e;
}
finally
{
cn.Close();
}
}
/*public static iDB2DataAdapter getiDB2DataAdapter(String DBType, String Sql, String sqlType)
* {
* iDB2DataAdapter da;
* iDB2Connection dbConn = new iDB2Connection();
* dbConn.ConnectionString = getConnStr(DBType);
* //dbConn.ConnectionString = "Provider=IBMDA400;Database=R21AFLBZ;Hostname=172.31.71.37;Uid=ITSDTS;Pwd=STD008;";
* if (dbConn.State != System.Data.ConnectionState.Open)
* dbConn.Open();
* da = new iDB2DataAdapter(Sql, dbConn);
* if (sqlType.ToUpper() == "PROCEDURE")
* {
* da.SelectCommand.CommandType = CommandType.StoredProcedure;
* }
* else
* {
* iDB2CommandBuilder builder = new iDB2CommandBuilder(da);
* try
* {
* da.DeleteCommand = builder.GetDeleteCommand();
* da.UpdateCommand = builder.GetUpdateCommand();
* da.InsertCommand = builder.GetInsertCommand();
* }
* catch (Exception e)
* {
* Console.WriteLine(e.ToString());
* }
* }
* return da;
* }
*/
public static OleDbDataAdapter getOleDbDataAdapter(DbConnection Conn, String Sql, String SqlType)
{
OleDbDataAdapter da;
OleDbConnection dbConn = (OleDbConnection)Conn;
//dbConn.ConnectionString = "Provider=IBMDA400;Database=R21AFLBZ;Hostname=172.31.71.37;Uid=ITSDTS;Pwd=STD008;";
if (dbConn.State != ConnectionState.Open)
{
dbConn.Open();
}
da = new OleDbDataAdapter(Sql, dbConn);
if (SqlType.ToUpper() == "PROCEDURE")
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;
}
else
{
try
{
OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
da.DeleteCommand = builder.GetDeleteCommand();
da.UpdateCommand = builder.GetUpdateCommand();
da.InsertCommand = builder.GetInsertCommand();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
return(da);
}
/// <summary>
/// Queries the database and populates a dataset.
/// </summary>
private void RetrieveDataFromTheDatabase()
{
try
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='AMDatabase.mdb'";
this.connection = new OleDbConnection();
this.connection.ConnectionString = connectionString;
this.connection.Open();
OleDbCommand selectCommand = new OleDbCommand();
selectCommand.CommandText = "SELECT * FROM VehicleStock";
selectCommand.Connection = this.connection;
this.adapter = new OleDbDataAdapter();
this.adapter.SelectCommand = selectCommand;
OleDbCommandBuilder builder = new OleDbCommandBuilder();
builder.DataAdapter = this.adapter;
this.adapter.UpdateCommand = builder.GetUpdateCommand();
this.adapter.DeleteCommand = builder.GetDeleteCommand();
this.adapter.InsertCommand = builder.GetInsertCommand();
this.dataset = new DataSet();
this.adapter.Fill(this.dataset, "VehicleStock");
}
catch (Exception e)
{
this.connection.Close();
this.connection.Dispose();
}
}
/// <summary>
/// Pre-Condition: The parameters will be in the order of passing the dataset,
/// and the string table name.
/// Post-Condition: The update dataset will be persisted in the database.
/// Description: This method will update the database based on the updated
/// records in the dataset for a given table.
/// </summary>
/// <param name="pDataSet">The dataset that contains the updated records.</param>
/// <param name="pStrTableName">The table name that will be updated in the
/// database.</param>
public void SaveData(DataSet pDataSet, string pStrTableName)
{
string strQuery = "SELECT * FROM " + pStrTableName;
OleDbDataAdapter dbDA = new OleDbDataAdapter(strQuery, _dbConn);
try
{
// setup Command Builders
OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA);
dbDA.InsertCommand = dbBLD.GetInsertCommand();
dbDA.UpdateCommand = dbBLD.GetUpdateCommand();
dbDA.DeleteCommand = dbBLD.GetDeleteCommand();
// subscribe to the OleDbRowUpdateEventHandler
dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
_dbConn.Open();
dbDA.Update(pDataSet, pStrTableName);
pDataSet.Tables[pStrTableName].AcceptChanges();
_dbConn.Close();
pStrTableName = null;
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
_dbConn.Close();
}
}
public void DeleteTeacher()
{
StudentList();
if (_tblStudents.Rows.Count != 0)
{
for (int r = 0; r < _tblStudents.Rows.Count; r++)
{
_tblStudents.Rows[r].Delete();
_daTblStudents.DeleteCommand = _cbS.GetDeleteCommand();
UpdateStudentsTbl();
}
}
_tblTeachers.Rows[0].Delete();
_daTblTeachers.DeleteCommand = _cbT.GetDeleteCommand();
UpdateTeachersTbl();
}
public static void DeleteClientHistory(string datetime, string tablename)
{
string selectCommandText = selectCommandText = "SELECT * FROM " + tablename + " WHERE room_datetime='" + datetime + "'";;
/*
* if (tablename.Equals("history_table_0"))
* selectCommandText = "SELECT * FROM history_table_0 WHERE room_datetime='" + datetime + "'";
* else if (tablename.Equals("history_table_1"))
* selectCommandText = "SELECT * FROM history_table_1 WHERE room_datetime='" + datetime + "'";
* else if (tablename.Equals("history_table_2"))
* selectCommandText = "SELECT * FROM history_table_2 WHERE room_datetime='" + datetime + "'";
* else
* return;
*/
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(selectCommandText, AccessFunction.dbConnection);
OleDbCommandBuilder oleDbCommandBuilder = new OleDbCommandBuilder(oleDbDataAdapter);
DataTable dataTable = new DataTable();
if (oleDbDataAdapter.Fill(dataTable) > 0)
{
dataTable.Rows[0].Delete();
oleDbDataAdapter.DeleteCommand = oleDbCommandBuilder.GetDeleteCommand();
oleDbDataAdapter.Update(dataTable);
}
}
private void button_showTable_Click(object sender, EventArgs e)
{
BindingSource bs1 = new BindingSource();
// создаем экземпляр класса OleDbConnection
myConnection = new OleDbConnection(connectString.ConnectionString);
// открываем соединение с БД
myConnection.Open();
var changeTable = comboBox1.SelectedItem.ToString();
string sqlCommand = String.Format("SELECT * FROM {0}", changeTable);
da = new OleDbDataAdapter(sqlCommand, myConnection);
dt = new DataTable();
//фиксирование изменений
OleDbCommandBuilder bulder = new OleDbCommandBuilder(da);
da.UpdateCommand = bulder.GetUpdateCommand();
da.InsertCommand = bulder.GetInsertCommand();
da.DeleteCommand = bulder.GetDeleteCommand();
da.Fill(dt);
bs1.DataSource = dt;
//dataGridView1.DataSource = dt; //выводим в грид
bindingNavigator1.BindingSource = bs1;
dataGridView1.DataSource = bs1;
myConnection.Close();
}
//void CopySqlTable(string name,SqlConnection sourceConn,SqlConnection destConn)
//{
// Message("拷貝" + name);
// DataTable table = new DataTable(name);
// string SelectStr = "Select * From " + name;
// var sourceAdapter = new SqlDataAdapter(SelectStr, sourceConn);
// try
// {
// int sourceNo = sourceAdapter.Fill(table);
// foreach (DataRow row in table.Rows) row.SetAdded();
// var destAdapter = new SqlDataAdapter(SelectStr, destConn);
// var builder = new SqlCommandBuilder(destAdapter);
// builder.QuotePrefix = "["; // 因為有些ColumnName是保留字,如Password
// builder.QuoteSuffix = "]";
// destAdapter.UpdateCommand = builder.GetUpdateCommand();
// destAdapter.InsertCommand = builder.GetInsertCommand();
// destAdapter.DeleteCommand = builder.GetDeleteCommand();
// int no = destAdapter.Update(table);
// MessageBox.Show(name + "來源共" + sourceNo.ToString() + "筆,寫出成功" + no.ToString() + "筆");
// }
// catch (Exception ex)
// {
// MessageBox.Show("寫出[" + name + "]時出錯:" + ex.Message);
// }
//}
void CopyOldDbTable(string name, OleDbConnection sourceConn, OleDbConnection destConn)
{
Message("拷貝" + name);
DataTable table = new DataTable(name);
string SelectStr = "Select * From " + name;
var sourceAdapter = new OleDbDataAdapter(SelectStr, sourceConn);
try
{
int sourceNo = sourceAdapter.Fill(table);
foreach (DataRow row in table.Rows)
{
row.SetAdded();
}
var destAdapter = new OleDbDataAdapter(SelectStr, destConn);
var builder = new OleDbCommandBuilder(destAdapter);
builder.QuotePrefix = "["; // 因為有些ColumnName是保留字,如Password
builder.QuoteSuffix = "]";
destAdapter.UpdateCommand = builder.GetUpdateCommand();
destAdapter.InsertCommand = builder.GetInsertCommand();
destAdapter.DeleteCommand = builder.GetDeleteCommand();
int no = destAdapter.Update(table);
MessageBox.Show(name + "來源共" + sourceNo.ToString() + "筆,寫出成功" + no.ToString() + "筆");
}
catch (Exception ex)
{
MessageBox.Show("寫出[" + name + "]時出錯:" + ex.Message);
}
}
/// <summary>
/// Initializes an instance of VehicelData form.
/// </summary>
public VehicelData()
{
InitializeComponent();
//Construct an instance of a connection object
OleDbConnection oleDbConnection = new OleDbConnection();
//Setup the ConnectionString
oleDbConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=AMDatabase.mdb";
//Open the database file
oleDbConnection.Open();
//Selection query
string SqlQuery = "SELECT * FROM VehicleStock";
//Construct an instance of a command object
//SqlQuery is the selection
//oleDbConnection reference to the connection
OleDbCommand dbCommand = new OleDbCommand(SqlQuery, oleDbConnection);
//Create an data adapter object
_dataAdapter = new OleDbDataAdapter();
//Set the data adapter object's select command to the command object
_dataAdapter.SelectCommand = dbCommand;
//Create an data set object
_dataSet = new DataSet();
//Using the data adapter object, fill the data set
//"VehicleStock" is the table name
_dataAdapter.Fill(_dataSet, "VehicleStock");
//Create the binding source object
_bindingSource = new BindingSource();
//Create the command builder object
OleDbCommandBuilder oleDbCommandBuilder = new OleDbCommandBuilder();
//Set the command builder object's data adapter to _dataAdapter
oleDbCommandBuilder.DataAdapter = _dataAdapter;
//Use command builder to get required SQL
_dataAdapter.UpdateCommand = oleDbCommandBuilder.GetUpdateCommand();
_dataAdapter.InsertCommand = oleDbCommandBuilder.GetInsertCommand();
_dataAdapter.DeleteCommand = oleDbCommandBuilder.GetDeleteCommand();
//Resolve the primary key conflict when Adding and Deleting new row
oleDbCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
//EventHandlers
_dataAdapter.RowUpdated += new OleDbRowUpdatedEventHandler(dataAdapter_RowUpdated);
dgvVehicelData.SelectionChanged += new EventHandler(dgvVehicelData_SelectionChanged);
dgvVehicelData.CellValueChanged += new DataGridViewCellEventHandler(dgvVehicelData_CellValueChanged);
this.FormClosing += FormClosingEvent;
mnuClose.Click += closeToolStripMenuItem_Click;
this.Load += new EventHandler(Form_load);
}
private int SaveData(string SQL, DataTable dt)
{
int result;
if (dt == null)
{
return(-1);
}
if (dt.DataSet != null && !dt.DataSet.HasChanges())
{
Helper.ShowMessage(true);
}
OleDbDataAdapter da = GetDataAdapter(SQL);
OleDbCommandBuilder cb = GetCommandBuilder(da);
OpenConnection();
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
result = da.Update(dt);
CloseConnection();
return(result);
}
//public bool UpdateDbData(string command, DataSet ds)
//{
// if (UpdateDbData(ds.Tables[0], "SystemOption") >= 0)
// return true;
// else
// return false;
//}
public bool UpdateDbData(string commandText, DataTable table)
{
int affect = -1;
try
{
OleDbConnection connection = new OleDbConnection(Url);
using (connection)
{
// Create the select command.
OleDbCommand command = new OleDbCommand(commandText, connection);
// Create the DbDataAdapter.
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
// Create the DbCommandBuilder.
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
builder.RefreshSchema();
// Get the insert, update and delete commands.
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
var dd = table.GetChanges();
affect = adapter.Update(table);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(affect > 0 ? true : false);
}
private void Main_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“mTP1DataSet.T_TEST_PRJ”中。您可以根据需要移动或删除它。
//this.t_TEST_PRJTableAdapter.Fill(this.mTP1DataSet.T_TEST_PRJ);
OleDbConnection conn = Connect.getConnection();
string sql = "select * from T_TEST_PRJ";
adapter = new OleDbDataAdapter(sql, conn);
builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetDeleteCommand();
ds = new DataSet();
adapter.Fill(ds, "T_TEST_PRJ");
this.dataGridView1.DataSource = ds.Tables["T_TEST_PRJ"];
conn.Close();
}
//--------------------------------------------------------
/// <summary>Creates a OleDbCommand object for the delete stored procedure</summary>
/// <returns>Initalized OleDbCommand object</returns>
public virtual OleDbCommand GetDeleteCmd()
{
_da.SelectCommand = GetSelectCmd(0); //GetListCmd();
OleDbCommandBuilder builder = new OleDbCommandBuilder(_da);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
return(builder.GetDeleteCommand());
}
private void buttonDBSync_Click(object sender, EventArgs e)
{
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapterArtikel);
adapterArtikel.DeleteCommand = builder.GetDeleteCommand();
adapterArtikel.InsertCommand = builder.GetInsertCommand();
adapterArtikel.UpdateCommand = builder.GetUpdateCommand();
adapterArtikel.Update(ds.Tables["Artikel"]);
}
/// 获得第数据
public static OleDbDataAdapter GetDBDataAdapter(string sql)
{
OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection);
OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
da.InsertCommand = builder.GetInsertCommand();
da.UpdateCommand = builder.GetUpdateCommand();
da.DeleteCommand = builder.GetDeleteCommand();
return(da);
}
private void BuildCommand(OleDbDataAdapter adapter)
{
OleDbCommandBuilder Builder = new OleDbCommandBuilder(adapter);
Builder.QuotePrefix = "[";
Builder.QuoteSuffix = "]";
adapter.UpdateCommand = Builder.GetUpdateCommand();
adapter.InsertCommand = Builder.GetInsertCommand();
adapter.DeleteCommand = Builder.GetDeleteCommand();
}
//定义一个函数，用数据集来更新物理数据库
public void UpdateDataset(string sql, DataSet ds)
{
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
OleDbCommandBuilder CmdBuilder = new OleDbCommandBuilder(da);//用来自动产生sql语句
da.UpdateCommand = CmdBuilder.GetUpdateCommand();
da.InsertCommand = CmdBuilder.GetInsertCommand();
da.DeleteCommand = CmdBuilder.GetDeleteCommand();
da.Update(ds);
}
public static void Update(string tableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from " + tableName, con);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.Update(ds, tableName);
}
private void button3_Click(object sender, EventArgs e)
{
if (MessageBox.Show("هل تريد الحذف", "تحذير", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
bs.RemoveCurrent();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(ds, "mor");
}
}
////////////////////////////////////////////////////
public override IDbCommand GetDeleteCommand(DataTable table, IDbDataAdapter adapter)
{
OleDbCommandBuilder builder = new OleDbCommandBuilder((OleDbDataAdapter)adapter);
OleDbCommand command = builder.GetDeleteCommand();
if (m_connexion.IsInTrans())
{
command.Transaction = (OleDbTransaction)m_connexion.Transaction;
}
return(command);
}
/// <summary>
/// 批量操作表
/// </summary>
/// <param name="cmdType"></param>
/// <param name="dt"></param>
/// <param name="strTblName">数据库表名</param>
/// <param name="where">除主键外的条件</param>
/// <returns></returns>
private int UpdateDbData(DataTable dt, string strTblName, string where = null)
{
int affect = -1;
StringBuilder cmdFields = new StringBuilder();
foreach (DataColumn column in dt.Columns)
{
cmdFields.Append(column.ColumnName + ",");
}
if (cmdFields.Length > 0)
{
cmdFields = cmdFields.Remove(cmdFields.Length - 1, 1);
}
using (OleDbConnection oldCon = new OleDbConnection(Url))
{
OleDbCommand myCommand = new OleDbCommand(string.Format("select {0} from {1}", cmdFields, strTblName), oldCon);
try
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter();
myAdapter.SelectCommand = myCommand;
OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder
{
DataAdapter = myAdapter,
SetAllValues = true,
ConflictOption = ConflictOption.OverwriteChanges,
};
myCommandBuilder.RefreshSchema();
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand(true);
myAdapter.InsertCommand.Connection = oldCon;
myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(true);
myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand(true);
if (!string.IsNullOrEmpty(where))
{
myAdapter.UpdateCommand.CommandText = myAdapter.UpdateCommand.CommandText.Replace("WHERE", "WHERE " + where + " and ");
}
var dd = dt.GetChanges();
affect = myAdapter.Update(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
return(affect);
}
/// <summary>
/// 更新Adapter命令
/// </summary>
public void UpdateAdapterCommand()
{
string txtCommand = "SELECT * FROM " + "[" + TBName + "]";
DBAdapter = new OleDbDataAdapter(txtCommand, DBConnection);
DBBuilder = new OleDbCommandBuilder(DBAdapter);
DBBuilder.QuotePrefix = "`";
DBBuilder.QuoteSuffix = "`";
DBAdapter.UpdateCommand = DBBuilder.GetUpdateCommand();
DBAdapter.DeleteCommand = DBBuilder.GetDeleteCommand();
DBAdapter.InsertCommand = DBBuilder.GetInsertCommand();
}
public void Update(string tableName)//הפעולה מקבלת שם טבלה ומעדכנת את מסד הנתונים
{
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from " + tableName, con);
// הכנת פעולות העדכון
OleDbCommandBuilder Builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = Builder.GetInsertCommand(); // שאילתת הכנסה
adapter.UpdateCommand = Builder.GetUpdateCommand(); // שאילתת עדכון
adapter.DeleteCommand = Builder.GetDeleteCommand(); // שאילתת מחיקה
// עדכון מסד הנתונים
adapter.Update(ds, tableName);
}
public OleDbDataAdapter GetOleAdapter(string SelectCommand)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(SelectCommand, this.connection);
OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = cb.GetInsertCommand();
adapter.UpdateCommand = cb.GetUpdateCommand();
adapter.DeleteCommand = cb.GetDeleteCommand();
return(adapter);
}
public static void DeleteClientProtocol(string clientname)
{
string selectCommandText = "SELECT * FROM protocol WHERE client_type='" + clientname + "'";
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(selectCommandText, AccessFunction.dbConnection);
OleDbCommandBuilder oleDbCommandBuilder = new OleDbCommandBuilder(oleDbDataAdapter);
DataTable dataTable = new DataTable();
oleDbDataAdapter.Fill(dataTable);
dataTable.Rows[0].Delete();
oleDbDataAdapter.DeleteCommand = oleDbCommandBuilder.GetDeleteCommand();
oleDbDataAdapter.Update(dataTable);
}
// Функция добавления объектов в таблицу и сохранения в базу данных
public static void AddToTableAndSave(DataSet ds, OleDbDataAdapter adapter, string tableName)
{
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
using (builder)
{
adapter.Update(ds, tableName);
}
}
