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;

        }
    }
Beispiel #2
0
        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;
        }
Beispiel #3
0
        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();
        }
Beispiel #4
0
        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();
        }
Beispiel #5
0
        /// <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);
            }
        }
Beispiel #6
0
        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();
            }
        }
Beispiel #7
0
        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);
            }
        }
Beispiel #8
0
        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");
        }
Beispiel #11
0
        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");
        }
Beispiel #13
0
        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 !!! ");

            }
        }
Beispiel #14
0
        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.");
            }
        }
Beispiel #15
0
        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);
            }
        }
Beispiel #18
0
        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;
        }
Beispiel #19
0
     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;
         }
 
     }
Beispiel #20
0
        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();
            }
        }
Beispiel #22
0
        /// <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;
        }
Beispiel #23
0
 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);
 }
Beispiel #24
0
  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;
 }
Beispiel #26
0
        //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);
        }
Beispiel #27
0
 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;
 }
Beispiel #28
0
 /// <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);
 }
Beispiel #29
0
 /// <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);
 }
Beispiel #30
0
        /// <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;
        }
Beispiel #31
0
 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();
 }
Beispiel #32
0
		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);
		}
Beispiel #33
0
        /// <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;
        }
Beispiel #34
0
 /// <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);
 }
Beispiel #35
0
        //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);
        }
Beispiel #36
0
        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);
        }
Beispiel #37
0
        //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);
    }
Beispiel #39
0
        /// <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();
                }
            }
        }
Beispiel #40
0
        //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;
            }
        }
Beispiel #41
0
        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();
        }
Beispiel #42
0
        //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);
        }