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); } } } } }
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); } }
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++; } }
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; } }
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(); } }
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); }
//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(); }
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; } }
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)); }
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(); } }
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; } }
private void clears() { txtNameSearch.Text = "Enter all or part of a name here..."; txtCCSearch.Text = "Scan a customer card here..."; try { connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile; string query = "select * from customer order by last_name;"; OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); BindingSource bs = new BindingSource(); bs.DataSource = dt; dgvCustomers.DataSource = bs; da.Update(dt); databaseHandler d = new databaseHandler(); d.closeDatabaseConnection(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
public override DataTable GetRecords(string strWhere, string strGroupBy, string strOrderBy) { if (m_pOleDbConnection != null) { try { m_nCurrentRowIndex = 0; 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; }
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"); }
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); }
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; }
/// <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; } } }
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); } }
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; }
//計算每個點的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; } }
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; } }
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++; } }
/// <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); }
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); }
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]); }
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(); }
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); }
/// <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); } }
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 = ""; } } }
/// 非同步, 網頁不太好使, 太常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); } }
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); } } // } } } }
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); }
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); }
/// <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()); } // }
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(); } }
/// <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); } }
// // 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 = "Подробнее..."; } }
/// <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(); } }
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()); }
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); } }
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); }
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); }
/// <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]); }
/// <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); } }
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; }