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);
}
