protected void Button3_Click(object sender, EventArgs e) { if (this.IsValid) { string con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath("App_data/db1.mdb"); OleDbConnection ole = new OleDbConnection(con); DataSet ds = new DataSet(); DataRow drow; ole.Open(); OleDbDataAdapter oda = new OleDbDataAdapter("select * from yonghu", con); OleDbCommandBuilder ob = new OleDbCommandBuilder(oda); oda.Fill(ds, "tab"); int num = ds.Tables["tab"].Rows.Count; drow = ds.Tables["tab"].NewRow(); drow[0] = num + 1; num++; drow[1] = TextBox2.Text; drow[2] = TextBox3.Text; ds.Tables["tab"].Rows.Add(drow); oda.Update(ds, "tab"); ole.Close(); this.Panel3.Visible = false; this.Label2.Visible = true; this.Label3.Visible = true; this.Button4.Visible = true; } }
public override DataTable GetRecords(string strWhere, string strGroupBy, string strOrderBy) { if (m_pOleDbConnection != null) { try { m_nCurrentRowIndex = 0; string commandText = "Select * from " + "(Select LineNodeID as LineID,X1 as PX1,Y1 as PY1,X2 as PX2,Y2 as PY2,-1 from LineNodeEx Where EntityID=-1 " + "union Select LineNodeID as LineID,X2 as PX1,Y2 as PY1,X1 as PX2,Y1 as PY2,1 from LineNodeEx Where EntityID=-1) " + "Order By PX1,PY1,PX2,PY2,LineID"; m_pOleDbDataAdapter = new OleDbDataAdapter(commandText, m_pOleDbConnection); OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(m_pOleDbDataAdapter); return GetNextRecords(); } catch (Exception ex) { Logger.WriteErrorLog(ex); } } return null; }
public void searcher(string field, string column) { try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select ISBN, Title, Author, Publisher, Binding, pub_date as Publication_Date, Pages, Available from book where " + column + " like '%" + escapeHandling(field) + "%' order by Author, Title;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvBook.DataSource = bs; da.Update(dt); databaseHandler d = new databaseHandler(); d.closeDatabaseConnection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } dgvBook.ClearSelection(); }
public void fillgv(string query) { OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\sciencedb.accdb"; OleDbCommand oledbRunCommand = new OleDbCommand(); oledbRunCommand.CommandText = query; oledbRunCommand.CommandType = CommandType.Text; oledbRunCommand.Connection = connection; //create an OleDbDataAdapter to execute the query OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connection); //create a command builder OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter); //create a DataTable to hold the query results //fill the DataTable dAdapter.Fill(dTable); //the DataGridView //DataGridView dgView = new DataGridView(); //BindingSource to sync DataTable and DataGridView BindingSource bSource = new BindingSource(); //set the BindingSource DataSource bSource.DataSource = dTable; //set the DataGridView DataSource locationTableDataGridView.DataSource = bSource; connection.Close(); }
/// <summary>Delete the specified Cause from the specified Issue.</summary> /// <param name="pIssueID">ID of the Issue the cause applies to.</param> /// <param name="pCauseID">ID of the Cause to delete.</param> /// <returns>ID of the modified/created cause row.</returns> public void Delete(int pIssueID, int pCauseID) { try { using(OleDbDataAdapter oDA = new OleDbDataAdapter(String.Format("Select * from Causes where issue_id = {0} and cause_id = {1} order by cause_seq", pIssueID, pCauseID), this.DataObject.ConnectionString)) using(OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA)) using(OleDbConnection oConn = new OleDbConnection(this.DataObject.ConnectionString)) { // Get the list of previously saved Causes to the current Issue. oConn.Open(); DataSet oDS = new DataSet("Causes"); oDA.Fill(oDS); // If we found any...And we're updating an existing Cause... if(oDS.Tables[0].Rows.Count > 0) { DataRow oRow = oDS.Tables[0].Rows[0]; oRow.Delete(); oDA.Update(oDS); } } } catch(Exception e) { throw new Exception("Unable to save new Issue Cause!\nError: " + e.Message); } }
private void updated_dataview() { OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + reference.Getdb(); OleDbDataAdapter dataAdapter; try { dataAdapter = new OleDbDataAdapter("select * from printer", connection); OleDbCommandBuilder command = new OleDbCommandBuilder(dataAdapter); // Populate a new data table and bind it to the BindingSource. printers = new DataTable(); printers.Locale = System.Globalization.CultureInfo.InvariantCulture; dataAdapter.Fill(printers); printers_listbox.DataSource = printers; printers_listbox.DisplayMember = "location"; currentchart = printers.Rows[0].ItemArray.GetValue(0).ToString(); } catch (Exception e3) { MessageBox.Show(e3.Message); } finally { connection.Close(); } }
private void frmWhatMedia_Load(object sender, EventArgs e) { try { string connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select Title, isbn as Checkout_Code from (select title, isbn from book union select title, upc from movie union select title,upc from game union select album,upc from cd) order by title;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvMedia.DataSource = bs; da.Update(dt); databaseHandler d = new databaseHandler(); d.closeDatabaseConnection(); DataGridViewColumn c = dgvMedia.Columns[0]; c.Width = 155; dgvMedia.ClearSelection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void GetData(string selectCommand) { try { // Specify a connection string. Replace the given value with a // valid connection string for a Northwind SQL Server sample // database accessible to your system. //System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Otros\Documents\PVI_Alumnos2.accdb"); String conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Otros\\Documents\\PVI_Alumnos2.accdb"; // Create a new data adapter based on the specified query. dataAdapter = new OleDbDataAdapter(selectCommand, conn); // Create a command builder to generate SQL update, insert, and // delete commands based on selectCommand. These are used to // update the database. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter); // Populate a new data table and bind it to the BindingSource. table = new DataSet("Alumnos"); //table.Locale = System.Globalization.CultureInfo.InvariantCulture; dataAdapter.Fill(table); dataGridView1.DataSource = table.Tables[0]; //bindingSource1.DataSource = table; // Resize the DataGridView columns to fit the newly loaded content. dataGridView1.AutoResizeColumns( DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader); } catch (SqlException) { MessageBox.Show("To run this example, replace the value of the " + "connectionString variable with a connection string that is " + "valid for your system."); } }
protected void Button1_Click1(object sender, EventArgs e) { string username=this.TextBox1.Text; string con="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+System.Web.HttpContext.Current.Server.MapPath("App_data/db1.mdb"); OleDbConnection ole=new OleDbConnection(con); DataSet ds=new DataSet(); DataRow drow; ole.Open(); OleDbCommand olec=new OleDbCommand("select * from yonghu where yname='"+username+"'",ole); int count=Convert.ToInt32(olec.ExecuteScalar()); OleDbDataAdapter oda=new OleDbDataAdapter ("select * from yonghu",ole); OleDbCommandBuilder ob=new OleDbCommandBuilder(oda); oda.Fill(ds,"tab"); if (count > 0) { this.Label1.Text = "昵称已存在,请重新输入!"; } else { drow = ds.Tables["tab"].NewRow(); drow[0] = ds.Tables["tab"].Rows.Count + 1; drow[1] = this.TextBox1.Text; ds.Tables["tab"].Rows.Add(drow); oda.Update(ds, "tab"); ole.Close(); Session["name"] = Request["TextBox1"]; Application["message"] = ""; Response.Redirect("HTMLPage.htm"); } }
private static void Test_Command_CommandBuilder() { (new OleDbCommand("drop table if exists t", conn)).ExecuteNonQuery(); (new OleDbCommand("create table t(id int not null default 1,name string default 'cubrid',PRIMARY KEY (id))", conn)).ExecuteNonQuery(); (new OleDbCommand("insert into t(id,name) value(1,'cubrid');", conn)).ExecuteNonQuery(); DataSet dataSet = new DataSet(); DataTable dataTable = new DataTable(); string query = "select id,name from t"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn); OleDbCommandBuilder commandBuilder =new OleDbCommandBuilder(dataAdapter); dataAdapter.Fill(dataTable); //Without the OleDbCommandBuilder this line would fail. foreach (System.Data.DataRow row in dataTable.Rows) { foreach (System.Data.DataColumn col in dataTable.Columns) { Assert.AreEqual(row[col], 1); break; } } OleDbCommand t1 = commandBuilder.GetInsertCommand(); Assert.AreEqual(t1.CommandText, "INSERT INTO T DEFAULT VALUES"); }
private void getData(string sqlCommand) { try { users = new DataSet(); dataAdapter = new OleDbDataAdapter(sqlCommand, DbConn.connString); dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter); commandBuilder.QuotePrefix = "["; commandBuilder.QuoteSuffix = "]"; dataAdapter.Fill(users,"users"); dataGridView.DataSource = users.Tables["users"]; dataGridView.AutoGenerateColumns = true; // dataGridView.Columns[0].ReadOnly = true; } catch (Exception e) { MessageBox.Show(e.Message); } }
// ExStart:FillDataSet private void DataBindingFeatures_Load(object sender, EventArgs e) { // The path to the documents directory. string dataDir = Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Creating Select query to fetch data from database string query = "SELECT * FROM Products ORDER BY ProductID"; // Creating connection string to connect with database string conStr = @"Provider=microsoft.jet.oledb.4.0;Data Source=" + dataDir + "dbDatabase.mdb"; // Creating OleDbDataAdapter object that will be responsible to open/close connections with database, fetch data and fill DataSet with data adapter = new OleDbDataAdapter(query, conStr); // Setting MissingSchemaAction to AddWithKey for getting necesssary primary key information of the tables adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; /* * Creating OleDbCommandBuilder object to create insert/delete SQL commmands * automatically that are used by OleDbDatAdapter object for updating * changes to the database */ cb = new OleDbCommandBuilder(adapter); // Creating DataSet object ds = new DataSet(); // Filling DataSet with data fetched by OleDbDataAdapter object adapter.Fill(ds, "Products"); }
private void GetData(string selectCommand, BindingSource a, DataTable b) { try { // Specify a connection string. Replace the given value with a // valid connection string for a Northwind SQL Server sample // database accessible to your system. // Create a new data adapter based on the specified query. OleDbDataAdapter dataAdapter = new OleDbDataAdapter(selectCommand, connexionstring); // Create a command builder to generate SQL update, insert, and // delete commands based on selectCommand. These are used to // update the database. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter); // Populate a new data table and bind it to the BindingSource. b.Locale = System.Globalization.CultureInfo.InvariantCulture; dataAdapter.Fill(b); a.DataSource = b; // Resize the DataGridView columns to fit the newly loaded content. /* Vente_JourdataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader); Stock_dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader); Historique_dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);*/ } catch (OleDbException) { MessageBox.Show("Erreur !!! "); } }
private void subGetData(string _strSELECT) { try { // Specify a connection string. Replace the given value with a // valid connection string for a Northwind SQL Server sample // database accessible to your system. String strConn = clsAppSettings.GetAppSettings().strCTConn; // Create a new data adapter based on the specified query. daMORNCOAAlerts = new OleDbDataAdapter(_strSELECT, strConn); // Create a command builder to generate SQL update, insert, and // delete commands based on _strSELECT. These are used to // update the database. OleDbCommandBuilder cmdMORNCOAAlerts = new OleDbCommandBuilder(daMORNCOAAlerts); // Populate a new data table and bind it to the BindingSource. DataTable dtMORNCOAAlerts = new DataTable(); dtMORNCOAAlerts.Locale = System.Globalization.CultureInfo.InvariantCulture; daMORNCOAAlerts.Fill(dtMORNCOAAlerts); srcMORNCOAAlerts.DataSource = dtMORNCOAAlerts; } catch (OleDbException) { MessageBox.Show("To run this example, replace the value of the " + "strConn variable with a connection string that is " + "valid for your system."); } }
static void Main(string[] args) { //dateTime.DataSet1TableAdapters.table1TableAdapter table = new dateTime.DataSet1TableAdapters.table1TableAdapter(); //table.Insert("arif", "khan", "hasan", "1988-01-10", "100.00"); //Console.ReadLine(); OleDbConnection con = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=D:\Documents and Settings\axkhan2\Desktop\table\table1.dbf"); con.Open(); OleDbDataAdapter adapt = new OleDbDataAdapter(@"select * from 'D:\Documents and Settings\axkhan2\Desktop\table\table1.dbf'", con); OleDbCommandBuilder build = new OleDbCommandBuilder(adapt); DataSet set = new DataSet(); adapt.Fill(set); DataTable table = set.Tables[0]; DataRow row = table.NewRow(); row["firstname"] = "arifkhan"; row["lastname"] = "khan"; row["middlename"] = "mname"; row["dob"] = DateTime.Now; row["amount"] = 100; table.Rows.Add(row); //adapt.Update(table); //adapt.UpdateCommand = build.GetUpdateCommand(); adapt.Update(table); //cmd.ExecuteNonQuery(); }
private void ReserveMenu_Load(object sender, EventArgs e) { AirAsianDataBase = new OleDbConnection(connectionString); AirAsianDataBase.Open(); string strSelect = "Select * from AirCraft"; TripGridView.DataSource = null; TripGridView.Rows.Clear(); TripGridView.Refresh(); OleDbDataAdapter da = new OleDbDataAdapter( strSelect, AirAsianDataBase); OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(da); DataTable dataTable = new DataTable(); da.Fill(dataTable); for (int i = 0; i < dataTable.Rows.Count; i++) { TripGridView.Rows.Add( dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dataTable.Rows[i][5]); } AirAsianDataBase.Close(); }
private void clears() { txtNameSearch.Text = "Enter all or part of a name here..."; txtCCSearch.Text = "Scan a customer card here..."; try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select * from customer order by last_name;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvCustomers.DataSource = bs; da.Update(dt); databaseHandler d = new databaseHandler(); d.closeDatabaseConnection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
public override DataTable GetRecords(string strWhere, string strGroupBy, string strOrderBy) { if (m_pOleDbConnection != null) { try { m_nCurrentRowIndex = 0; /* * Select a.LineNodeID,b.LineNodeID,a.PolygonID,b.PolygonID,a.IsReverse,b.IsReverse,a.EntityID,a.IsFromLine From LineNodeEx as a left join LineNodeEx as b on a.OrtherIndexID=b.LineNodeID Order By a.PolygonID,a.LineIndex * */ string commandText = "Select a.LineNodeID as LineNodeID,b.LineNodeID as OtherLineNodeID,a.PolygonID as PolygonID,b.PolygonID as OtherPolygonID,a.IsReverse as IsReverse,b.IsReverse as OtherIsReverse,a.EntityID as EntityID,b.EntityID as OtherEntityID,a.LineIndex as LineIndex,b.LineIndex as OtherLineIndex"//,a.IsFromLine as IsFromLine + " From LineNodeEx as a left join LineNodeEx as b on a.OrtherLineNodeID=b.LineNodeID Where a.IsFromLine=-1" + " Order By a.PolygonID,a.LineIndex"; m_pOleDbDataAdapter = new OleDbDataAdapter(commandText, m_pOleDbConnection); OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(m_pOleDbDataAdapter); return GetNextRecords(); } catch (Exception ex) { LogAPI.WriteErrorLog(ex); } } return null; }
private void FrmConfigDatabase_Load(object sender, EventArgs e) { if (m_cnADONetConnection.State == ConnectionState.Open) { m_cnADONetConnection.Close(); } m_cnADONetConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Windows.Forms.Application.StartupPath + @"\ConfigDatabase.mdb"; m_cnADONetConnection.Open(); m_daDataAdapter = new OleDbDataAdapter("Select * From DatabaseInfo", m_cnADONetConnection); OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter); m_daDataAdapter.Fill(m_dtDict); if(m_dtDict.Rows.Count != 0) { listBox1.Items.Add("Tên Server : " + m_dtDict.Rows[0]["ServerName"]); listBox1.Items.Add("Tên Instance : " + m_dtDict.Rows[0]["InstanceName"]); listBox1.Items.Add("Tên Database : " + m_dtDict.Rows[0]["DatabaseName"]); if (m_dtDict.Rows[0]["Mode"].ToString() == "AUT") { listBox1.Items.Add("Window Authentication"); } else { listBox1.Items.Add("UserName : "******"UserID"]); userId = m_dtDict.Rows[0]["UserID"].ToString(); pass = m_dtDict.Rows[0]["Password"].ToString(); } mode = m_dtDict.Rows[0]["Mode"].ToString(); serverName = m_dtDict.Rows[0]["ServerName"].ToString(); instanceName = m_dtDict.Rows[0]["InstanceName"].ToString(); databaseName = m_dtDict.Rows[0]["DatabaseName"].ToString(); isConfiged = true; } }
private void button2_Click(object sender, EventArgs e) { String query = "select * from Admin ;"; DatabaseManager dbManager = DatabaseManager.dataBaseManager(); dbManager.BookConn = new OleDbConnection(dbManager.getConnParam()); OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, dbManager.BookConn); OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter); DataTable dataTable = new DataTable(); DataSet ds = new DataSet(); dAdapter.Fill(dataTable); DataRow[] dra = dataTable.Select(""); foreach (DataRow dr in dra) { if (textBox1.Text.Equals(dr["Sifre"] + "")) { AdminEkrani f22 = new AdminEkrani(); this.Visible = false; f22.Show(); } else { MessageBox.Show("ADMIN SIFRESI HATALI"); } } }
private void btnSales_Click(object sender, EventArgs e) { try { loginConn.Open(); string strSelect = "SELECT * FROM TopSales"; dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); OleDbDataAdapter da = new OleDbDataAdapter(strSelect, loginConn); OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(da); DataTable dataTable = new DataTable(); da.Fill(dataTable); for (int i = 0; i < dataTable.Rows.Count; i++) { dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1]); dataGridView1.Sort(dataGridView1.Columns["TotalTransaction"], ListSortDirection.Descending); } loginConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); loginConn.Close(); } }
/// <summary> /// 插入数据通过Datatable /// </summary> /// <param name="_dt"></param> /// <returns>影响记录条数</returns> public override int DataTableInsert(DataTable _dt) { bool flag = false; int _nResult = 0; if (_dt == null) return _nResult; string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName); OleDbCommand _Command = (OleDbCommand)CreateCommand(_sCmdText, CommandType.Text); OleDbDataAdapter _adapter = new OleDbDataAdapter(_Command); OleDbDataAdapter _adapter1 = new OleDbDataAdapter(_Command); OleDbCommandBuilder _builder = new OleDbCommandBuilder(_adapter1); _adapter.InsertCommand = _builder.GetInsertCommand(); if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count) { flag = true;//因为表中有自增字段,所以CommandBuild生成的InserttCommand的参数中少了自增字段 foreach (DataColumn _dc in _dt.Columns) { if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName)) { _adapter.InsertCommand.CommandText = _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName); _adapter.InsertCommand.CommandText = _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",?"); _adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, OleDbType.Decimal, _dc.MaxLength, _dc.ColumnName); if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count) break; } } } if (flag) this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName)); this.BeginTransaction(); try { _adapter.InsertCommand.Transaction = _Command.Transaction; _Command.CommandText = "delete from " + _dt.TableName; _Command.ExecuteNonQuery(); _nResult = _adapter.Update(_dt); this.CommitTransaction(); } catch (Exception ex) { this.RollbackTransaction(); throw ex; } finally { if (flag) this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName)); } return _nResult; }
public OleDbManager() { daTickets = new OleDbDataAdapter(); string selectSql = "SELECT Id, AccountId, DateOpened, State FROM tblTickets"; OleDbCommand selectCmd = new OleDbCommand(selectSql, GetConnection()); daTickets.SelectCommand = selectCmd; OleDbCommandBuilder cb = new OleDbCommandBuilder(daTickets); }
private void ExcelConnection(string ExcelFiles) { con= new OleDbConnection("provider=Microsoft.Jet.OleDb.4.0;data source=" + ExcelFiles + ";" + "extended properties=excel 8.0;"); Excel_da = new OleDbDataAdapter("select * from [sheet1$]", con); Excel_dt = new System.Data.DataTable(); OleDbCommandBuilder cb = new OleDbCommandBuilder(Excel_da); Excel_da.Fill(Excel_dt); }
/// <summary> /// ʹ���������ģʽ /// �û������ڷ��ر�ṹ��DataTable��������� /// �ٵ���Update(DataTable)���и��²��� /// �˺����˳���ģʽ״̬ /// </summary> /// <param name="TableName">��Ҫ����ı�����</param> /// <returns>Ҫ����Ŀ���Ľṹ</returns> public override DataTable InsertMode(string TableName) { dataManager.execNum++; System.Data.DataTable dt = new DataTable(); _dap = new OleDbDataAdapter("select * from [" + TableName + "] where false", dataManager._conn); _cmdb = new OleDbCommandBuilder(_dap); _dap.Fill(dt); return dt; }
//3.5.0.0 & 3.6.0.0 //TODO: Change back to Private and reduce the CONDID to one joined column public int DbRecsGetCondTabPreValues(string strCondIdMasterCondId, string strCondId_PotFireStandId, string strCurRxFilePath) { int intRet = 0; string strSqlCond = @"SELECT * FROM COND WHERE BIOSUM_COND_ID = '{0}'"; string strSqlFvsPotFire = @"SELECT * FROM FVS_POTFIRE WHERE STANDID = '{0}'"; //TODO: Missing not_calc_yn, & what happens if the row count dn match - the row Cond ID must be less? string[] strSqlCondFilter = new string[12] { "pre_Tot_Flame_Mod", "pre_Tot_Flame_Sev", "pre_Fire_Type_Severe", "pre_Fire_Type_Mod", "pre_Torch_Index", "pre_Crown_Index", "pre_Canopy_Ht", "pre_Canopy_Density", "pre_Mortality_BA_Severe", "pre_Mortality_BA_Mod", "pre_Mortality_VOL_Severe", "pre_Mortality_VOL_Mod" }; string[] strSqlFvsPfFilter = new string[12] { "Flame_Len_Mod", "Flame_Len_Severe", "Fire_Type_Severe", "Fire_Type_Mod", "Torch_Index", "Crown_Index", "Canopy_Ht", "Canopy_Density", "Mortality_BA_Severe", "Mortality_BA_Mod", "Mortality_VOL_Severe", "Mortality_VOL_Mod" }; DataTable dtCond = new DataTable("COND"); DataTable dtFvsPF = new DataTable("FVS_PF"); try { ado_optimizer_tables adoCore = new ado_optimizer_tables(); adoCore.m_OleDbConnection = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathMasterDbFile)); adoCore.m_OleDbConnection.Open(); adoCore.m_daCond = new System.Data.OleDb.OleDbDataAdapter(string.Format(strSqlCond, strCondIdMasterCondId), adoCore.m_OleDbConnection.ConnectionString); adoCore.m_daCond.Fill(dtCond); ado_optimizer_tables adoFvs = new ado_optimizer_tables(); adoFvs.m_OleDbConnection = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, strCurRxFilePath)); adoFvs.m_OleDbConnection.Open(); adoFvs.m_daCond = new System.Data.OleDb.OleDbDataAdapter(string.Format(strSqlFvsPotFire, strCondId_PotFireStandId), adoFvs.m_OleDbConnection.ConnectionString); OLE.OleDbCommandBuilder oleCmdB = new System.Data.OleDb.OleDbCommandBuilder(adoFvs.m_daCond); adoFvs.m_daCond.Fill(dtFvsPF); if (dtCond.Rows.Count <= dtFvsPF.Rows.Count) { for (short i = 0; i < dtCond.Rows.Count; i++) { dtCond.Rows[i].BeginEdit(); for (short x = 0; x < strSqlCondFilter.Length; x++) { dtCond.Rows[i][strSqlCondFilter[x]] = dtFvsPF.Rows[i][strSqlFvsPfFilter[x]]; intRet++; } dtCond.Rows[i].EndEdit(); } adoFvs.m_daCond.Update(dtCond); dtCond.AcceptChanges(); } } catch (Exception e) { intRet = 0; throw e; } return(intRet); }
public DataTable ExtractValues(string query) { DataTable table = new DataTable(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, Connection); OleDbCommandBuilder cmd = new OleDbCommandBuilder(dataAdapter); dataAdapter.Fill(table); //ciudat asa se face sigur?:D folosesc mai sus acelasi lucru pe dgeisp_query si merge lejer, aici nu mai vrea, is instante diferite deci ce plm return table; }
/// <summary> /// 使对象将进入修改模式状态 /// 用户可以修改返回结果,包括删除、修改和增加行 /// 再调用Update(DataTable)进行更新操作 /// 此后,对象将退出修改模式状态 /// </summary> /// <param name="SQLCmd">SQL语句</param> /// <returns>查询响应结果</returns> public override DataTable SelectWithUpdate(string SQLCmd) { dataManager.execNum++; System.Data.DataTable dt = new DataTable(); _dap = new OleDbDataAdapter(SQLCmd, dataManager._conn); _cmdb = new OleDbCommandBuilder(_dap); _dap.Fill(dt); return(dt); }
/// <summary> /// 使对象进入修改模式 /// 用户可以在返回表结构的DataTable中添加数据 /// 再调用Update(DataTable)进行更新操作 /// 此后,对象将退出修改模式状态 /// </summary> /// <param name="TableName">需要插入的表名称</param> /// <returns>要插入目标表的结构</returns> public override DataTable InsertMode(string TableName) { dataManager.execNum++; System.Data.DataTable dt = new DataTable(); _dap = new OleDbDataAdapter("select * from [" + TableName + "] where false", dataManager._conn); _cmdb = new OleDbCommandBuilder(_dap); _dap.Fill(dt); return(dt); }
/// <summary>Saves the passed IssueType description to the passed IssueType ID if it exists. If it doesn't exist, creates a new row.</summary> /// <param name="pIssueTypeID">ID of the IssueType to update (if it exists). -1 indicates an explicit Insert.</param> /// <param name="pDescription">Description to set.</param> /// <returns>ID of the modified/created row.</returns> public int Save(int pIssueTypeID, string pDescription) { int iID = 0; try { using(OleDbDataAdapter oDA = new OleDbDataAdapter(String.Format("select * from IssueTypes where issue_type_id = {0}", pIssueTypeID), this.m_oDBConn.ConnectionString)) using(OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA)) using(OleDbConnection oConn = new OleDbConnection(this.m_oDBConn.ConnectionString)) { oConn.Open(); DataSet oDS = new DataSet("IssueTypes"); oDA.Fill(oDS); if((oDS.Tables[0].Rows.Count > 0) && (pIssueTypeID > 0)) { // Should only be one row returned, but go ahead and update all found. foreach(DataRow oRow in oDS.Tables[0].Rows) { if((int)oRow["issue_type_id"] == pIssueTypeID) { oRow.BeginEdit(); oRow["issue_type_desc"] = pDescription; oRow["date_last_modified"] = DateTime.Now; oRow.EndEdit(); } } } else { // Determine the next ID number using(DataSet oDS2 = new DataSet()) using(OleDbDataAdapter oDA2 = new OleDbDataAdapter("select max(issue_type_id)+1 as issue_type_id from IssueTypes", this.m_oDBConn.ConnectionString)) using(OleDbConnection oConn2 = new OleDbConnection(this.m_oDBConn.ConnectionString)) { oDA2.Fill(oDS2); iID = (oDS2.Tables[0].Rows.Count > 0) ? (int)oDS2.Tables[0].Rows[0]["issue_type_id"] : 1; } DataRow oRow = oDS.Tables[0].NewRow(); oRow["issue_type_id"] = iID; oRow["issue_type_desc"] = pDescription; oRow["date_created"] = DateTime.Now; oRow["date_last_modified"] = DateTime.Now; oDS.Tables[0].Rows.Add(oRow); } oDA.Update(oDS); } } catch(Exception e) { throw new Exception("Unable to save new IssueType!\nError: " + e.Message); } return iID; }
public static void CapNhatBang(DataSet ds) { OleDbConnection cn = DataProvider.ConnectionData(); string strSQL = "Select * From LoaiSach"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); da.Update(ds.Tables[0]); //B5: Dong ket noi CSDL cn.Close(); }
public void Gravar(DataSet ds, string tabela) { OleDbCommandBuilder cmdBuilder; OleDbDataAdapter adaptador; adaptador = new OleDbDataAdapter("select * from " + tabela, conexão); cmdBuilder = new OleDbCommandBuilder(adaptador); adaptador.Update(ds, tabela); }
/// <summary>Saves the passed Contact description to the passed Contact ID if it exists. If it doesn't exist, creates a new row.</summary> /// <param name="pID">ID of the Contact to update (if it exists).</param> /// <param name="pDescription">Description to set.</param> /// <returns>ID of the modified/created row.</returns> public int Save(int pContactID, string pLastName, string pFirstName, string pEmail, string pPhone) { int iID = (pContactID > 0) ? pContactID : 1; try { using(OleDbDataAdapter oDA = new OleDbDataAdapter(String.Format("select * from Contacts where contact_id = {0}", pContactID), this.DataObject.ConnectionString)) using(OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA)) using(OleDbConnection oConn = new OleDbConnection(this.DataObject.ConnectionString)) { // oConn.Open(); DataSet oDS = new DataSet("Contacts"); oDA.Fill(oDS); if((oDS.Tables[0].Rows.Count > 0) && (pContactID > 0)) { // Should only be one row returned, but go ahead and update all found. foreach(DataRow oRow in oDS.Tables[0].Rows) { if((int)oRow["contact_id"] == pContactID) { oRow.BeginEdit(); oRow["contact_name_last"] = pLastName; oRow["contact_name_first"] = pFirstName; oRow["contact_email"] = pEmail; oRow["contact_phone"] = pPhone; oRow["date_last_modified"] = DateTime.Now; oRow.EndEdit(); } } } else { // Get our next Contact ID. iID = this.DetermineNextID(); DataRow oRow = oDS.Tables[0].NewRow(); oRow["contact_id"] = iID; oRow["contact_name_last"] = pLastName; oRow["contact_name_first"] = pFirstName; oRow["contact_email"] = pEmail; oRow["contact_phone"] = pPhone; oRow["date_created"] = DateTime.Now; oRow["date_last_modified"] = DateTime.Now; oDS.Tables[0].Rows.Add(oRow); } oDA.Update(oDS); } } catch(Exception e) { throw new Exception("Unable to save new contact!\nError: " + e.Message); } return iID; }
/// <summary> /// 释放由当前对象控制的所有资源 /// </summary> /// <param name="disposing">显式调用</param> protected override void Dispose(bool disposing) { if (disposed) { return; } if (disposing) { //释放托管资源 } //释放非托管资源 dataManager = null; _cmdb = null; _dap = null; base.Dispose(disposing); }
//public bool WriteLine(IEnumerable<object> values) //{ // return WriteLine((IEnumerable<string>)values); //} /// <summary> /// Make sure you have a value for every column! /// This is really intentionally not thread safe, do not share this class across threads. /// </summary> public bool WriteLine(IEnumerable <string> values) { if (!IsOpen()) { return(false); } if (_insertCmd == null) { DbCommand cmd = _conn.CreateCommand(); cmd.CommandText = "SELECT * FROM " + _tablename; DbDataAdapter dda = new System.Data.OleDb.OleDbDataAdapter((OleDbCommand)cmd); System.Data.OleDb.OleDbCommandBuilder odcb = new System.Data.OleDb.OleDbCommandBuilder((OleDbDataAdapter)dda); _insertCmd = odcb.GetInsertCommand(); } //double temp = Double.NaN; int i = 0; foreach (string val in values) { //if (Double.TryParse(val, out temp)) //{ // //try to parse out integers, float vars (lat/lon), ids, anything numeric // _insertCmd.Parameters[i].Value = temp; //} //else if (_insertCmd.Parameters[i].SourceColumn.ToLower().EndsWith("id")) //{ // //if the value is empty, and it HAS to be a number... // _insertCmd.Parameters[i].Value = 0; //} //else //{ _insertCmd.Parameters[i].Value = val; //} i++; } int rows = _insertCmd.ExecuteNonQuery(); return(rows == 1); }
public DataSet AtualizaBanco(DataSet Pds, string Psql) { System.Data.OleDb.OleDbConnection cnx = new System.Data.OleDb.OleDbConnection(ConexaoOledb); try { System.Data.OleDb.OleDbDataAdapter Adp = new System.Data.OleDb.OleDbDataAdapter(); Adp.SelectCommand = new System.Data.OleDb.OleDbCommand(Psql, cnx); System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(Adp); cnx.Open(); Adp.Update(Pds); } catch (Exception ex) { throw ex; } finally { cnx.Close(); } return(Pds); }
//1) Delete the recs from FFE with related RX ID //2) Append into FFE from FVS_Potfire public int DbRecsUpdateFfeFromPotFire(string strRxFilePath) { int intRet = 0; string strRxId = this.UtilHelperGetRxString(strRxFilePath); string strSqlSrcPotFire = "SELECT * FROM FVS_PotFire"; string strSqlTgtFfe = "SELECT * FROM FFE WHERE biosum_cond_id = '{0}'"; // TODO: NOT_CALC_YN VALUE? string[] strSqlSrcFilterPotFire = new string[15] { "StandID", "CaseID", " ", "Flame_Len_Severe", "Flame_Len_Mod", "Fire_Type_Severe", "Fire_Type_Mod", "Torch_Index", "Crown_Index", "Canopy_Ht", "Canopy_Density", "Mortality_BA_Severe", "Mortality_BA_Mod", "Mortality_VOL_Severe", "Mortality_VOL_Mod" }; string[] strSqlTgtFilterFfe = new string[15] { "biosum_cond_id", "rx", "post_not_calc_yn", "post_tot_flame_sev", "post_tot_flame_mod", "post_fire_type_sev", "post_fire_type_mod", "post_torch_index", "post_crown_index", "post_canopy_ht", "post_canopy_density", "post_mortality_ba_sev", "post_mortality_ba_mod", "post_mortality_vol_sev", "post_mortality_vol_mod" }; DataTable dtSrcPotFire = null; DataTable dtTgtFfe = null; OLE.OleDbConnection oleConxSrc = null; OLE.OleDbDataAdapter oleAdaptSrc = null; OLE.OleDbConnection oleConxTgt2 = null; OLE.OleDbDataAdapter oleAdaptTgt2 = null; OLE.OleDbCommandBuilder oleCmdBTgt2 = null; try { oleConxSrc = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, string.Format(this.strPathFvsDbFile, strRxId))); oleConxSrc.Open(); oleAdaptSrc = new System.Data.OleDb.OleDbDataAdapter(string.Format(strSqlSrcPotFire, strRxId), oleConxSrc); dtSrcPotFire = new DataTable("FVSPOTFIRE"); oleAdaptSrc.Fill(dtSrcPotFire); oleConxTgt2 = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathMasterDbFile)); oleConxTgt2.Open(); oleAdaptTgt2 = new System.Data.OleDb.OleDbDataAdapter(strSqlTgtFfe, oleConxTgt2); dtTgtFfe = new DataTable("FFE"); oleAdaptTgt2.Fill(dtTgtFfe); oleCmdBTgt2 = new System.Data.OleDb.OleDbCommandBuilder(oleAdaptTgt2); if (dtTgtFfe.Rows.Count > 0) { DataRow[] drs = dtTgtFfe.Select(string.Format(cStrRxFilter, strRxId)); foreach (DataRow dr in drs) { dr.Delete(); } oleAdaptTgt2.Update(dtTgtFfe); dtTgtFfe.AcceptChanges(); } if (dtSrcPotFire.Rows.Count > 1) { dtTgtFfe.BeginLoadData(); for (short i = 0; i < dtSrcPotFire.Rows.Count; i++) { dtTgtFfe.LoadDataRow(dtSrcPotFire.Rows[i].ItemArray, false); dtTgtFfe.Rows[i]["rx"] = strRxId; intRet++; } dtTgtFfe.EndLoadData(); } oleAdaptTgt2.Update(dtTgtFfe); dtTgtFfe.AcceptChanges(); } catch (Exception e) { throw e; } finally { if (oleConxTgt2.State == ConnectionState.Open) { oleConxTgt2.Close(); } oleConxTgt2 = null; } return(0); }
public static bool ExportToXLSX(string sheetToCreate, List <DataRow> selectedRows, System.Data.DataTable origDataTable, string tableName) { bool status = false; System.Data.OleDb.OleDbConnection cn = new OleDbConnection(); try { char Space = ' '; string dest = sheetToCreate; if (File.Exists(dest)) { File.Delete(dest); } sheetToCreate = dest; if (tableName == null) { tableName = string.Empty; } tableName = tableName.Trim().Replace(Space, '_'); if (tableName.Length == 0) { tableName = origDataTable.TableName.Replace(Space, '_'); } if (tableName.Length == 0) { tableName = "NoTableName"; } if (tableName.Length > 30) { tableName = tableName.Substring(0, 30); } //Excel names are less than 31 chars string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] ("; Dictionary <string, string> colNames = new Dictionary <string, string>(); foreach (DataColumn dc in origDataTable.Columns) { //Cause the query to name each of the columns to be created. string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#'); string origColName = dc.ColumnName; colNames.Add(modifiedcolName, origColName); queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,"; } queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")"; //adds the closing parentheses to the query string if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls")) { //use Excel 2007 for large sheets. sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx"; } string strCn = string.Empty; string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower(); if (ext == ".xls") { strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'"; } if (ext == ".xlsx") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' "; } if (ext == ".xlsb") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' "; } if (ext == ".xlsm") { strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' "; } cn = new System.Data.OleDb.OleDbConnection(strCn); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn); cn.Open(); cmd.ExecuteNonQuery(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn); System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da); //creates the INSERT INTO command cb.QuotePrefix = "["; cb.QuoteSuffix = "]"; cmd = cb.GetInsertCommand(); //gets a hold of the INSERT INTO command. foreach (DataRow row in selectedRows) { foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters) { param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]]; } cmd.ExecuteNonQuery(); //INSERT INTO command. } cn.Close(); cn.Dispose(); da.Dispose(); GC.Collect(); GC.WaitForPendingFinalizers(); status = true; } catch (Exception ex) { status = false; if (cn.State == ConnectionState.Open) { cn.Close(); } } return(status); }
/// <summary> /// 界面加载数据OLEDB /// </summary> /// <param name="ConnStr">连接字符串</param> /// <param name="strSQL">SQL语句</param> /// <param name="strTableName">DataTable表名</param> public static SD.DataTable GetOLEDBData(string ConnStr, string strSQL, string strTableName, bool isTable) { SD.DataTable dt = new SD.DataTable(strTableName); try { using (OleDbsqlconn = new SDOL.OleDbConnection(ConnStr)) { //sqlconn.Open(); using (OleDbsqlcmd = new SDOL.OleDbCommand(strSQL, OleDbsqlconn)) { //if (sqlcmd == null) //{ // using (dt = new SD.DataTable(strTableName)) // { // return dt; // } //} using (OleDbsqladp = new SDOL.OleDbDataAdapter(OleDbsqlcmd)) { if (isTable) { using (OleDbsqlcmdbd = new SDOL.OleDbCommandBuilder(OleDbsqladp)) { OleDbsqlcmdbd.ConflictOption = SD.ConflictOption.CompareAllSearchableValues; OleDbsqladp.InsertCommand = OleDbsqlcmdbd.GetInsertCommand(); OleDbsqladp.UpdateCommand = OleDbsqlcmdbd.GetUpdateCommand(); OleDbsqladp.DeleteCommand = OleDbsqlcmdbd.GetDeleteCommand(); OleDbsqladp.Fill(dt); return(dt); } } else { OleDbsqladp.Fill(dt); return(dt); } } } } } catch (Exception ex) { throw ex; } finally { if (sqlconn != null) { if (sqlconn.State != SD.ConnectionState.Closed) { sqlconn.Close(); } sqlconn.Dispose(); } } }
//TODO: provide the UI interface CheckBox to overwrite existing differing Diam and Species Group from Biosum and FVS //Otherwise, make a copy? and or not do anything? //TODO: Change back to Private public void DbRecsTdgTsg2ProcDbCopy(bool overwrite) { if (!overwrite) { return; //can't do anything now if user don't want replace table yet. } //bool blnRowDiff=false; //15-MAY-2017: These tables were renamed and moved but this code is obsolete so it was not updated string strSqlTdg = "SELECT * FROM TREE_DIAM_GROUPS"; string strSqlTsg = "SELECT * FROM TREE_SPECIES_GROUPS"; string strSqlDelTdg = "DELETE FROM TREE_DIAM_GROUPS"; string strSqlDelTsg = "DELETE FROM TREE_SPECIES_GROUPS"; DataTable dtTdg = new DataTable("TDG"); DataTable dtTsg = new DataTable("TSG");; DataTable dtTdgTgt = new DataTable("TDGTGT"); DataTable dtTsgTgt = new DataTable("TSGTGT"); DataSet dsMaster = new DataSet("FVS_TREE"); dsMaster.Tables.Add(dtTdg); dsMaster.Tables.Add(dtTsg); OLE.OleDbConnection oleConx2Master = null; OLE.OleDbDataAdapter oleAdaptTdg = null; OLE.OleDbDataAdapter oleAdaptTsg = null; OLE.OleDbConnection oleConx2Proc = null; OLE.OleDbDataAdapter oleAdaptTdgTgt = null; OLE.OleDbDataAdapter oleAdaptTsgTgt = null; OLE.OleDbCommandBuilder oleCmdBTdgTgt = null; OLE.OleDbCommandBuilder oleCmdBTsgTgt = null; try { oleConx2Master = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathMasterDbFile)); oleConx2Master.Open(); oleAdaptTdg = new System.Data.OleDb.OleDbDataAdapter(strSqlTdg, oleConx2Master); oleAdaptTdg.Fill(dtTdg); oleAdaptTsg = new System.Data.OleDb.OleDbDataAdapter(strSqlTsg, oleConx2Master); oleAdaptTsg.Fill(dtTsg); //TODO: Made assumption that the table structures exist ... need to create the Table //If 2 tables exists in strPathProcDbFile, then delete records and import them from here. oleConx2Proc = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathProcDbFile)); oleConx2Proc.Open(); oleAdaptTdgTgt = new System.Data.OleDb.OleDbDataAdapter(strSqlTdg, oleConx2Proc); oleAdaptTsgTgt = new System.Data.OleDb.OleDbDataAdapter(strSqlTsg, oleConx2Proc); oleAdaptTdgTgt.Fill(dtTdgTgt); oleAdaptTsgTgt.Fill(dtTsgTgt); oleCmdBTdgTgt = new System.Data.OleDb.OleDbCommandBuilder(oleAdaptTdgTgt); oleCmdBTsgTgt = new System.Data.OleDb.OleDbCommandBuilder(oleAdaptTsgTgt); int tdgMatch = 0, tsgMatch = 0; foreach (DataRow dr in dtTdg.Rows) { foreach (DataRow dr2 in dtTdgTgt.Rows) { if (dr.ItemArray.ToString().CompareTo(dr2.ItemArray.ToString()) == 0) { tdgMatch++; } } } foreach (DataRow dr in dtTsg.Rows) { foreach (DataRow dr2 in dtTsgTgt.Rows) { if (dr.ItemArray.ToString().CompareTo(dr2.ItemArray.ToString()) == 0) { tsgMatch++; } } } //flag value compare difference in both row and value if (tdgMatch != dtTdg.Rows.Count || tsgMatch != dtTsg.Rows.Count || dtTdg.Rows.Count != dtTdgTgt.Rows.Count || dtTsg.Rows.Count != dtTsgTgt.Rows.Count) { // blnRowDiff=true; if (dtTdgTgt.Rows.Count > 0 && overwrite) { OLE.OleDbCommand oCmd = oleConx2Proc.CreateCommand(); oCmd.CommandText = strSqlDelTdg; oCmd.ExecuteNonQuery(); dtTdgTgt.Rows.Clear(); dtTdgTgt.AcceptChanges(); } } if (dtTsgTgt.Rows.Count > 0 && overwrite) { OLE.OleDbCommand oCmd = oleConx2Proc.CreateCommand(); oCmd.CommandText = strSqlDelTsg; oCmd.ExecuteNonQuery(); dtTsgTgt.Rows.Clear(); dtTsgTgt.AcceptChanges(); } dtTdgTgt.BeginLoadData(); dtTsgTgt.BeginLoadData(); foreach (DataRow dr in dtTdg.Rows) { dtTdgTgt.LoadDataRow(dr.ItemArray, false); } foreach (DataRow dr in dtTsg.Rows) { dtTsgTgt.LoadDataRow(dr.ItemArray, false); } dtTdgTgt.EndLoadData(); dtTsgTgt.EndLoadData(); oleAdaptTdgTgt.Update(dtTdgTgt); dtTdgTgt.AcceptChanges(); oleAdaptTsgTgt.Update(dtTsgTgt); dtTsgTgt.AcceptChanges(); } catch (OLE.OleDbException oe) { throw oe; } finally { if (oleConx2Master.State == ConnectionState.Open) { oleConx2Master.Close(); } oleConx2Master = null; oleAdaptTdg = null; oleAdaptTsg = null; if (oleConx2Proc.State == ConnectionState.Open) { oleConx2Proc.Close(); } oleAdaptTdgTgt = null; oleAdaptTsgTgt = null; } }
private void ExportAccess() { bool IsTable = false; OleDbConnection conn = new OleDbConnection(); string connectionstring = m_pTaskData.DataSource; //判断是否为新建表 string tName = m_pTaskData.DataTableName; conn.ConnectionString = connectionstring; try { conn.Open(); } catch (System.Exception ex) { if (e_PublishLog != null) { WriteLog(ex.Message); e_PublishLog(this, new PublishLogEventArgs(this.TaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务:" + this.TaskData.TaskName + "发布Access失败,错误信息为:" + ex.Message + "\n")); } return; } System.Data.DataTable tb = conn.GetSchema("Tables"); foreach (DataRow r in tb.Rows) { if (r[3].ToString() == "TABLE") { if (r[2].ToString() == tName) { IsTable = true; break; } } } if (IsTable == false) { //需要建立新表,建立新表的时候采用ado.net新建行的方式进行数据增加 string CreateTablesql = getCreateTablesql(cGlobalParas.DatabaseType.Access, ""); OleDbCommand com = new OleDbCommand(); com.Connection = conn; com.CommandText = CreateTablesql; com.CommandType = CommandType.Text; try { int result = com.ExecuteNonQuery(); } catch (System.Data.OleDb.OleDbException ex) { if (ex.ErrorCode != -2147217900) { if (e_PublishLog != null) { WriteLog(ex.Message); e_PublishLog(this, new PublishLogEventArgs(this.TaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务:" + this.TaskData.TaskName + "发布MySql失败,错误信息为:" + ex.Message + "\n")); } throw ex; } } try { System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + tName, conn); System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds, m_pTaskData.DataTableName); for (int i = 0; i < m_pTaskData.PublishData.Rows.Count; i++) { DataRow dr = ds.Tables[0].NewRow(); for (int j = 0; j < m_pTaskData.PublishData.Columns.Count; j++) { dr[j] = m_pTaskData.PublishData.Rows[i][j].ToString(); } ds.Tables[0].Rows.Add(dr); } int m = da.Update(ds.Tables[0]); } catch (System.Exception ex) { if (e_PublishLog != null) { WriteLog(ex.Message); e_PublishLog(this, new PublishLogEventArgs(this.TaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务:" + this.TaskData.TaskName + "发布Access失败,错误信息为:" + ex.Message + "\n")); } return; } } else { try { //无需建立新表,需要采用sql语句的方式进行,但需要替换sql语句中的内容 System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand(); cm.Connection = conn; cm.CommandType = CommandType.Text; //开始拼sql语句 string sql = ""; for (int i = 0; i < m_pTaskData.PublishData.Rows.Count; i++) { sql = m_pTaskData.InsertSql; for (int j = 0; j < m_pTaskData.PublishData.Columns.Count; j++) { string strPara = "{" + m_pTaskData.PublishData.Columns[j].ColumnName + "}"; //string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString(); string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString().Replace("\"", "\"\""); sql = sql.Replace(strPara, strParaValue); } cm.CommandText = sql; cm.ExecuteNonQuery(); } } catch (System.Exception ex) { if (e_PublishLog != null) { WriteLog(ex.Message); e_PublishLog(this, new PublishLogEventArgs(this.TaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务:" + this.TaskData.TaskName + "发布Access失败,错误信息为:" + ex.Message + "\n")); } return; } } conn.Close(); }
//TODO: change back to Private public int DbRecsFinalizeFvsTree(string strRxFilePath) { int intRet = 0; int intExe = 0; string strRxId = this.UtilHelperGetRxString(strRxFilePath); string strSqlSrc = "SELECT * FROM FVS_CUTLIST"; string strSqlTgt = "SELECT * FROM FVS_TREE"; //N,N,T string[] strSqlSrcFilterFvsCutlist = new string[27] { "Id", "StandID", "getRxId", "Year", "PrdLen", "TreeId", "Species", "TreeVal", "SSCD", "PtIndex", "TPA", "MortPA", "DBH", "DG", "Ht", "HtG", "PctCr", "CrWidth", "MistCD", "BAPctile", "PtBAL", "TCuFt", "MCuFt", "BdFt", "MDefect", "BDefect", "TruncHt" }; //N,T,T string[] strSqlTgtFilterFvsTree = new string[27] { "Id", "biosum_cond_id", "rx", "Year", "PrdLen", "Tid", "Species", "TreeValue", "SSCD", "PtIndex", "TPA", "MortPA", "DBH", "DG", "Ht", "HtG", "PctCr", "CrWidth", "MistCD", "BAPctile", "PtBAL", "TCuFt", "MCuFt", "BdFt", "MDefect", "BDefect", "TruncHt" }; DataTable dtTgtFvsTree = null; DataTable dtSrcFvsCutList = null; OLE.OleDbConnection oleConxTgt = null; OLE.OleDbDataAdapter oleAdaptTgt = null; OLE.OleDbCommandBuilder oleCmdBTgt = null; OLE.OleDbCommand oCmd = null; OLE.OleDbConnection oleConxSrc = null; OLE.OleDbDataAdapter oleAdaptSrc = null; try { oleConxTgt = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, this.strPathProcDbFile)); oleConxTgt.Open(); oleAdaptTgt = new System.Data.OleDb.OleDbDataAdapter(strSqlTgt, oleConxTgt); dtTgtFvsTree = new DataTable("FVS_Tree"); oleCmdBTgt = new System.Data.OleDb.OleDbCommandBuilder(oleAdaptTgt); oleAdaptTgt.Fill(dtTgtFvsTree); if (dtTgtFvsTree.Rows.Count > 0) { DataRow[] drDel = dtTgtFvsTree.Select(string.Format(cStrRxFilter, strRxId)); if (drDel.Length > 0) { foreach (DataRow dr in drDel) { dr.Delete(); //delete old Records base on RX ID } oleAdaptTgt.Update(dtTgtFvsTree); dtTgtFvsTree.AcceptChanges(); } } oleConxSrc = new System.Data.OleDb.OleDbConnection(string.Format(this.strDbConx, string.Format(this.strPathFvsDbFile, strRxId))); oleConxSrc.Open(); oleAdaptSrc = new System.Data.OleDb.OleDbDataAdapter(strSqlSrc, oleConxSrc); dtSrcFvsCutList = new DataTable("FVS_CUTLIST"); oleAdaptSrc.Fill(dtSrcFvsCutList); if (dtSrcFvsCutList.Rows.Count > 1) { dtTgtFvsTree.BeginLoadData(); foreach (DataRow dr in dtSrcFvsCutList.Rows) { dtTgtFvsTree.LoadDataRow(dr.ItemArray, false); DataRow drCur = dtTgtFvsTree.Rows[intRet]; drCur.BeginEdit(); drCur["rx"] = strRxId; drCur["Tid"] = dr["TreeId"].ToString().Trim(); //Data Quality Issue drCur.EndEdit(); intRet++; } dtTgtFvsTree.EndLoadData(); dtTgtFvsTree.AcceptChanges(); string[] strArySql = this.DbHelperStrSqlInserts(dtTgtFvsTree); //See Documentation oCmd = oleConxTgt.CreateCommand(); for (short x = 0; x < strArySql.Length; x++) { oCmd.CommandText = strArySql[x].Trim(); oCmd.CommandType = CommandType.Text; intExe = oCmd.ExecuteNonQuery(); } } } catch (OLE.OleDbException oe) { throw oe; } catch (Exception e) { throw e; } finally { if (oleConxSrc.State == ConnectionState.Open) { oleConxSrc.Close(); } if (oleConxTgt.State == ConnectionState.Open) { oleConxTgt.Close(); } oleConxSrc = null; oleConxTgt = null; } return(intExe); }