Inheritance: System.Data.Common.DbDataAdapter, IDbDataAdapter
示例#1
0
        private void DisplayStudents_Load(object sender, EventArgs e)
        {
            connection = new OleDbConnection();
            command = new OleDbCommand();
            adapter = new OleDbDataAdapter();
            dataset = new DataSet();

            connection.ConnectionString =
                @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Students.accdb;" +
                "Persist Security Info=False";

            command.Connection = connection;
            command.CommandText = "SELECT * FROM Student";

            adapter.SelectCommand = command;

            try
            {
                adapter.Fill(dataset, "Student");
            }
            catch (OleDbException)
            {
                MessageBox.Show("Error occured while connecting to database.");
                Application.Exit();
            }

            PopulateFields(0);
        }
        public void ReadExcelSheet(string fileName, string sheetName, Action<DataTableReader> actionForEachRow)
        {
            var connectionString = string.Format(ExcelSettings.Default.ExcelConnectionString, fileName);

            using (var excelConnection = new OleDbConnection(connectionString))
            {
                excelConnection.Open();

                if (sheetName == null)
                {
                    var excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (excelSchema != null)
                    {
                        sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
                    }
                }

                var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection);

                using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand))
                {
                    var dataSet = new DataSet();
                    oleDbDataAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            actionForEachRow(reader);
                        }
                    }
                }
            }
        }
示例#3
0
        private void Autocomplete()
        {
            try
            {
                con = new OleDbConnection(cs);
                con.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT distinct ProductName FROM product", con);
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds, "Product");
                AutoCompleteStringCollection col = new AutoCompleteStringCollection();
                int i = 0;
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    col.Add(ds.Tables[0].Rows[i]["productname"].ToString());

                }
                txtProductName.AutoCompleteSource = AutoCompleteSource.CustomSource;
                txtProductName.AutoCompleteCustomSource = col;
                txtProductName.AutoCompleteMode = AutoCompleteMode.Suggest;

                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
示例#4
0
        public void loads(string SlnoAbbreviation)
        {
            string path = System.IO.Path.GetFullPath(@SlnoAbbreviation);

            oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
              path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;

            dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {

            }

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            for (int j = 0; j < excelSheets.Length; j++)
            {
                drfile.Items.Add(excelSheets[j]);
            }
            drfile.Items.Insert(0, new ListItem("~Select~", "0"));
        }
            public static void StateData()
            {
                var count = 0;

                var datafile = AppDomain.CurrentDomain.BaseDirectory + @"data\StateData.xlsx";

                var connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + datafile + @"; Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                var adapter = new OleDbDataAdapter("select * from [states$]", connStr);
                var ds = new DataSet();
                adapter.Fill(ds, "states");

                foreach (DataRow r in ds.Tables["states"].Rows)
                {
                    var code = r.ItemArray[0].ToString();
                    var value = r.ItemArray[1].ToString();
                    var capitol = r.ItemArray[2].ToString();
                    var population = r.ItemArray[3].ToString();
                    var squaremiles = r.ItemArray[4].ToString();

                    var entity = new Entity("States", code, value, SequenceType.ALPHA_ASC);
                    entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Capitol", capitol));
                    entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Population", population));
                    entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Square Miles", squaremiles));
                    entity.link = "US";

                    var json = ModelManager.ModelToJson<Entity>(entity);
                    var filename = AppDomain.CurrentDomain.BaseDirectory + @"data\state" + count.ToString() + ".json"; 
                    System.IO.File.WriteAllText(filename, json);

                    Console.WriteLine(count + " Entity: " + entity.code + " | " + entity.codevalue + " | " + capitol + " | " + population + " | " + squaremiles);

                    count++;
                }
            }
示例#6
0
        public DataTable ExcelToDataTable(string pathName, string sheetName)
        {
            DataTable tbContainer = new DataTable();
            string strConn = string.Empty;
            if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; }
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists) { throw new Exception("文件不存在"); }
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;

                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;

                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }

            //链接Excel
            OleDbConnection cnnxls = new OleDbConnection(strConn);

            //读取Excel里面有 表Sheet1
            OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
            DataSet ds = new DataSet();

            //将Excel里面有表内容装载到内存表中!
            oda.Fill(tbContainer);
            return tbContainer;
        }
		public static DbDataAdapter CreateDataAdapter(DbProviderType DbProviderType, IDbCommand command, IDbConnection connection)
		{
			switch (DbProviderType)
			{
				case DbProviderType.SQLSERVER:
					SqlDataAdapter sqlda = new SqlDataAdapter();
					sqlda.SelectCommand = (SqlCommand) command;
					command.Connection = connection;
					return sqlda;
				case DbProviderType.ORACLE:
					OracleDataAdapter orada = new OracleDataAdapter();
					orada.SelectCommand = (OracleCommand) command;
					command.Connection = connection;
					return orada;
//				case DbProviderType.MYSQL:
//					MySqlDataAdapter mysqlda = new MySqlDataAdapter();
//					mysqlda.SelectCommand = (MySqlCommand) command;
//					command.Connection = connection;
//					return mysqlda;
				default:
					OleDbDataAdapter oleda = new OleDbDataAdapter();
					oleda.SelectCommand = (OleDbCommand) command;
					command.Connection = connection;
					return oleda;
			}
		}
示例#8
0
        private static void PrintSpreadsheet(OleDbConnectionStringBuilder connestionString)
        {
            DataSet sheet1 = new DataSet();
            using (OleDbConnection connection = new OleDbConnection(connestionString.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sheet1$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.Fill(sheet1);
                }
                connection.Close();
            }
            
            var table = sheet1.Tables[0];

            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn column in table.Columns)
                {
                    Console.Write("{0, -20} ", row[column]);
                }

                Console.WriteLine();
            }
        }
示例#9
0
    private DataTable DadosExcel(string Arquivo)
    {
        Char   aspas   = '******';
        string Conexao = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                         "Data Source=" + Arquivo + ";" +
                         "Extended Properties=" + aspas + "Excel 8.0;HDR=YES" + aspas;

        System.Data.OleDb.OleDbConnection Cn = new System.Data.OleDb.OleDbConnection();
        Cn.ConnectionString = Conexao;
        Cn.Open();
        object[]  Restricoes = { null, null, null, "TABLE" };
        DataTable DTSchema   = Cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Restricoes);

        if (DTSchema.Rows.Count > 0)
        {
            string Sheet = DTSchema.Rows[0]["TABLE_NAME"].ToString();
            System.Data.OleDb.OleDbCommand Comando = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + Sheet + "]", Cn);
            DataTable Dados = new DataTable();
            System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter(Comando);
            DA.Fill(Dados);
            Cn.Close();
            return(Dados);
        }
        return(null);
    }
示例#10
0
        //Create an Excel file with 2 columns: name and score:
        //Write a program that reads your MS Excel file through
        //the OLE DB data provider and displays the name and score row by row.

        static void Main()
        {
            OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=D:\Telerik\DataBases\HW\ADONET\06. ReadExcel\Table.xlsx;Extended Properties=""Excel 12.0 XML;HDR=Yes""");
            OleDbCommand myCommand = new OleDbCommand("select * from [Sheet1$]", dbConn);
            dbConn.Open();
            //First way
            //using (dbConn) - I think it is better to use dbConn in using clause, but for the demo issues i dont use using.
            //{
            OleDbDataReader reader = myCommand.ExecuteReader();

            while (reader.Read())
            {
                string name = (string)reader["Name"];
                double score = (double)reader["Score"];
                Console.WriteLine("{0} - score: {1}", name, score);
            }
            //}

            dbConn.Close();
            //Second way
            dbConn.Open();
            Console.WriteLine();
            Console.WriteLine("Second Way");
            Console.WriteLine("----------");
            DataTable dataSet = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", dbConn);
            adapter.Fill(dataSet);

            foreach (DataRow item in dataSet.Rows)
            {
                Console.WriteLine("{0}|{1}", item.ItemArray[0], item.ItemArray[1]);
            }
            dbConn.Close();
        }
示例#11
0
    public DataSet GetDataSet(string filepath, string excelFileExtension)
    {
        try {
            System.Data.OleDb.OleDbConnection oledbcon = null;
            string strConn = string.Empty;
            switch (excelFileExtension.Trim()) {
                case "xls":
                    oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;MaxScanRows=0;\"");
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
                    break;
                case "xlsx":
                    oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
                   + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'");
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
                   + ";Extended Properties=Excel 12.0;";
                    break;
            }

            //excel
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            string sheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
            System.Data.OleDb.OleDbDataAdapter oledbAdaptor = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", oledbcon);
            //select
            DataSet ds = new DataSet();
            oledbAdaptor.Fill(ds);
            oledbcon.Close();
            return ds;
        } catch (Exception ex) {
            throw ex;
        }
    }
示例#12
0
    public static string fnImportExcel(string sFileName, string sSql, string[] sColumnName, HttpContext context)
    {
        string sPath = context.Server.MapPath("/\\Import\\" + sFileName);

        if (File.Exists(sPath) == false)
        {
            return("錯誤訊息:找無檔案。");
        }

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath +
                         ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";

        System.Data.OleDb.OleDbDataAdapter myCommeand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
        DataSet myDataSet = new DataSet();

        try
        {
            myCommeand.Fill(myDataSet, "Excelinfo");
        }
        catch (Exception ex)
        {
            return("活頁步名稱請改Sheet1");
        }
        DataTable dtData = myDataSet.Tables["Excelinfo"].DefaultView.ToTable();

        return(fnInsertSql(dtData, sSql, sColumnName));
    }
示例#13
0
    public void updateProductPrice(ProductInBag Product)
    {
        OleDbConnection myConn = new OleDbConnection(Connstring.getConnectionString());
        OleDbCommand myCmd;
        OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
        try
        {
            myCmd = new OleDbCommand("UpdateUnitPrice", myConn);
            myCmd.CommandType = CommandType.StoredProcedure;
            OleDbParameter objParam;

            objParam = myCmd.Parameters.Add("@UnitPrice", OleDbType.Decimal);
            objParam.Direction = ParameterDirection.Input;
            objParam.Value = Product.Price;

            objParam = myCmd.Parameters.Add("@ProductID", OleDbType.Integer);
            objParam.Direction = ParameterDirection.Input;
            objParam.Value = Product.ProdID;

            myConn.Open();
            myCmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            myConn.Close();
        }
    }
示例#14
0
        private void cbxclub_DropDownOpened(object sender, EventArgs e)
        {
            try
            {
                if (cbxseason.SelectedItem == null || cbxzone.SelectedItem == null || cbxdivision.SelectedItem == null)
                {
                    MessageBox.Show("Select Season, Zone, Division");
                }

                string strRetrieve = "";
                strRetrieve = "select * from clubdetails";
                //  cbxclub.Items.Clear();
                OleDbDataAdapter adpt = new OleDbDataAdapter(strRetrieve, Database.getConnection());
                DataTable dt = new DataTable();
                adpt.Fill(dt);

                foreach (DataRowView dr in dt.DefaultView)
                {
                    //   List<Team> lstFilter = Database.GetEntityList<Team>( false, false,false,Database.getConnection(),RecordStatus true);
                    //cbxclub.Items.Add(dr["ClubName"]);
                    //cbxteam.Items.Refresh();

                    if (!cbxclub.Items.Contains(dr["ClubName"]))// For remove list duplicacy
                    {
                        cbxclub.Items.Add(dr["ClubName"]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
示例#15
0
        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);
            }
        }
示例#16
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;
        }
        public ICollection<Empresa> ConsultarEmpresa(string RUC, string nombreComercial)
        {
            OleDbConnection obj_Conexion = new OleDbConnection();
            OleDbCommand obj_Comando = new OleDbCommand();
            OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
            DataSet obj_Data = new DataSet();
            obj_Conexion.ConnectionString = "Server=VIRTUALXP-50904\\SQL2008; Provider=SQLOLEDB; User ID=sa; Initial Catalog=Ventas; password=royal2008;";
            obj_Conexion.Open();
            obj_Comando.Connection = obj_Conexion;
            if (RUC == null && nombreComercial == null)
                obj_Comando.CommandText = "Select * From Contribuyentes";
            else if (RUC != null && nombreComercial == null)
                obj_Comando.CommandText = "Select * From Contribuyentes Where Ruc Like '%" + RUC + "%'";
            else
                obj_Comando.CommandText = "Select * From Contribuyentes Where RazonSocial Like '%" + nombreComercial + "%'";
            obj_Comando.CommandType = CommandType.Text;
            obj_Adapter.SelectCommand = obj_Comando;
            obj_Adapter.Fill(obj_Data);
            obj_Conexion.Close();

            List<Empresa> empresas = new List<Empresa>();
            Empresa empresa;
            foreach (var dr_Fila in obj_Data.Tables[0].Rows)
            {
                DataRow fila = (DataRow)dr_Fila;
                empresa = new Empresa();
                empresa.RUC = fila[0].ToString();
                empresa.nombrecomercial = fila[1].ToString();
                empresa.Estado = fila[3].ToString();
                empresas.Add(empresa);
            }

            return empresas;
        }
示例#18
0
        private void FormFirma_Load(object sender, EventArgs e)
        {
            PutBaze = System.IO.File.ReadAllText(Application.StartupPath + "\\Ponuda\\BazaPonuda.txt");
            string PutKriterij =System.IO.File.ReadAllText(@"privPonuda.txt");

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+PutBaze;

            //----------SQL instrukcija-----------\\

            string sql = "SELECT * FROM sve WHERE Firma LIKE '" + PutKriterij + "%'";

            //klase za povezivanje na ACCESS bazu podataka//
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
            //\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\

             ds = new DataSet();         //kreira noi objekt(kopiju) DataSet()-a
            conn.Open();                        //otvara spoj s bazom podataka

            adapter.Fill(ds, "sve");       //puni se DataSet s podacima tabele t_razred
            conn.Close();                       //zatvara se baza podataka

            //nakon zatvaanja BP imamo odgovarajuće podatke u ds objektu ( DataSet() )
            dataGridView2.DataSource = ds;      //upisivanje podataka id ds u dataGridView2
            dataGridView2.DataMember = "sve";
            ukupnaCijenaDataGridViewTextBoxColumn.DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("de-DE");

            this.dataGridView2.Sort(firmaDataGridViewTextBoxColumn, ListSortDirection.Ascending);
            System.IO.File.Delete(@"privPonuda.txt");
        }
示例#19
0
    DataSet ReadCSV(string fileNameToUpload)
    {
        DataSet ds = new DataSet();

        System.Data.OleDb.OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("UploadedOrders") + ";Extended Properties=Text;");

        try
        {
            System.Data.OleDb.OleDbCommand     excelCommand = new System.Data.OleDb.OleDbCommand(@"SELECT * FROM " + fileNameToUpload, excelConnection);
            System.Data.OleDb.OleDbDataAdapter excelAdapter = new System.Data.OleDb.OleDbDataAdapter(excelCommand);
            excelConnection.Open();
            excelAdapter.Fill(ds);
            excelConnection.Close();
        }
        catch (Exception objException)
        {
            if (objException != null && objException.Message != null)
            {
                string script = "alertify.alert('" + objException.Message + "');";
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", script, true);
            }

            CSLOrderingARCBAL.LinqToSqlDataContext db;
            db = new CSLOrderingARCBAL.LinqToSqlDataContext();
            db.USP_SaveErrorDetails(Request.Url.ToString(), "ReadCSV", Convert.ToString(objException.Message), Convert.ToString(objException.InnerException), Convert.ToString(objException.StackTrace), "", HttpContext.Current.Request.UserHostAddress, false, Convert.ToString(HttpContext.Current.Session[enumSessions.User_Id.ToString()]));
        }
        finally
        {
            excelConnection.Close();
        }
        return(ds);
    }
示例#20
0
    protected void btTaiLen_Click1(object sender, EventArgs e)
    {
        if (Request.Cookies["ADMIN"] == null) return;
        if (FileUpload1.HasFile)
            try
            {
                string path = Server.MapPath(@"EXCEL\") + "Question_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xlsx";
                FileUpload1.SaveAs(path);
                string txt = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;", path);
                OleDbConnection con = new OleDbConnection(txt);
                con.Open();
                txt = "Select * FROM [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(txt, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                con.Close();

                gvCauHoi.DataSource = ds;
                gvCauHoi.DataBind();
                string temp;
                foreach (GridViewRow dr in gvCauHoi.Rows)
                {
                    temp = Server.HtmlDecode(dr.Cells[0].Text);
                    if (temp.StartsWith("#"))
                        dr.CssClass = "Q1";
                    else if (temp.StartsWith("$")) // Không đảo phương án trả lời
                        dr.CssClass = "Q2";
                    else if (temp.StartsWith("*")) // Phương án trả lời đúng
                        dr.CssClass = "A1";
                }
            }
            catch (Exception ex) { lbError.Text = ex.Message; }
    }
 private void btnCompararStock_Click(object sender, EventArgs e)
 {
     Cursor.Current = Cursors.WaitCursor;
     string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Benja\\Desktop\\libro1.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";";
     OleDbConnection con = new OleDbConnection(Connection);
     System.Data.DataTable tblOriginal = new System.Data.DataTable();
     OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [original$]", con);
     myCommand.Fill(tblOriginal);
     System.Data.DataTable tblModificado = new System.Data.DataTable();
     myCommand = new OleDbDataAdapter("select * from [modificado$]", con);
     myCommand.Fill(tblModificado);
     int contador = 0;
     ArrayList miArray = new ArrayList();
     foreach (DataRow rowOriginal in tblOriginal.Rows)
     {
         string articulo = rowOriginal["Articulo"].ToString();
         DataRow[] rowfound = tblModificado.Select("Articulo LIKE '" + articulo + "'");
         if (rowfound.Count() == 0)
         {
             miArray.Add(articulo);
         }
         foreach (DataRow rowModificado in rowfound)
         {
             string originalMakro = rowOriginal["Makro"].ToString();
             string modificadoMakro = rowModificado["Makro"].ToString();
             string originalJesus = rowOriginal["Jesus Maria"].ToString();
             string modificadoJesus = rowModificado["Jesus Maria"].ToString();
             if (originalMakro != modificadoMakro || originalJesus != modificadoJesus)
                 MessageBox.Show(rowOriginal["Articulo"].ToString());
         }
         contador++;
     }
     MessageBox.Show(contador.ToString());
     Cursor.Current = Cursors.Arrow;
 }
示例#22
0
 /// <summary>
 /// 执行查询语句,返回DataSet
 /// </summary>
 /// <param name="SQLString">查询语句</param>
 /// <returns>DataSet</returns>
 public override DataSet ExecuteDataSet(string SQLString, params IDataParameter[] cmdParms)
 {
     using (OleDbConnection connection = new OleDbConnection(connectionString))
     {
         OleDbCommand cmd = new OleDbCommand();
         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
         using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
         {
             DataSet ds = new DataSet();
             try
             {
                 da.Fill(ds, "ds");
                 cmd.Parameters.Clear();
             }
             catch (OleDbException e)
             {
                 //LogManage.OutputErrLog(e, new Object[] { SQLString, cmdParms });
                 throw;
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
             return ds;
         }
     }
 }
示例#23
0
        private void button1_Click(object sender, EventArgs e)
        {
            try
                {
                // Mo hop thoai Dialog de tim den file Excel
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Title = "Chon file chua danh sach so dien thoai";
                ofd.Filter = "Các file Excel (*.xls) và *.txt|*.xls|*.txt|";
                ofd.ShowDialog();

                // Khai bao chuoi ket noi csdl
                string ConnectionString;

                ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; " + "data source='" + ofd.FileName + "'; ";


                // Tao doi tuong ket noi
                OleDbConnection cn = new OleDbConnection(ConnectionString);
                cn.Open();

                // Tao doi tuong thuc thi cau lenh
                OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$] ", cn);
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds, "Danhsach");

                // gan du lieu vao dieu khien
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Danhsach";

                }
                catch (Exception ex) { MessageBox.Show("Khong ket noi duoc CSDL: " + ex.Message); }

                }
示例#24
0
        public void validateStaff()
        {
            OleDbConnection dataConnection = new OleDbConnection();
            DataTable dt = new DataTable();
            dataConnection.ConnectionString = dataConnection.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\database\\database.accdb");
            string sql = "SELECT LAST(StaffID)FROM Registration";
            OleDbDataAdapter adapt = new OleDbDataAdapter(sql, dataConnection);
            adapt.Fill(dt);
            string StaffID = dt.Rows[0][0].ToString();

            if (StaffID == "")
            {
                MessageBoxResult linkoLogIn = MessageBox.Show("No staff on call. Please log in to proceed!", "Error", MessageBoxButton.OKCancel, MessageBoxImage.Error);
                if (linkoLogIn == MessageBoxResult.OK)
                {
                    Employee_login loginScreen = new Employee_login();
                    loginScreen.Show();
                }
            }
            else
            {
                BedsideMonitoringStation mainInterface = new BedsideMonitoringStation();
                mainInterface.Show();
            }
        }
 public DataView ExceltoDataView(string strFilePath)
 {
     DataView dv;
     try
     {
         OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");
         conn.Open();
         object[] CSs0s0001 = new object[4];
         CSs0s0001[3] = "TABLE";
         DataTable tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, CSs0s0001);
         string tableName = Convert.ToString(tblSchema.Rows[0]["TABLE_NAME"]);
         if (tblSchema.Rows.Count > 1)
         {
             tableName = "sheet1$";
         }
         string sql_F = "SELECT * FROM [{0}]";
         OleDbDataAdapter adp = new OleDbDataAdapter(string.Format(sql_F, tableName), conn);
         DataSet ds = new DataSet();
         adp.Fill(ds, "Excel");
         dv = ds.Tables[0].DefaultView;
         conn.Close();
     }
     catch (Exception)
     {
         Exception strEx = new Exception("請確認是否使用模板上傳(上傳的Excel中第一個工作表名稱是否為Sheet1)");
         throw strEx;
     }
     return dv;
 }
        public Empresa ObtenerEmpresa(string RUC)
        {
            OleDbConnection obj_Conexion = new OleDbConnection();
            OleDbCommand obj_Comando = new OleDbCommand();
            OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
            DataSet obj_Data = new DataSet();
            obj_Conexion.ConnectionString = "Server=VIRTUALXP-50904\\SQL2008; Provider=SQLOLEDB; User ID=sa; Initial Catalog=Ventas; password=royal2008;";
            obj_Conexion.Open();
            obj_Comando.Connection = obj_Conexion;
            obj_Comando.CommandText = "Select * From Contribuyentes Where Ruc ='" + RUC + "'";
            obj_Comando.CommandType = CommandType.Text;
            obj_Adapter.SelectCommand = obj_Comando;
            obj_Adapter.Fill(obj_Data);
            obj_Conexion.Close();

            Empresa empresa = new Empresa(); ;
            foreach (var dr_Fila in obj_Data.Tables[0].Rows)
            {
                DataRow fila = (DataRow)dr_Fila;
                empresa.RUC = fila[0].ToString();
                empresa.nombrecomercial = fila[1].ToString();
                empresa.direccion = fila[5].ToString();
                empresa.telefono = fila[6].ToString();
                empresa.Estado = fila[3].ToString();
            }

            return empresa;
        }
示例#27
0
        //計算每個點的Frequency Table
        private void BFT_Click(object sender, EventArgs e)
        {
            DataTable FreqTable = new DataTable();
            GetDataSet.Open();
            OleDbDataAdapter dafretable = new OleDbDataAdapter("SELECT * FROM [01.csv]", GetDataSet);
            dafretable.Fill(FreqTable);

            foreach (DataRow row in FreqTable.Rows)
            {
                node[(int)row[0]].Visit[(int)row[1]] = node[(int)row[0]].Visit[(int)row[1]] + 1;  //計算每個點拜訪每個地點分別次數
                node[(int)row[0]].VTotal = node[(int)row[0]].VTotal + 1;                          //計算每個點總拜訪次數
            }

            for (int x = 1; x < 100; x++)
            {
                for (int y = 1; y < (locanum + 1); y++)
                {
                    if (node[x].VTotal != 0)
                    {
                        node[x].FTable[y] = (node[x].Visit[y] / node[x].VTotal) * 10;                  //計算頻率
                    }

                }
            }
            //label1.Text = node[121].FTable[363].ToString();
            GetDataSet.Close();
            state.Text = "BFT over.";
        }
 /// <summary>
 /// initialization casting for InitializeDataAccess()
 /// </summary>
 /// <param name="type"></param>
 /// <param name="ConnectionString"></param>
 /// <param name="Query"></param>
 private void castProvider(ProviderType type, string ConnectionString, string Query = null)
 {
     switch (type)
     {
         case ProviderType.Oledb:
             conn = new OleDbConnection(ConnectionString);
             cmd = new OleDbCommand(Query, (OleDbConnection)conn);
             da = new OleDbDataAdapter();
             break;
         case ProviderType.Odbc:
             conn = new OdbcConnection(ConnectionString);
             cmd = new OdbcCommand(Query, (OdbcConnection)conn);
             da = new OdbcDataAdapter();
             break;
         case ProviderType.SqlClient:
             conn = new SqlConnection(ConnectionString);
             cmd = new SqlCommand(Query, (SqlConnection)conn);
             da = new SqlDataAdapter();
             break;
         //case ProviderType.OracleClient:
         //    conn = new OracleConnection(ConnectionString);
         //    cmd = new OracleCommand(Query,(OracleConnection)conn);
         //    break;
     }
 }
示例#29
0
        public static DataTable GetExcelDataTable(string filePath, string sql)
        {
            try
            {
                //Office 2003
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;Readonly=0'");

                //Office 2007
                OleDbConnection conn =
                    new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
                                        ";Extended Properties='Excel 12.0 Xml;HDR=YES'");

                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dt.TableName = "tmp";
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                MessageBox.Show("Please input correct SQL Command for your file!");
                return null;
            }
        }
示例#30
0
        public void GetTracks()
        {
            var dt = new DataTable("Track");

            using (var conn = new OleDbConnection())
            {
                conn.ConnectionString = GetConnectionString(Filename);
                conn.Open();

                var commandText = string.Format("SELECT * FROM [{0}${1}]", SheetName, SheetRange);

                using (var cmd = new OleDbCommand(commandText, conn))
                {

                    var adapter = new OleDbDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
            }

            _reservedColumnsCount = 0;
            _tracks = new List<string>();
            foreach (DataColumn col in dt.Columns)
            {
                if (!MetadataFileFormat.GetReservedColumnNames().Contains(col.ColumnName))
                    _tracks.Add(col.ColumnName);
                else
                    _reservedColumnsCount++;
            }
        }
示例#31
0
        /// <summary>
        /// 从EXCEL中获取数据(放入dataset中)
        /// </summary>
        /// <param name="postedFile"></param>
        /// <param name="context"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet GetDataFromUploadFile(this HttpPostedFile postedFile, HttpContext context, string tableName)
        {
            string directory = context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY);

            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            string filename = postedFile.FileName;
            //将文件上传至服务器
            postedFile.SaveAs(context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename);

            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

            //string sqlin = "SELECT * FROM [" + "ConstructPlanDevice" + "$]";
            //OleDbCommand oleCommand = new OleDbCommand(sqlin, new OleDbConnection(conn));

            //OleDbDataAdapter adapterIn = new OleDbDataAdapter(oleCommand);
            //DataSet dsIn = new DataSet();
            //adapterIn.Fill(dsIn, tableName);

            OleDbConnection conn1 = new OleDbConnection(conn);
            conn1.Open();
            string name = conn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from ["+name+"]", conn1);
            DataSet dsIn1 = new DataSet();
            odda.Fill(dsIn1, tableName);
            conn1.Close();
            return dsIn1;
        }
        /// <summary>
        /// Opens an Excel file and imports worksheet into a DataTable.  Worksheet Name is required.
        /// </summary>
        public static DataTable Build(string FilePath, string WorksheetName)
        {
            if (Path.GetExtension(FilePath) == ".xls")
            {
                // If anything goes wrong, "using" will force disposal of the connection to the file
                using (OleDbConnection conn = BuildExcelConnection(FilePath))
                {
                    // "Connect" to the XLS file
                    conn.Open();

                    // Get a DataAdapter to the query
                    string ExcelQuery = String.Format(SelectWorksheetQueryTemplate, WorksheetName);
                    OleDbDataAdapter Adapter = new OleDbDataAdapter(ExcelQuery, conn);

                    // Populate DataTable using DataAdapter
                    DataTable dataTable = new DataTable();
                    Adapter.Fill(dataTable);

                    // Close the connection
                    conn.Close();

                    // Finished
                    return dataTable;
                }
            }
            if (Path.GetExtension(FilePath) == ".xlsx")
            {
                return OpenXmlExcelToDataTableBuilder.Build(FilePath, WorksheetName);
            }

            throw new ArgumentException("Invalid file extension specified on Excel data file:" + FilePath);
        }
示例#33
0
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        string TempUploadFile = "~/CaseMaintain/TempUploadFile";
        string IdNos          = "";

        if (fu_Excel.PostedFile.ContentLength > 0)
        {
            try
            {
                if (!Directory.Exists(Server.MapPath(TempUploadFile)))
                {
                    Directory.CreateDirectory(Server.MapPath(TempUploadFile));
                }
                string strDate = DateTime.Now.ToString("yyyyMMddHHmmss");
                Session["UploadExcelFileName"] = strDate;
                fu_Excel.SaveAs(Server.MapPath(TempUploadFile + "/") + strDate + ".xls");
                SqlCommand cmd1 = new SqlCommand();

                System.Data.OleDb.OleDbConnection  cn = new System.Data.OleDb.OleDbConnection();
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
                DataTable dt = new DataTable();
                cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(TempUploadFile + "/") + strDate + ".xls" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                cn.Open();
                cmd1.CommandTimeout = 0;

                try
                {
                    da = new System.Data.OleDb.OleDbDataAdapter("select * from [IdentityNumber$]", cn);
                    da.SelectCommand.CommandTimeout = 0;
                    da.Fill(dt);

                    foreach (DataRow r in dt.Rows)
                    {
                        IdNos += r["ID"].ToString() + ",";
                    }
                }
                catch (Exception ex2)
                {
                    Response.Write(ex2.Message);
                }


                cmd1.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                // Response.Write(ex.Message);
            }
        }

        IdNos = IdNos.TrimEnd(',');
        //Response.Write(IdNos);
        Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "window.opener.getIdNo('" + IdNos + "'); window.close();", true);
        // Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "", "window.opener.getIdNo('" + IdNos + "'); window.close();", true);
    }
示例#34
0
    public DataTable GetDataTableExcel(string strFileName, string Table)
    {
        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");

        conn.Open();
        string strQuery = "SELECT * FROM [" + Table + "]";

        System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
        System.Data.DataSet ds = new System.Data.DataSet();
        adapter.Fill(ds);
        return(ds.Tables[0]);
    }
示例#35
0
    protected void SearchCV()
    {
        grdsearch.DataSource = null;
        grdsearch.DataBind();


        //Dim strCatalog As String
        string strCatalog;

        //' Catalog Name
        //strCatalog = "TestCatalog"
        if (rdbPersonnelCV.Checked)
        {
            strCatalog = ConfigReader.PersonnelCatelogName.ToString();
        }
        else
        {
            strCatalog = ConfigReader.CVBankCatelogName.ToString();
        }

        //Dim strQuery As String
        string strQuery;

        //strQuery = "Select DocTitle,Filename,Size,PATH,URL from SCOPE() where FREETEXT('" & TextBox1.Text & "')"....
        strQuery = "Select Contents,DocTitle, Filename, Size, PATH, URL,characterization " +
                   " from SCOPE() WHERE FREETEXT(Contents,'" + txtKeyword.Text.ToSqlSafeData() + "')";
        //strQuery = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, '" + txtKeyword.Text.ToSqlSafeData() + "') order by rank desc ";

        //' TextBox1.Text is word that you type in the text box to query by using Index Service.
        //'
        //Dim connString As String = "Provider=MSIDXS.1;Integrated Security .='';Data Source='" & strCatalog & "'"
        string connString;

        connString = "Provider=MSIDXS.1;Integrated Security .='';Data Source='" + strCatalog + "'";

        //Dim cn As New System.Data.OleDb.OleDbConnection(connString)
        System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(connString);
        //Dim cmd As New System.Data.OleDb.OleDbDataAdapter(strQuery, cn)
        System.Data.OleDb.OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(strQuery, cn);
        //Dim testDataSet As New System.Data.DataSet()
        System.Data.DataSet testDataSet = new DataSet();

        //cmd.Fill(testDataSet)
        cmd.Fill(testDataSet);

        DataTable detTable = new DataTable();

        detTable = getDetailsInformation(testDataSet.Tables[0]);

        grdsearch.DataSource = detTable;
        grdsearch.DataBind();
    }
示例#36
0
 public DataTable GetDraggableDataSource()
 {
     if (HttpContext.Current.Session[draggableSource] == null)
     {
         var command          = "SELECT CategoryID, CategoryName, Description FROM Categories";
         var connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + HttpContext.Current.Server.MapPath(@"~/App_Data/Categories + products.mdb");
         var adapter          = new System.Data.OleDb.OleDbDataAdapter(command, connectionString);
         var dt = new DataTable();
         adapter.Fill(dt);
         HttpContext.Current.Session[draggableSource] = dt;
     }
     return(HttpContext.Current.Session[draggableSource] as DataTable);
 }
示例#37
0
        /// <summary>
        /// Returns the contents of the sheet
        /// </summary>
        /// <param name="worksheetName">The sheet's name in a string</param>
        /// <returns>A DataTable containing the data</returns>
        public System.Data.DataTable GetWorksheet(string worksheetName)
        {
            OleDbConnection  con = new System.Data.OleDb.OleDbConnection(connectionString);
            OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
                "select * from [" + worksheetName + "]", con);

            con.Open();
            System.Data.DataSet excelDataSet = new DataSet();
            cmd.Fill(excelDataSet);
            con.Close();
            if (excelDataSet.Tables.Count > 0)
            {
                return(excelDataSet.Tables[0]);
            }
            else
            {
                return(null);
            }
        }
示例#38
0
        public DataTable leerExcel(int hoja)
        {
            DataTable dt = new DataTable();

            try
            {
                string          sSheetName             = "";
                string          sConnection            = "";
                DataTable       dtTablesList           = default(DataTable);
                OleDbConnection oleExcelConnection     = default(OleDbConnection);
                System.Data.OleDb.OleDbDataAdapter cmd = default(System.Data.OleDb.OleDbDataAdapter);
                //sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" & path & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
                sConnection        = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + path + ";Extended Properties=\"Excel 12.0;HDR=no;IMEX=1\"";
                oleExcelConnection = new OleDbConnection(sConnection);
                oleExcelConnection.Open();

                dtTablesList = oleExcelConnection.GetSchema("Tables");

                if (dtTablesList.Rows.Count > 0)
                {
                    sSheetName = dtTablesList.Rows[hoja]["TABLE_NAME"].ToString();
                }

                dtTablesList.Clear();
                dtTablesList.Dispose();

                if (!string.IsNullOrEmpty(sSheetName))
                {
                    cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sSheetName + "]", oleExcelConnection);
                    cmd.Fill(dt);
                    oleExcelConnection.Close();
                }

                oleExcelConnection.Close();
                //USAR EL dt PARA INSERTAR LOS DATOS NECESARIOS.
                return(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return(new DataTable());
            }
        }
    DataSet ReadExcel(string fileNameToUpload)
    {
        DataSet ds = new DataSet();

        string fileExt = Path.GetExtension(fileUploadCon.FileName);

        string connString = "";

        if (fileExt == ".xls")
        {
            connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("UploadedOrders") + "\\" + fileNameToUpload + ";Extended Properties=Excel 8.0;";
        }
        else
        {
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("UploadedOrders") + "\\" + fileNameToUpload + ";Extended Properties=Excel 12.0;";
        }
        System.Data.OleDb.OleDbConnection oledbConn = new System.Data.OleDb.OleDbConnection(connString);
        try
        {
            oledbConn.Open();
            System.Data.OleDb.OleDbCommand     cmd   = new System.Data.OleDb.OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
            System.Data.OleDb.OleDbDataAdapter oleda = new System.Data.OleDb.OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            oleda.Fill(ds);
        }
        catch (Exception objException)
        {
            if (objException != null && objException.Message != null)
            {
                string script = "alertify.alert('" + objException.Message + "');";
                ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", script, true);
            }
            CSLOrderingARCBAL.LinqToSqlDataContext db;
            db = new CSLOrderingARCBAL.LinqToSqlDataContext();
            db.USP_SaveErrorDetails(Request.Url.ToString(), "ReadExcel", Convert.ToString(objException.Message), Convert.ToString(objException.InnerException), Convert.ToString(objException.StackTrace), "", HttpContext.Current.Request.UserHostAddress, false, Convert.ToString(HttpContext.Current.Session[enumSessions.User_Id.ToString()]));
        }

        finally
        {
            oledbConn.Close();
        }
        return(ds);
    }
        private void updateMatters(string fileName)
        {
            matErrors = new DataSet();
            DataTable et = matErrors.Tables.Add("Errors");

            et.Columns.Add("ClientCode");
            et.Columns.Add("MatterCode");
            et.Columns.Add("BillToBill");
            et.Columns.Add("BillToEdit");
            et.Columns.Add("ErrorMessage");
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.DataSet DtSet;
            System.Data.OleDb.OleDbDataAdapter MyCommand;
            MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;");

            MyCommand = new System.Data.OleDb.OleDbDataAdapter("select distinct * from [Matter$]", MyConnection);

            DtSet = new System.Data.DataSet();
            MyCommand.Fill(DtSet);
            MyConnection.Close();

            DataTable dt = DtSet.Tables[0];

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string clicode    = dt.Rows[i]["CliCode"].ToString();
                string BillToBill = dt.Rows[i]["BillToBillFormat"].ToString();
                string Matter     = dt.Rows[i]["MatCode"].ToString();
                string BillToEdit = dt.Rows[i]["BillToEditFormat"].ToString();

                string sqlIns = "update billto set BillToBillFormat = '" + BillToBill + "', BillToEditFormat = '" + BillToEdit + "' where billtosysnbr = " +
                                " (select matbillto from matter where matcode like '%00" + Matter + "' and matclinbr = (select clisysnbr from client where clicode like '%000" + clicode + "'))";

                _jurisUtility.ExecuteNonQueryCommand(0, sqlIns);

                if (_jurisUtility.error)
                {
                    et.Rows.Add(clicode, Matter, BillToBill, BillToEdit, _jurisUtility.errorMessage.Replace("\r", "").Replace("\n", "").Trim());
                    _jurisUtility.error        = false;
                    _jurisUtility.errorMessage = "";
                }
            }
        }
示例#41
0
    /// 非同步, 網頁不太好使, 太常PostBack
    public async static Task <string> fnUploadExcelAsync(FileUpload fuFileUpload, string sSql, int iAmount, System.Web.UI.Page pagePage)
    {
        if (fuFileUpload.FileName.Length < 1)
        {
            //fnMessageBox("請按瀏覽選擇要上傳的檔案", pagePage);
            return("請按瀏覽選擇要上傳的檔案");
        }
        else if (fuFileUpload.FileName.Substring(fuFileUpload.FileName.Length - 3) != "xls")
        {
            //fnMessageBox("不是*.xls的Excel檔案!", pagePage);
            return("不是*.xls的Excel檔案!");
        }
        else
        {
            if (Directory.Exists(pagePage.Server.MapPath("~/Imports/")) == false)
            {
                Directory.CreateDirectory(pagePage.Server.MapPath("~/Imports/"));
            }

            string appPath  = pagePage.Request.PhysicalApplicationPath;
            string filePath = appPath + "\\Imports\\";
            fuFileUpload.SaveAs(filePath + fuFileUpload.FileName);
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + fuFileUpload.FileName +
                             ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
            System.Data.OleDb.OleDbDataAdapter myCommeand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
            DataSet myDataSet = new DataSet();
            try
            {
                myCommeand.Fill(myDataSet, "Excelinfo");
            }
            catch (Exception ex)
            {
                return("活頁步名稱請改Sheet1");
                //myCommeand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + fuFileUpload.FileName.Substring(0, fuFileUpload.FileName.Length - 4) + "$]", strConn);
                //myCommeand.Fill(myDataSet, "Excelinfo");
            }
            DataTable dtData = myDataSet.Tables["Excelinfo"].DefaultView.ToTable();
            var       vTask  = await Task.Run(() => fnInsertSql(dtData, sSql, iAmount));

            return(vTask);
        }
    }
示例#42
0
        public DataTable ImportWorkSheet(string sheetname, string filepath, string Extension)
        {
            string isHDR = "Yes";

            string conStr = "";

            switch (Extension)
            {
            case ".xls":     //Excel 97-03
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
                break;

            case ".xlsx":     //Excel 07
                conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
                break;
            }

            conStr = String.Format(conStr, filepath, isHDR);
            using (OleDbConnection connExcel = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmdExcel = new OleDbCommand())
                {
                    cmdExcel.Connection = connExcel;
                    connExcel.Open();
                    // using (DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                    // {
                    connExcel.Close();
                    sheetname            = sheetname + "$";
                    cmdExcel.CommandText = "SELECT * From [" + sheetname + "] Where Actions is Not Null";
                    using (OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter())
                    {
                        using (DataTable ds = new DataTable())
                        {
                            da.SelectCommand = cmdExcel;
                            da.Fill(ds);
                            return(ds);
                        }
                    }
                    //   }
                }
            }
        }
示例#43
0
        public static DataTable import(string arquivo)
        {
            string ext     = string.Empty;
            string aspas   = "******"";
            string Conexao = string.Empty;

            for (int i = arquivo.Length - 1; i < arquivo.Length; i--)
            {
                if (arquivo[i] != '.')
                {
                    ext += arquivo[i];
                }
                else
                {
                    ext += "."; break;
                }
            }
            ext = Reverse(ext);
            if (ext == ".xls")
            {
                Conexao = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + arquivo + ";" + "Extended Properties=" + aspas + "Excel 8.0;HDR=YES" + aspas;
            }
            if (ext == ".xlsx")
            {
                Conexao = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + arquivo + ";" + "Extended Properties=" + aspas + "Excel 12.0;HDR=YES" + aspas;
            }
            System.Data.OleDb.OleDbConnection Cn = new System.Data.OleDb.OleDbConnection();
            Cn.ConnectionString = Conexao; Cn.Open();
            object[]  Restricoes = { null, null, null, "TABLE" };
            DataTable DTSchema   = Cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Restricoes);

            if (DTSchema.Rows.Count > 0)
            {
                string Sheet = DTSchema.Rows[0]["TABLE_NAME"].ToString();
                System.Data.OleDb.OleDbCommand Comando = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + Sheet + "]", Cn);
                DataTable Dados = new DataTable();
                System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter(Comando);
                DA.Fill(Dados);
                Cn.Close(); return(Dados);
            }
            return(null);
        }
示例#44
0
 public static System.Data.DataSet Query(string SQLString)
 {
     System.Data.DataSet result;
     using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
     {
         System.Data.DataSet dataSet = new System.Data.DataSet();
         try
         {
             oleDbConnection.Open();
             System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(SQLString, oleDbConnection);
             oleDbDataAdapter.Fill(dataSet, "ds");
         }
         catch (System.Data.OleDb.OleDbException ex)
         {
             throw new Exception(ex.Message);
         }
         result = dataSet;
     }
     return(result);
 }
示例#45
0
        /// <summary>
        /// 读取XLS数据到数据库
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static DataSet xsldata(string filepath, String SheetName)
        {
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);

            Conn.Open();
            DataTable dt        = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            String    tableName = dt.Rows[0][2].ToString().Trim();

            string strCom = String.Format("SELECT * FROM [{0}]", tableName);


            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
            DataSet ds = new DataSet();

            myCommand.Fill(ds);
            Conn.Close();
            return(ds);
        }
 private void showdata_Click(object sender, EventArgs e)
 {
     try {
         System.Data.OleDb.OleDbConnection MyConnection;
         // System.Data.DataSet DtSet;
         System.Data.OleDb.OleDbDataAdapter MyCommand;
         MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textselect.Text + "; Extended Properties = Excel 8.0");
         MyCommand    = new System.Data.OleDb.OleDbDataAdapter("select * from [" + textchoice.Text + "$]", MyConnection);
         MyCommand.TableMappings.Add("Table", "TestTable");
         DtSet = new System.Data.DataSet();
         MyCommand.Fill(DtSet);
         dataGridView.DataSource = DtSet.Tables[0];
         MyConnection.Close();
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.ToString());
     }
     //
 }
示例#47
0
        private static void ReadValue(string file)
        {
            string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";

            OleDbConnection con = new OleDbConnection(ConnectionString);

            DataTable dt = new DataTable();

            con.Open();
            OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("select * from [SHEET1$]", con);

            da.Fill(dt);
            con.Close();

            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine(row[dt.Columns[1]]);
                Console.WriteLine();
            }
        }
示例#48
0
 /// <summary>
 /// 把excel数据读入dataset返回(服务端读取)
 /// </summary>
 /// <param name="FilePath"></param>
 /// <returns></returns>
 private DataSet ConvertXlsToDataSet(string FilePath)
 {
     //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES'";
     try
     {
         string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + FilePath;
         System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
         string strCom = "SELECT * FROM [Sheet1$]";
         Conn.Open();
         System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
         DataSet ds = new DataSet();
         myCommand.Fill(ds, "[Sheet1$]");
         Conn.Close();
         return(ds);
     }
     catch
     {
         return(null);
     }
 }
示例#49
0
        //
        // Create table of employees.
        //
        public void CreateTable()
        {
            dSet = new DataSet();
            String Query = "SELECT ID, SurName, FirstName, Patronymic FROM Empoyee WHERE DepaRtmentID = (SELECT ID FROM Department WHERE Name = '" + nameDep + "')";

            dAdapter = new OleDbDataAdapter(Query, cn);
            dAdapter.Fill(dSet, "Empoyee");
            dTable = dSet.Tables["Empoyee"];
            dTable.Columns.Add(" ", typeof(String));
            bs.DataSource            = dTable;
            dataGridView1.DataSource = bs;
            dataGridView1.Columns[0].HeaderCell.Value = "ID";
            dataGridView1.Columns[1].HeaderCell.Value = "Фамилия";
            dataGridView1.Columns[2].HeaderCell.Value = "Имя";
            dataGridView1.Columns[3].HeaderCell.Value = "Отчество";
            for (int i = 0; i < dTable.Rows.Count; i++)
            {
                dataGridView1.Rows[i].Cells[4].Value = "Подробнее...";
            }
        }
示例#50
0
 /// <summary>
 /// To Execute queries which returns result set (table / relation)
 /// </summary>
 /// <param name="query">the query string</param>
 /// <returns></returns>
 public static DataTable ExecuteDataTable(string query)
 {
     try
     {
         con.Open();
         OleDbCommand command = new OleDbCommand(query, con);
         System.Data.OleDb.OleDbDataAdapter tableAdapter = new System.Data.OleDb.OleDbDataAdapter(command);
         DataTable dt = new DataTable();
         tableAdapter.Fill(dt);
         return(dt);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         con.Close();
     }
 }
示例#51
0
        public T FindById(Guid ID)
        {
            DataTable dataTable;

            using (OleDbCommand cmd = new OleDbCommand())
                using (OleDbConnection conn = new OleDbConnection(ConnectionString))
                {
                    conn.Open();

                    var allFields          = _utility.GetColumns <T>();
                    var query              = $"select {string.Join(",", allFields.Select(d => "[" + d.Value + "]"))} from [{_utility.GetSheetName<T>()}$] WHERE ID='{ID}'";
                    OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
                                                 (query, conn);
                    DataSet excelDataSet = new DataSet();
                    objDA.Fill(excelDataSet);
                    dataTable = excelDataSet.Tables[0];
                }

            return(dataTable.DataTableToList <T>().FirstOrDefault());
        }
示例#52
0
    protected void ImportToGrid(String path)
    {
        DataTable dt = new DataTable();

        Path1 = path;
        OleDbConnection  MyConnection = null;
        DataSet          DtSet        = null;
        OleDbDataAdapter MyCommand    = null;

        strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
        //use below connection string if your excel file .xslx 2007 format
        if (strFileType.Trim() == ".xls")
        {
            MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");
        }
        else if (strFileType.Trim() == ".xlsx")
        {
            MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties=Excel 12.0;");
        }
        else
        {
            lblMessage.Text = "Sorry! The Extension format is Incorrect";
            return;
        }
        MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
        DtSet     = new System.Data.DataSet();
        MyCommand.Fill(DtSet, "[Sheet1]");
        dt = DtSet.Tables[0];
        MyConnection.Close();
        if (dt.Rows.Count > 0)
        {
            Grd_Assign_order_Type_to_State.DataSource = dt;
            Grd_Assign_order_Type_to_State.DataBind();
            btn_Submit.Visible = true;
            btn_Cancel.Visible = true;
        }
        if (System.IO.File.Exists(path))
        {
            System.IO.File.Delete(path);
        }
    }
示例#53
0
        private DataTable searchResult(string fullPath, string testItem, bool test = true)
        {
            string Item = "";

            if (testItem.Contains('['))
            {
                Item = testItem.Substring(0, testItem.IndexOf('['));
            }
            else
            {
                Item = testItem;
            }
            string filePath = fullPath.Substring(0, fullPath.LastIndexOf('\\'));
            string fileName = fullPath.Substring(fullPath.LastIndexOf('\\') + 1, fullPath.Length - fullPath.LastIndexOf('\\') - 1);
            string connStr  = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                              + filePath + ";Extended Properties='Text;HDR=YES;FMT=Delimited;IMEX=1;'";

            string          cmdStr  = "select 检测时间,NG项,结果,工站号,主站主,主站副,从站主,从站副,BarCode from [" + fileName + "]";
            OleDbConnection conn    = new System.Data.OleDb.OleDbConnection(connStr);
            var             retData = new System.Data.DataTable();

            try
            {
                conn.Open();
                var adapter = new System.Data.OleDb.OleDbDataAdapter(cmdStr, conn);
                adapter.Fill(retData);
            }
            catch (System.Exception ex)
            {
                writeErrorLog("查询异常" + ex.ToString());
            }
            finally
            {
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return(retData);
        }
示例#54
0
        public DataSet RetornaDataSet(string psql)
        {
            System.Data.OleDb.OleDbConnection  cnx = new System.Data.OleDb.OleDbConnection(ConexaoOledb);
            System.Data.OleDb.OleDbDataAdapter Adp = new System.Data.OleDb.OleDbDataAdapter(psql, cnx);
            DataSet ds = new DataSet();

            try
            {
                Adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }

            return(ds);
        }
        private string[,] Read(string fileName, int cols, int pageIndex = 1)
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                                ";Extended Properties=Excel 12.0;";

            System.Data.DataTable dt = null;
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return(null);
                }
                string[] excelSheets = new String[dt.Rows.Count];
                int      k           = 0;
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[k] = row["TABLE_NAME"].ToString();
                    k++;
                }
                string           sheetName = excelSheets[0];
                OleDbDataAdapter objDA     = new System.Data.OleDb.OleDbDataAdapter
                                                 ($"select * from [{sheetName}]", conn);
                DataSet excelDataSet = new DataSet();
                objDA.Fill(excelDataSet);
                System.Data.DataTable table = excelDataSet.Tables[0];
                int columnsCount            = cols;
                int rowsCount = table.Rows.Count;
                string[,] data = new string[table.Rows.Count, cols];
                for (int i = 0; i < rowsCount; i++)
                {
                    for (int j = 0; j < columnsCount; j++)
                    {
                        data[i, j] = table.Rows[i][j].ToString();
                    }
                }
                return(data);
            }
        }
        private DataSet GetData()
        {
            String filepath = Server.MapPath("/TrainWeb/Train/cjl.xls");
            string strCon   = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);

            Conn.Open();
            DataTable dt        = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            String    tableName = dt.Rows[0][2].ToString().Trim();

            tableName = "客运长交路机车牵引费单价表$";
            string strCom = String.Format("SELECT * FROM [{0}]", tableName);


            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
            DataSet ds = new DataSet();

            myCommand.Fill(ds);
            Conn.Close();
            return(ds);
        }
示例#57
0
        /// <summary>
        /// 读取Excel文件 指定Sheet
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <returns>DataTable</returns>
        public static DataTable ReadExcel(string fileName, string sheet, bool isHdR)
        {
            string hdr = "N0";

            if (isHdR)
            {
                hdr = "YES";
            }
            System.Data.DataSet itemDS = new DataSet();
            if (fileName.Trim().ToUpper().EndsWith("XLS") || fileName.Trim().ToUpper().EndsWith("XLSX"))
            {
                string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HdR=" + hdr + ";\"";
                System.Data.OleDb.OleDbConnection conn       = null;
                System.Data.OleDb.OleDbCommand    oledbCommd = null;
                try
                {
                    conn = new System.Data.OleDb.OleDbConnection();
                    conn.ConnectionString = connStr;
                    conn.Open();

                    string sqlText = "select * from [" + sheet + "]";
                    oledbCommd = new System.Data.OleDb.OleDbCommand(sqlText, conn);
                    oledbCommd.CommandTimeout = 100000;
                    //执行
                    System.Data.OleDb.OleDbDataAdapter oledbDA = new System.Data.OleDb.OleDbDataAdapter(oledbCommd);
                    oledbDA.Fill(itemDS);
                }
                catch
                { }
                finally
                {
                    //释放
                    oledbCommd.Dispose();
                    conn.Close();
                }
                //创建连接
            }
            return(itemDS.Tables[0]);
        }
示例#58
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public DataTable ConnectToExcel(string excelPath)
        {
            DataTable result = null;

            try
            {
                System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.DataSet DtSet;
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + excelPath + "';Extended Properties=Excel 8.0;");
                MyCommand    = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
                MyCommand.TableMappings.Add("Table", "TestTable");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                result = DtSet.Tables[0];
                MyConnection.Close();
            }
            catch (Exception ex)
            {
            }
            return(result);
        }
        public static DataTable readData()
        {
            try {
                string _connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + GlobalData.initSetting.BOSAREPORT + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                using (OleDbConnection conn = new OleDbConnection(_connectionString)) {
                    DataTable dt = new DataTable();
                    conn.Open();
                    OleDbDataAdapter objDA        = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$A8:H]", conn);
                    DataSet          excelDataSet = new DataSet();
                    objDA.Fill(excelDataSet);
                    dt = excelDataSet.Tables[0];

                    excelDataSet.Dispose();
                    objDA.Dispose();
                    conn.Dispose();
                    return(dt);
                }
            } catch (Exception ex) {
                System.Windows.MessageBox.Show(ex.ToString(), "Error", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
                return(null);
            }
        }
示例#60
-16
        public static int SaveRecord(string sql)
        {
            const int rv = 0;
            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
                using (var conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
                    var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
                    comm.ExecuteNonQuery();

                    var ds = new DataSet();
                    var adapt = new OleDbDataAdapter(cmGetID);
                    adapt.Fill(ds);
                    adapt.Dispose();
                    cmGetID.Dispose();

                    return int.Parse(ds.Tables[0].Rows[0][0].ToString());

                }
            }
            catch (Exception)
            {
            }
            return rv;
        }