/// פעולה המוסיפה טבלה ל- Dataset
/// ומכינה את האדפטר לביצוע כל פעולות העידכון
/// "tableName">שם הטבלה<
///"sqlStat">שאילתת שליפה<
public bool AddTable(string tableName, string sqlStat)
{
if (!ds.Tables.Contains(tableName))
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlStat, con);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.Fill(ds, tableName);
adapters.Add(tableName, adapter);
return(true);
}
return(false);
}
private void 查看数据集ToolStripMenuItem_Click(object sender, EventArgs e)
{
table.Clear();
string sql = "Select * from student";
adapter = new OleDbDataAdapter(sql, str);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.Fill(table);
table.DefaultView.Sort = "学号 asc";
dataGridView1.DataSource = table;
}
// update by the dataset from the Form
public void Update(string dbName, string tabName)
{
OleDbConnection con = getCon(dbName);
string sql = "select * From " + tabName;
sda = new OleDbDataAdapter(sql, con);
OleDbCommandBuilder builder = new OleDbCommandBuilder(sda);
sda.InsertCommand = builder.GetInsertCommand();
sda.DeleteCommand = builder.GetDeleteCommand();
sda.UpdateCommand = builder.GetUpdateCommand();
this.ds = new DataSet();
sda.Fill(this.ds, tabName);
}
private void LoadData()
{
oleDbDataAdapter = new OleDbDataAdapter("select * from students", oleDbConnection);
dataSet = new DataSet();
oleDbDataAdapter.Fill(dataSet, "Students");
oleDbCommandBuilder = new OleDbCommandBuilder(oleDbDataAdapter);//把adapter的信息放入commandBuilder
oleDbDataAdapter.DeleteCommand = oleDbCommandBuilder.GetDeleteCommand();
oleDbDataAdapter.UpdateCommand = oleDbCommandBuilder.GetUpdateCommand();
oleDbDataAdapter.InsertCommand = oleDbCommandBuilder.GetInsertCommand();
//toolStripStatusLabel1.Text = oleDbDataAdapter.SelectCommand;
//DataSet本身没有从数据库加载数据的能力，必须通过OleDbDataAdapter提供的Fill方法进行加载。
//在Fill过程中，若dataSet中没有students，则会自动建立，并把“select * from students”结果载入。
oleDbDataAdapter.SelectCommand = new OleDbCommand("select * from classes", oleDbConnection);
oleDbDataAdapter.Fill(dataSet, "Classes");
}
public void add(Form2 f)
{
String name = f.textBox1.Text.ToString();
String ID = f.textBox2.Text.ToString();
String sex = f.textBox3.Text.ToString();
String Class = f.textBox4.Text.ToString();
String Email = f.textBox5.Text.ToString();
String chinese = f.textBox6.Text.ToString();
String math = f.textBox7.Text.ToString();
String english = f.textBox8.Text.ToString();
OleDbConnection conn = new OleDbConnection();
String str = System.Environment.CurrentDirectory;
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
connStr += @str + @"//StudentManager.mdb";
// Console.WriteLine("当前连接字符串为:\n" + connStr + "\n");
conn.ConnectionString = connStr;
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
string strSelectQuery = "select * from students";
OleDbCommand cmd = new OleDbCommand(strSelectQuery, conn);
da.SelectCommand = cmd;
da.InsertCommand = cb.GetInsertCommand();
DataSet result = new DataSet();
da.Fill(result, "students");
DataRow row1 = result.Tables["students"].NewRow();
row1["Name"] = name;
row1["ID"] = ID;
row1["Sex"] = sex;
row1["Class"] = Class;
row1["Email"] = Email;
row1["Chinese"] = chinese;
row1["Math"] = math;
row1["English"] = english;
try {
result.Tables["students"].Rows.Add(row1);
da.Update(result, "students");
}
catch (System.Data.OleDb.OleDbException e) {
MessageBox.Show(e.Message);
}
conn.Close();
}
public void SaveToAccess(string tableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from " + tableName, con);
//עצם שעוזר בבנית משפטי הוספה עדכון ומחיקה
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
try {
adapter.Update(ds, tableName);
}
catch
{
MessageBox.Show("try again later");
}
}
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>
/// Execute all rows in DataTable into Excel File
/// </summary>
/// <param name="path"></param>
/// <param name="tableName"></param>
/// <param name="tbl"></param>
/// <returns></returns>
public static Int32 Execute(string path, string tableName, System.Data.DataTable tbl)
{
int rs = 0;
if (tbl == null)
{
Memory.Instance.Error = new Exception("DataTable can't null");
return(-1);
}
try
{
OleDbDataAdapter da = null;
OleDbConnection connect = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");
try
{
connect.Open();
da = new OleDbDataAdapter(string.Format("select * from [{0}$]", tableName), connect);
OleDbCommandBuilder cmdBd = new OleDbCommandBuilder(da);
cmdBd.ConflictOption = ConflictOption.OverwriteChanges;
cmdBd.QuotePrefix = "[";
cmdBd.QuoteSuffix = "]";
da.DeleteCommand = cmdBd.GetDeleteCommand();
da.InsertCommand = cmdBd.GetInsertCommand();
da.UpdateCommand = cmdBd.GetUpdateCommand();
rs = da.Update(tbl);
}
catch (Exception ex)
{
Memory.Instance.Error = ex;
rs = -1;
}
finally
{
connect.Close();
}
}
catch (Exception e)
{
//Map server exception to meaningful client error message
Memory.Instance.Error = e;
rs = -2;
}
return(rs);
}
public Profile()
{
InitializeComponent();
OleDbConnection conn = Connect.getConnection();
string sql = "select * from T_USER";
adapter = new OleDbDataAdapter(sql, conn);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
dataSet = new DataSet();
adapter.Fill(dataSet, "T_USER");
this.dataGridView1.DataSource = dataSet.Tables["T_USER"];
}
internal int UpdateDataTable(string tablename, DataTable datatable)
{
using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + tablename + "]", conn))
{
using (OleDbDataAdapter oledbDataAdapter = new OleDbDataAdapter(cmd))
{
using (OleDbCommandBuilder oledbCommandBuilder = new OleDbCommandBuilder(oledbDataAdapter))
{
oledbCommandBuilder.QuotePrefix = " [";
oledbCommandBuilder.QuoteSuffix = "] ";
oledbDataAdapter.DeleteCommand = oledbCommandBuilder.GetDeleteCommand(true);
oledbDataAdapter.UpdateCommand = oledbCommandBuilder.GetUpdateCommand(true);
oledbDataAdapter.InsertCommand = oledbCommandBuilder.GetInsertCommand(true);
return(oledbDataAdapter.Update(datatable));
}
}
}
}
public static void ConnectToDB()
{
dsProject = new DataSet();
con = new OleDbConnection(dbpath);
adapters = new OleDbDataAdapter[tableNames.Length];
for (int i = 0; i < adapters.Length; i++)
{
adapters[i] = new OleDbDataAdapter("select * from " + tableNames[i], con);
adapters[i].Fill(dsProject, tableNames[i]);
// בנית פקודות לעדכון
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapters[i]);
adapters[i].InsertCommand = builder.GetInsertCommand();
adapters[i].UpdateCommand = builder.GetUpdateCommand();
adapters[i].DeleteCommand = builder.GetDeleteCommand();
}
}
/// <summary>
/// Updates the specified table(s) in the data set
/// </summary>
/// <param name="tableName">The name of the table that is to be updated</param>
public void UpdateData(string tableName)
{
OleDbDataAdapter tempAdapter = new OleDbDataAdapter();
OleDbCommandBuilder tempBuilder = new OleDbCommandBuilder(tempAdapter);
tempAdapter.AcceptChangesDuringFill = false;
tempAdapter.SelectCommand = new OleDbCommand("SELECT * FROM " + tableName, m_connection);
tempAdapter.InsertCommand = tempBuilder.GetInsertCommand();
tempAdapter.UpdateCommand = tempBuilder.GetUpdateCommand();
tempAdapter.TableMappings.Add("Table", tableName);
DataSet ds = new DataSet();
//tempAdapter.Fill(ds);
DataTable tempTable = sourceData.Tables[tableName].Copy();
tempTable.TableName = tableName;
ds.Merge(tempTable);
tempAdapter.Update(ds, tableName);
sourceData.Tables[tableName].AcceptChanges();
}
public void ExtractTableParameters(string TableName, System.Data.IDbDataAdapter adapter, out DatabaseCache InsertCache, out DatabaseCache DeleteCache, out DatabaseCache UpdateCache, out DatabaseCache IsExistCache, out System.Data.DataTable dt)
{
adapter.SelectCommand.CommandText = "select top 1 * from " + TableName;
DataSet ds = new DataSet();
dt = adapter.FillSchema(ds, SchemaType.Source)[0];
dt.TableName = TableName;
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter as OleDbDataAdapter);
builder.ConflictOption = ConflictOption.OverwriteChanges;
//builder.SetAllValues = false;
OleDbCommand 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;
}
}
OleDbCommand UpdateCmd = builder.GetUpdateCommand(true);
UpdateCache = new DatabaseCache(UpdateCmd.CommandText, UpdateCmd.Parameters);
UpdateCache.CurrentTable = dt;
OleDbCommand 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 bool UpdateSemester(DataTable semester, int Kaf, int Year, int Semester_Number)
{
string command_str = "SELECT [Year], [Semester], [Department_ID], [Disc_ID], [Cycle_ID], [Norm], [Bachelor], [Kursovaya], [KKS], [LK], [LZ], [PZ], [Weeks] FROM Nagruzka WHERE [Year]=" + Year.ToString() + " AND [Semester]=" + Semester_Number.ToString() +
" AND [Department_ID]=" + Kaf.ToString();
if (!Connect())
{
return(false);
}
OleDbDataAdapter adapt = new OleDbDataAdapter(command_str, conn);
OleDbCommandBuilder comand_b = new OleDbCommandBuilder(adapt);
comand_b.QuotePrefix = "[";
comand_b.QuoteSuffix = "]";
adapt.DeleteCommand = comand_b.GetDeleteCommand();
adapt.InsertCommand = comand_b.GetInsertCommand();
adapt.UpdateCommand = comand_b.GetUpdateCommand();
OleDbTransaction trans = conn.BeginTransaction();
if (trans == null)
{
MessageBox.Show("Нулл");
}
try
{
adapt.InsertCommand.Transaction = trans; //null object error here
adapt.UpdateCommand.Transaction = trans;
adapt.DeleteCommand.Transaction = trans;
adapt.AcceptChangesDuringUpdate = false;
adapt.Update(semester);
trans.Commit();
semester.AcceptChanges();
}
catch (Exception e)
{
trans.Rollback();
MessageBox.Show(e.Message);
return(false);
}
return(true);
}
// public static Microsoft.Office.Interop.Access.Application oAccess = null;
// static String dbpath = Environment.CurrentDirectory + "\\AdikaStyle.accdb'";
/// <summary>
/// שיטה המתחברת למסד נתונים וממלא את הטבלאות בדטה סט
/// </summary>
public static void ConnectToDB()
{
dsProject = new DataSet();
// מחזיר את המיקום הנוכחי של התוכנית
//System.IO.Directory.GetCurrentDirectory()
// con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + System.IO.Directory.GetCurrentDirectory() + @"\store.accdb'");
con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + System.IO.Directory.GetCurrentDirectory() + "\\AdikaStyle.accdb'");
adapters = new OleDbDataAdapter[TableNames.Length];
for (int i = 0; i < TableNames.Length; i++)
{
adapters[i] = new OleDbDataAdapter("select * from " + TableNames[i], con);
adapters[i].Fill(dsProject, TableNames[i]);
// בנית פקודות לעדכון
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapters[i]);
adapters[i].InsertCommand = builder.GetInsertCommand();
adapters[i].UpdateCommand = builder.GetUpdateCommand();
adapters[i].DeleteCommand = builder.GetDeleteCommand();
}
}
public override DbDataAdapter CreateAdapter(Table table, TableFilter filter)
{
var oleDbTable = (OleDbTable)table;
var adapter = new OleDbDataAdapter(table.GetBaseSelectCommandText(filter), this.connection);
if (!table.IsReadOnly)
{
var builder = new OleDbCommandBuilder(adapter);
builder.ConflictOption = ConflictOption.OverwriteChanges;
try {
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
} catch (InvalidOperationException) {
oleDbTable.SetReadOnly(true);
return(adapter);
}
}
return(adapter);
}
public int?UpdateAccessTableFromDataSet(DataSet dataSet, string tableName, string columns = "*")
{
int?newId = null;
using (OleDbConnection connection = new OleDbConnection(GetConnectionString())) {
OleDbDataAdapter discsAdapter = new OleDbDataAdapter($"SELECT {columns} FROM {tableName}", connection)
{
// InsertCommand = new OleDbCommand("INSERT INTO tblDiscs (DiscName, Wallet, Notes) VALUES (@DiscName, @WalletNum, @Notes)", connection)
};
OleDbCommandBuilder builder = new OleDbCommandBuilder(discsAdapter);
builder.GetInsertCommand();
connection.Open();
discsAdapter.Update(dataSet, tableName);
// get the new Autonumber (if you added multiple rows, will just return 0):
OleDbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT @@IDENTITY";
newId = (int)cmd.ExecuteScalar();
}
return(newId);
}
/// <summary>
/// Writes an updated table to a Microsoft Access database.
/// </summary>
/// <param name="dataTable"> DataTable to update Access mdb with</param>
/// <param name="filename">Access .mdb file</param>
/// <param name="sql">sql command</param>
/// <returns></returns>
static int Save(string filename, DataTable dataTable, string sql)
{
if (debugOutput)
{
Console.Write("Writing " + dataTable.TableName + " to " + filename);
}
CheckIfFileExists(filename);
string strAccessConn = GetConnectionString(filename);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
OleDbConnection connection = new OleDbConnection(strAccessConn);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(sql, connection);
da.RowUpdating += new OleDbRowUpdatingEventHandler(myDataAdapter_RowUpdating);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.UpdateCommand = cb.GetUpdateCommand();
// call Fill method only to make things work. (we ignore myDataSet)
// myDataAdapter.Fill(myDataSet,dataTable.TableName);
//Console.WriteLine(karlCB.GetUpdateCommand().CommandText);
//connection.Open();
da.Fill(myDataSet, dataTable.TableName);
int rowsChanged = da.Update(dataTable);
Console.WriteLine(rowsChanged + " rows changed");
return(rowsChanged);
}
public void dataGridDoldur(string komut)
{
try
{
ds.Clear();
baglanti.Open();
adaptor = new OleDbDataAdapter(komut, baglanti);
adaptor.Fill(ds, "Urunler");
bindingSource1.DataSource = ds.Tables["Urunler"];
gridControl1.DataSource = bindingSource1;
//dataNavigator1.DataSource = bindingSource1;
baglanti.Close();
gridView1.Columns[0].Caption = "Stok No";
gridView1.Columns[1].Caption = "Ürün Adı";
gridView1.Columns[2].Caption = "Kategori";
gridView1.Columns[3].Caption = "Ürün Açıklaması";
gridView1.Columns[4].Caption = "Stok Adedi";
gridView1.Columns[5].Caption = "Birim Adı";
gridView1.Columns[6].Caption = "Birim Fiyatı";
gridView1.Columns[7].Caption = "Ekleme Tarihi";
gridView1.Columns[8].Caption = "Güncelleme Tarihi";
gridView1.Columns[9].Caption = "Ürün Resmi";
gridView1.Columns[0].Width = 50;
gridView1.Columns[1].Width = 150;
gridView1.Columns[3].Width = 250;
OleDbCommandBuilder builder = new OleDbCommandBuilder(adaptor);
adaptor.InsertCommand = builder.GetInsertCommand();
adaptor.UpdateCommand = builder.GetUpdateCommand();
adaptor.DeleteCommand = builder.GetDeleteCommand();
label1.Caption = "Toplam Kayıt: " + gridView1.RowCount.ToString();
paraHesapla();
azalanVeyaKalmayanUrunler();
}
catch
{
}
}
// DataGridView'i db deki verilerle doldurmak
public void dataGridDoldur(String komut)
{
try
{
ds.Clear();
if (baglanti.State == ConnectionState.Closed)
{
baglanti.Open();
}
adaptor = new OleDbDataAdapter(komut, baglanti);
adaptor.Fill(ds, "Depo");
dataGridView1.DataSource = ds.Tables["Depo"];
adaptor.Dispose();
baglanti.Close();
dataGridView1.Columns[0].HeaderText = "Ürün Adı";
dataGridView1.Columns[1].HeaderText = "Kategori";
dataGridView1.Columns[2].HeaderText = "Adet";
dataGridView1.Columns[3].HeaderText = "Birim";
dataGridView1.Columns[4].HeaderText = "Konum";
dataGridView1.Columns[5].HeaderText = "UrunID";
dataGridView1.Columns[0].Width = 250;
dataGridView1.Columns[1].Width = 150;
dataGridView1.Columns[2].Width = 75;
dataGridView1.Columns[3].Width = 100;
dataGridView1.Columns[4].Width = 250;
OleDbCommandBuilder builder = new OleDbCommandBuilder(adaptor);
adaptor.InsertCommand = builder.GetInsertCommand();
adaptor.UpdateCommand = builder.GetUpdateCommand();
adaptor.DeleteCommand = builder.GetDeleteCommand();
}
catch
{
}
}
/// <summary>
/// Creates a OleDbDataAdpter with the given CommandText and associates it with the site's Live or TestDB as appropriate.
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public OleDbDataAdapter DataAdapter(string CommandText, bool UseCommandBuilder)
{
OleDbDataAdapter DA = new OleDbDataAdapter(GetCommand(CommandText));
OleDbCommandBuilder cb = default(OleDbCommandBuilder);
if (UseCommandBuilder)
{
cb = new OleDbCommandBuilder(DA);
cb.SetAllValues = false;
DA.DeleteCommand = cb.GetDeleteCommand();
DA.UpdateCommand = cb.GetUpdateCommand();
DA.InsertCommand = cb.GetInsertCommand();
DA.InsertCommand.Connection = DA.SelectCommand.Connection;
DA.UpdateCommand.Connection = DA.SelectCommand.Connection;
DA.DeleteCommand.Connection = DA.SelectCommand.Connection;
}
return(DA);
}
public static DbDataAdapter Adapter(string sql)
{
if (!Connected)
{
LastError = "Нет подключения!";
return(null);
}
var com = connection.CreateCommand();
com.CommandText = sql;
var ad = new OleDbDataAdapter(com);
try
{
var b = new OleDbCommandBuilder(ad);
ad.UpdateCommand = b.GetUpdateCommand();
ad.InsertCommand = b.GetInsertCommand();
}
catch { }
return(ad);
}
/// <summary>
/// 批量插入数据到数据库
/// </summary>
/// <param name="dt"></param>
/// <param name="DestinationTableName"></param>
/// <returns></returns>
public bool batchSeveData(DataTable dt, string DestinationTableName)
{
try
{
if (dt == null)
{
return(false);
}
OpenOle();
List <string> columnList = new List <string>();
foreach (DataColumn one in dt.Columns)
{
columnList.Add(one.ColumnName);
}
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("SELECT * FROM " + DestinationTableName, conn);
using (OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter))
{
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
adapter.InsertCommand = builder.GetInsertCommand();
foreach (string one in columnList)
{
adapter.InsertCommand.Parameters.Add(new OleDbParameter(one, dt.Columns[one].DataType));
}
adapter.Update(dt);
adapter.Dispose();
adapter = null;
}
}
catch (Exception E)
{
MessageBox.Show(E.Message);
}
finally
{
ColseOle();
}
return(true);
}
public static bool UpdateDataSet(string sql, DataSet ds)
{
lock (oleDbConn)
{
oleDbConn.Open();
OleDbTransaction trans = oleDbConn.BeginTransaction();
try
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter(sql, trans.Connection);
myAdapter.ContinueUpdateOnError = false;
myAdapter.SelectCommand = new OleDbCommand();
myAdapter.SelectCommand.Connection = trans.Connection;
myAdapter.SelectCommand.CommandText = sql;
OleDbCommandBuilder builder = new OleDbCommandBuilder();
builder.DataAdapter = myAdapter;
builder.ConflictOption = ConflictOption.OverwriteChanges;
myAdapter.SelectCommand.Transaction = trans;
myAdapter.InsertCommand = builder.GetInsertCommand();
myAdapter.InsertCommand.Transaction = trans;
myAdapter.DeleteCommand = builder.GetDeleteCommand();
myAdapter.DeleteCommand.Transaction = trans;
myAdapter.UpdateCommand = builder.GetUpdateCommand();
myAdapter.UpdateCommand.Transaction = trans;
string srcTable = ds.Tables[0].TableName;
myAdapter.Update(ds, srcTable);
trans.Commit();
}
catch (OleDbException ex)
{
trans.Rollback();
MessageHandle.MessageError(ex.ToString(), "错误信息");
return(false);
}
finally
{
oleDbConn.Close();
}
return(true);
}
}
public override int BlockCommand(DataTable dt)
{
lock (lockObject)
{
int result = 0;
if (!CheckConn())
{
return(0);
}
if (dt.TableName == "")
{
All.Class.Error.Add("无法完成批量数据更新,数据表名不能为空");
All.Class.Error.Add(Environment.StackTrace);
return(0);
}
try
{
using (OleDbCommand cmd = new OleDbCommand(string.Format("select * from {0}", dt.TableName), conn))
{
using (OleDbDataAdapter ada = new OleDbDataAdapter(cmd))
{
using (OleDbCommandBuilder scb = new OleDbCommandBuilder(ada))
{
ada.InsertCommand = scb.GetInsertCommand();
ada.DeleteCommand = scb.GetDeleteCommand();
ada.UpdateCommand = scb.GetUpdateCommand();
result = ada.Update(dt);
}
}
}
}
catch (Exception e)
{
All.Class.Error.Add(string.Format("出错Table为：{0}", dt.TableName));
All.Class.Error.Add(e);//数据库中一定要有主键，不然当前方法会出错。即没有办法生成删除命令
}
return(result);
}
}
private void InsertData(OleDbConnection con)
{
OleDbDataAdapter oleAdap = new OleDbDataAdapter("SELECT * FROM [" + this.TableName + "]", con);
OleDbCommandBuilder oleCmdBuilder = new OleDbCommandBuilder(oleAdap);
oleCmdBuilder.QuotePrefix = "[";
oleCmdBuilder.QuoteSuffix = "]";
OleDbCommand cmd = oleCmdBuilder.GetInsertCommand();
foreach (TEntity row in this.DataSource)
{
var pVals = GetEntityValues(row);
int index = 0;
foreach (OleDbParameter param in cmd.Parameters)
{
param.Value = pVals.ElementAt(index);
index++;
}
cmd.ExecuteNonQuery();
}
}
public static void InsertDataToTmp(DataTable FillTable, string InsertTableName, string strSql)
{
string TmpDataPath = DataCore.Global.GB_Base.AccessLink.Substring(0, DataCore.Global.GB_Base.AccessLink.LastIndexOf(@"\")) + @"\ClouMeterDataTmp.mdb";
string Sql_word_1 = "Provider=Microsoft.ACE.OleDb.12.0;Data Source=";
string Sql_word_2 = ";Persist Security Info=False";
DataTable MeterTable = new DataTable();
using (OleDbConnection conn = new OleDbConnection(TmpDataPath + Sql_word_2))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string Sql = string.Format("select * from {0} where 1=1", "TMP_" + InsertTableName);
//string Sql = string.Format("Insert into {0} select * from OPENDATASOURCE ({1})...{2} ", "TMP_" + InsertTableName, @"'Microsoft.ACE.OleDb.12.0';'Data Source=D:\A_temp\CL3000S-H-NW-20170627\CL3000S-H-NW\Resource\Client\DataBase\ClouMeterDataTmp.mdb;Persist Security Info=False'", InsertTableName);
OleDbCommand cmd = new OleDbCommand(Sql, conn);
OleDbDataAdapter ada = new OleDbDataAdapter(Sql, conn);
ada.AcceptChangesDuringFill = false;
ada.Fill(MeterTable);
MeterTable = FillTable;
MeterTable.PrimaryKey = new DataColumn[] { MeterTable.Columns[MeterTable.Columns[0].ColumnName] };
OleDbCommandBuilder Comb = new OleDbCommandBuilder(ada);
ada.InsertCommand = Comb.GetInsertCommand();
ada.Update(MeterTable);
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy( TmpDataPath + Sql_word_2))
//{
// bulkCopy.DestinationTableName = InsertTableName;
// bulkCopy.BatchSize = FillTable.Rows.Count;
// for (int i = 0; i < FillTable.Columns.Count; i++)
// {
// bulkCopy.ColumnMappings.Add(FillTable.Columns[i].ColumnName, FillTable.Columns[i].ColumnName);
// }
// bulkCopy.WriteToServer(FillTable);
//}
}
}
private void SaveRegisto(Registos reg)
{
try
{
var da = new OleDbDataAdapter("select * from tbl_registos", _cnn);
var cmdb = new OleDbCommandBuilder(da);
OleDbCommand cmd = cmdb.GetInsertCommand();
cmd.Parameters[0].Value = reg.IdColaborador;
cmd.Parameters[1].Value = reg.Data;
cmd.Parameters[2].Value = reg.Tipo;
cmd.Parameters[3].Value = reg.Observações ?? "";
cmd.ExecuteNonQuery();
cmd.Dispose();
cmdb.Dispose();
da.Dispose();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 对Datatable数据进行批量更新处理
/// </summary>
/// <param name="tableName">Access表名称</param>
/// <param name="dt">数据内容</param>
public static void ExcuteTableSql(DataTable dt)
{
using (OleDbConnection conn = new OleDbConnection(string.Format(connectionString, App.project_path)))
{
List <string> columnList = new List <string>();
foreach (DataColumn one in dt.Columns)
{
columnList.Add(one.ColumnName);
}
using (OleDbDataAdapter adapter = new OleDbDataAdapter())
{
adapter.SelectCommand = new OleDbCommand("select * from " + dt.TableName, conn);
using (OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter))
{
adapter.InsertCommand = builder.GetInsertCommand();
foreach (string one in columnList)
{
adapter.InsertCommand.Parameters.Add(new OleDbParameter(one, dt.Columns[one].DataType));
}
adapter.Update(dt);
}
}
}
}
/// <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");
this.initialRows = dataset.Tables[0].Rows.Count;
}
catch (Exception e)
{
this.connection.Close();
this.connection.Dispose();
}
}
