public System.Collections.Generic.List<string> GetColumnNames(string file, string table) { System.Data.DataTable dataSet = new System.Data.DataTable(); string connString = ""; if (Global.filepassword != "") { if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB") connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Jet OLEDB:Database Password="******"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Jet OLEDB:Database Password="******".ACCDB") connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file; else connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Persist Security Info=False;"; } using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connString)) { connection.Open(); System.Data.OleDb.OleDbCommand Command = new System.Data.OleDb.OleDbCommand("SELECT * FROM " + table, connection); using (System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter(Command)) { dataAdapter.Fill(dataSet); } } System.Collections.Generic.List<string> columns = new System.Collections.Generic.List<string>(); for (int i = 0; i < dataSet.Columns.Count; i++) { columns.Add(dataSet.Columns[i].ColumnName); } return columns; }
/// <summary> /// Método necesario para admitir el Diseñador, no se puede modificar /// el contenido del método con el editor de código. /// </summary> private void InitializeComponent() { this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); this.dsFicha1 = new ficha_antecedentes_personales.dsFicha(); ((System.ComponentModel.ISupportInitialize)(this.dsFicha1)).BeginInit(); // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = "Provider=SQLOLEDB;server=edoras;OLE DB Services = -2;uid=protic;pwd=,.protic;init" + "ial catalog=protic2"; // // oleDbSelectCommand1 // this.oleDbSelectCommand1.CommandText = @"select '' as nombre, '' as rut, '' as pasaporte, '' as fecha_nac, '' as fono, '' as nacionalidad, '' as Estado_civil, '' as Direccion, '' as comuna, '' as ciudad, '' as region, '' as colegio_egreso, '' as ano_egreso, '' as proced_educ, '' as inst_educ_sup, '' as Carrera, '' as ano_ingr, '' as FinanciaEst, '' as ultimo_post_ncorr, '' as nombre_sost_ec, '' as RUT_sost_ec, '' as fnac_sost_ec, '' as edad_sost, '' as fono_sost_ec, '' as pare_sost_ec, '' as dire_tdesc_sost_ec, '' as comu_sost_ec, '' as ciud_sost_ec, '' as regi_sost_ec"; this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // oleDbDataAdapter1 // this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1; // // dsFicha1 // this.dsFicha1.DataSetName = "dsFicha"; this.dsFicha1.Locale = new System.Globalization.CultureInfo("es-CL"); this.dsFicha1.Namespace = "http://www.tempuri.org/dsFicha.xsd"; this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.dsFicha1)).EndInit(); }
private void Form1_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the 'clientDBDataSet.ClientTable' table. You can move, or remove it, as needed. //this.clientTableTableAdapter.Fill(this.clientDBDataSet.ClientTable); Con = new ADODB.Connection(); Rs = new ADODB.Recordset(); //Connection calling partner object Provider, Path file, access string Con.Provider = Obj.MSAccessObj.Provider; Con.ConnectionString = Obj.MSAccessObj.PathFile; Con.Open(); //partnerObj.CAccess.AccessString = "select * from " in Class String accessStr; accessStr = Obj.MSAccessObj.AccessString + accessTableName; Rs.Open(accessStr, Con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); // connection in sql DB cn = new System.Data.OleDb.OleDbConnection(); // A dataset for sql table ds = new DataSet(); //sqlCon.ConnectionString = Obj.MSSQLObj.DataSource + Obj.MSSQLObj.AttachedDB + Obj.MSSQLObj.IntSecurity; cn.ConnectionString = "Provider = SQLNCLI11.0;Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\ITD\\Term 3\\C#\\assignment\\assignment7\\AwareObject\\AwareObject\\AwareSQLDB.mdf;Integrated Security = SSPI;database=thingy"; cn.Open(); // Attempting to access Admin Table String sqlstr; sqlstr = Obj.MSSQLObj.SqlStr + sqlTableName; da = new System.Data.OleDb.OleDbDataAdapter(sqlstr, cn); da.Fill(ds, sqlTableName); RecordCount = ds.Tables[sqlTableName].Rows.Count; }
public DataTable SelectToDataTable(string sql) { //set connection System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(); con.ConnectionString = connectString; //set commandtext System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(); command.CommandText = sql; command.Connection = con; //set adapter System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(); adapter.SelectCommand = command; //creat a datatable DataTable dt = new DataTable(); try { //open this connection con.Open(); adapter.Fill(dt); } catch (Exception ex) { //throw new Exception con.Close(); } finally { //close this connection con.Close(); } //return a datatable return dt; }
private void Form1_Load(object sender, EventArgs e) { string conn = GetConnectionString(); string sql = @"select c.CompanyName, a.OrderDate, a.ShipName, a.Freight, a.ShipCountry, d.ProductName, b.UnitPrice, Quantity, Discount from Orders a, [Order Details] b, Customers c, Products d where a.OrderID = b.OrderID and a.CustomerID = c.CustomerID and b.ProductID = d.ProductID"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn); DataTable dt = new DataTable(); da.Fill(dt); c1TrueDBGrid1.DataSource = dt; foreach (C1DisplayColumn col in c1TrueDBGrid1.Splits[0].DisplayColumns) { col.AutoSize(); if (col.Name == "Freight") { col.DataColumn.NumberFormat = "Currency"; } else if (col.Name == "ShipCountry") { col.Merge = ColumnMergeEnum.Free; } else if (col.Name == "UnitPrice") { col.DataColumn.NumberFormat = "$0.00"; } else if (col.Name == "Discount") { col.DataColumn.NumberFormat = "Percent"; } } }
public void AccountLoad() { System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(); con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=acct.mdb;"; con.Open(); string quryString = "select * from Accounts order by id desc"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(quryString, con); DataSet ds = new DataSet(); da.Fill(ds, "Accounts"); int row = ds.Tables["Accounts"].Rows.Count - 1; int v = 0; int aid = 0; int rr = -1; for (int r = 0; r <= row; r++) { v = (int)ds.Tables["Accounts"].Rows[r].ItemArray[14]; if (v == 1) { aid = (int)ds.Tables["Accounts"].Rows[r].ItemArray[0]; rr = r; } } this.accountid = aid; con.Close(); }
protected void Application_Start(Object sender, EventArgs e) { System.Data.DataSet ds; System.Data.OleDb.OleDbConnection oleDbConnection1; System.Data.OleDb.OleDbDataAdapter daAttendees; System.Data.OleDb.OleDbDataAdapter daRooms; System.Data.OleDb.OleDbDataAdapter daEvents; oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Inetpub\wwwroot\PCSWebApp3\PCSWebApp3.mdb;Mode=ReadWrite|Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"; oleDbConnection1.Open(); ds = new DataSet(); daAttendees = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Attendees", oleDbConnection1); daRooms = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Rooms", oleDbConnection1); daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1); daAttendees.Fill(ds, "Attendees"); daRooms.Fill(ds, "Rooms"); daEvents.Fill(ds, "Events"); oleDbConnection1.Close(); Application["ds"] = ds; }
private void Fill_GridUnderManagementPersonnelExeptionAccessView_UnderManagementPersonnelExeptionAccessView(int PageIndex) { string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="; conStr += Server.MapPath("~/common/db/demo.mdb"); System.Data.OleDb.OleDbConnection srcDB = new System.Data.OleDb.OleDbConnection(conStr); srcDB.Open(); string sql = string.Empty; switch (PageIndex) { case 0: sql = "SELECT top 10 * FROM MasterUnderManagementPersonnelExeptionAccessView"; break; case 1: sql = "SELECT * FROM MasterUnderManagementPersonnelExeptionAccessView where id = 14"; break; } System.Data.OleDb.OleDbDataAdapter daSrc = new System.Data.OleDb.OleDbDataAdapter(sql, srcDB); DataTable oTable = new DataTable(); daSrc.Fill(oTable); srcDB.Close(); this.GridUnderManagementPersonnelExeptionAccessView_UnderManagementPersonnelExeptionAccessView.DataSource = oTable; }
public Programlist() { //CSVファイルの名前 string csvFileName = "program.csv"; //接続文字列 string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Program.basepath + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\""; System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(conString); string commText = "SELECT * FROM [" + csvFileName + "]"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(commText, con); //DataTableに格納する da.Fill(dt); //DataTable内のVoiceデータをArrayに収納 for (int i = 0; i < dt.Rows.Count; i++) { DataRow[] rows = dt.Select(); voicelist.Add((string)rows[i][0]); } }
public int AddEvent(String eventName, String eventRoom, String eventAttendees, String eventDate) { System.Data.OleDb.OleDbConnection oleDbConnection1; System.Data.OleDb.OleDbDataAdapter daEvents; DataSet ds; oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\\Inetpub\\wwwroot\\PCSWebApp3\\PCSWebApp3.mdb"; String oleDbCommand = "INSERT INTO Events (Name, Room, AttendeeList," + " EventDate) VALUES ('" + eventName + "', '" + eventRoom + "', '" + eventAttendees + "', '" + eventDate + "')"; System.Data.OleDb.OleDbCommand insertCommand = new System.Data.OleDb.OleDbCommand(oleDbCommand, oleDbConnection1); oleDbConnection1.Open(); int queryResult = insertCommand.ExecuteNonQuery(); if (queryResult == 1) { daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1); ds = (DataSet)Application["ds"]; ds.Tables["Events"].Clear(); daEvents.Fill(ds, "Events"); Application.Lock(); Application["ds"] = ds; Application.UnLock(); oleDbConnection1.Close(); } return queryResult; }
/// <summary> /// M�todo necesario para admitir el Dise�ador, no se puede modificar /// el contenido del m�todo con el editor de c�digo. /// </summary> private void InitializeComponent() { System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.datosInforme1 = new informe_1.datosInforme(); ((System.ComponentModel.ISupportInitialize)(this.datosInforme1)).BeginInit(); // // oleDbDataAdapter1 // this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1; this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "Table", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("NOMBRE", "NOMBRE")})}); // // oleDbSelectCommand1 // this.oleDbSelectCommand1.CommandText = "SELECT \'\' AS NOMBRE FROM DUAL"; this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = ((string)(configurationAppSettings.GetValue("cadenaConexion", typeof(string)))); // // datosInforme1 // this.datosInforme1.DataSetName = "datosInforme"; this.datosInforme1.Locale = new System.Globalization.CultureInfo("es-ES"); this.datosInforme1.Namespace = "http://www.tempuri.org/datosInforme.xsd"; this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.datosInforme1)).EndInit(); }
static public System.Data.DataTable GetDataTable(string strFileName) { // string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Convertor Files\;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=60'"; // System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=60'"); System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); try { conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\""); conn.Open(); string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]"; System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn); System.Data.DataSet ds = new System.Data.DataSet("CSV File"); adapter.Fill(ds); return(ds.Tables[0]); } catch (Exception ex) { return(null); } finally { conn.Close(); } }
public void DBSetup() { /******************************************************DB SETUP FUNCTION*************************************************/ /*This DBSetup() method instantiates all the objects needed to access a Database includig OleDbDataAdapter, which contains * 4 other objects(OlsDbSelectCommand, OlsDbInsertCommand, OleDbUpdateCommand, OleDbDeleteCommand.) And each command object * contains a connection object and an SQL string object*****************************************************************/ OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(); OleDbSelectCommand = new System.Data.OleDb.OleDbCommand(); OleDbInsertCommand = new System.Data.OleDb.OleDbCommand(); OleDbUpdateCommand = new System.Data.OleDb.OleDbCommand(); OleDbDeleteCommand = new System.Data.OleDb.OleDbCommand(); OleDbConnection = new System.Data.OleDb.OleDbConnection(); OleDbDataAdapter.DeleteCommand = OleDbDeleteCommand; OleDbDataAdapter.InsertCommand = OleDbInsertCommand; OleDbDataAdapter.SelectCommand = OleDbSelectCommand; OleDbDataAdapter.UpdateCommand = OleDbUpdateCommand; //The text below includes the database information and where it is located OleDbConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= ???????????????? ;Persist Security Info=False;"; }//end DBSetup()
/// <summary> /// M�todo necesario para admitir el Dise�ador, no se puede modificar /// el contenido del m�todo con el editor de c�digo. /// </summary> private void InitializeComponent() { this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); this.dsFicha1 = new ficha_antecedentes_personales.dsFicha(); ((System.ComponentModel.ISupportInitialize)(this.dsFicha1)).BeginInit(); // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = "Provider=SQLOLEDB;server=edoras;OLE DB Services = -2;uid=protic;pwd=,.protic;init" + "ial catalog=protic2"; // // oleDbSelectCommand1 // this.oleDbSelectCommand1.CommandText = @"select '' as nombre, '' as rut, '' as pasaporte, '' as fecha_nac, '' as fono, '' as nacionalidad, '' as Estado_civil, '' as Direccion, '' as comuna, '' as ciudad, '' as region, '' as colegio_egreso, '' as ano_egreso, '' as proced_educ, '' as inst_educ_sup, '' as Carrera, '' as ano_ingr, '' as FinanciaEst, '' as ultimo_post_ncorr, '' as nombre_sost_ec, '' as RUT_sost_ec, '' as fnac_sost_ec, '' as edad_sost, '' as fono_sost_ec, '' as pare_sost_ec, '' as dire_tdesc_sost_ec, '' as comu_sost_ec, '' as ciud_sost_ec, '' as regi_sost_ec"; this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // oleDbDataAdapter1 // this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1; // // dsFicha1 // this.dsFicha1.DataSetName = "dsFicha"; this.dsFicha1.Locale = new System.Globalization.CultureInfo("es-CL"); this.dsFicha1.Namespace = "http://www.tempuri.org/dsFicha.xsd"; this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.dsFicha1)).EndInit(); }
private void EhViewResults() { // make sure we have a select statement var sql = _selectionStatement; if (string.IsNullOrEmpty(sql)) { return; } // create table to load with data and display var dt = new System.Data.DataTable("Query"); // get data try { using (var da = new System.Data.OleDb.OleDbDataAdapter(SelectionStatement, ConnectionString.ConnectionStringWithTemporaryCredentials)) { // get data da.Fill(0, MAX_PREVIEW_RECORDS, dt); // show the data var ctrl = new DataPreviewController(dt); string title = string.Format("{0} ({1:n0} records)", dt.TableName, dt.Rows.Count); Current.Gui.ShowDialog(ctrl, title, false); } } catch (Exception x) { Current.Gui.ErrorMessageBox(string.Format("Failed to retrieve data:\r\n{0}", x.Message)); } }
//***************** DBSetup Function********************** public void DBSetup() { // instantiates all the DB objects to access a DB OleDbDataAdapter2 = new System.Data.OleDb.OleDbDataAdapter(); OleDbSelectCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbInsertCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbUpdateCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbDeleteCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbConnection2 = new System.Data.OleDb.OleDbConnection(); // instantiates OleDbDataAdapter, which contains 4 objects OleDbDataAdapter2.DeleteCommand = OleDbDeleteCommand2; OleDbDataAdapter2.InsertCommand = OleDbInsertCommand2; OleDbDataAdapter2.SelectCommand = OleDbSelectCommand2; OleDbDataAdapter2.UpdateCommand = OleDbUpdateCommand2; // Command object contains a Connection object and an SQL string object OleDbConnection2.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" + "ocking Mode=1;Data Source=c:\\Users\\mimih\\Downloads\\CIST 2342\\Lab\\RegistrationMDB.mdb;J" + "et OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System datab" + "ase=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=S" + "hare Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet " + "OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repai" + "r=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"; Console.WriteLine("Connected to SQL..."); } // end DBSetup()
private void CalculateTotalAsset(System.Data.OleDb.OleDbConnection con) { string quryString = "select * from Expenses where accountid=" + this.accountid + " order by id desc"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(quryString, con); DataSet ds = new DataSet(); da.Fill(ds, "Expenses"); int row = ds.Tables["Expenses"].Rows.Count - 1; for (int r = 0; r <= row; r++) { this.asset = this.asset - Double.Parse(ds.Tables["Expenses"].Rows[r].ItemArray[3].ToString()); } quryString = "select * from Income where accountid=" + this.accountid + " order by id desc"; da = new System.Data.OleDb.OleDbDataAdapter(quryString, con); ds = new DataSet(); da.Fill(ds, "Income"); row = ds.Tables["Income"].Rows.Count - 1; for (int r = 0; r <= row; r++) { this.asset = this.asset + Double.Parse(ds.Tables["Income"].Rows[r].ItemArray[3].ToString()); } this.balass.Text = "$" + String.Format("{0:0.00}", this.asset); }
/** * DBSetup(); * Database setup function. This method setups all the funtionallity * required to access a desired database by instanciating the required * DB Objects including OleDbDataAdapter, which contains 4 other objects(OlsDbSelectCommand, * oleDbInsertCommand, oleDbUpdateCommand, oleDbDeleteCommand.) And each * Command object contains a Connection object and an SQL string object. * * OleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RegistrationMDB.mdb;"; **/ public void DBSetup() { // instanciated System database objects OleDbDataAdapter2 = new System.Data.OleDb.OleDbDataAdapter(); OleDbSelectCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbInsertCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbUpdateCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbDeleteCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbConnection2 = new System.Data.OleDb.OleDbConnection(); OleDbDataAdapter2.DeleteCommand = OleDbDeleteCommand2; OleDbDataAdapter2.InsertCommand = OleDbInsertCommand2; OleDbDataAdapter2.SelectCommand = OleDbSelectCommand2; OleDbDataAdapter2.UpdateCommand = OleDbUpdateCommand2; OleDbConnection2.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;" + "Jet OLEDB:Registry Path=;" + "Jet OLEDB:Database Locking Mode=1;" + "Data Source=C:\\Users\\Mattw\\Desktop\\C# II\\Week # 4\\StudentRegistrationSystem\\RegistrationMDB.mdb;" + "Jet OLEDB:Engine Type=5;" + "Provider=Microsoft.Jet.OLEDB.4.0;" + "Jet OLEDB:System database=;" + "Jet OLEDB:SFP=False;" + "persist security info=False;" + "Extended Properties=;" + "Mode=S" + "hare Deny None;" + "Jet OLEDB:Encrypt Database=False;" + "Jet OLEDB:Create System Database=False;" + "Jet OLEDB:Don't Copy Locale on Compact=False;" + "Jet OLEDB:Compact Without Replica Repair=False;" + "User ID=Admin;" + "Jet OLEDB:Global Bulk Transactions=1"; }// end DBSetup()
/// <summary> /// Método necesario para admitir el Diseñador, no se puede modificar /// el contenido del método con el editor de código. /// </summary> private void InitializeComponent() { System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); this.dataSet11 = new contrato_docente_otec.DataSet1(); ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit(); // // oleDbSelectCommand1 // this.oleDbSelectCommand1.CommandText = @"SELECT 0 AS CDOt_NCORR, 0 AS pers_ncorr, '' AS Nombre_Docente, '' AS Rut_Docente, '' AS DV, '' AS Fecha_Nac, '' AS Estado_Civil, '' AS Direccion, '' AS Comuna, '' AS Ciudad, '' AS PROFESION, 0 AS Bhot_ANEXO, 0 AS dcurr_CCOD, '' AS dcurr_TDESC, '' AS mote_CCOD, 0 AS daot_NHORA, '' AS mote_TDESC, '' AS daot_mhora, 0 AS anot_ncorr, '' AS INST_TRAZON_SOCIAL, '' AS NombreRepLeg, '' AS tcat_tdesc, '' AS Nacionalidad, '' AS cdot_finicio, '' AS cdot_ffin, '' AS SEot_TDESC, '' AS sede_tdesc, '' AS anot_ncuotas, '' AS anot_inicio, '' AS anot_fin, '' AS institucion_t, '' AS TipoDocente, '' AS ano_contrato, '' AS seot_ncorr, 0 AS daot_ncorr, 0 AS valorI, '' AS Mes, '' AS Dia, '' AS Año, 0 AS valoII, '' AS anot_ncodigo"; this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = ((string)(configurationAppSettings.GetValue("cadenaConexion", typeof(string)))); this.oleDbConnection1.InfoMessage += new System.Data.OleDb.OleDbInfoMessageEventHandler(this.oleDbConnection1_InfoMessage); // // oleDbDataAdapter1 // this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1; this.oleDbDataAdapter1.RowUpdated += new System.Data.OleDb.OleDbRowUpdatedEventHandler(this.oleDbDataAdapter1_RowUpdated); // // dataSet11 // this.dataSet11.DataSetName = "DataSet1"; this.dataSet11.Locale = new System.Globalization.CultureInfo("es-CL"); this.dataSet11.Namespace = "http://www.tempuri.org/DataSet1.xsd"; this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit(); }
public void DBSetup() { // +++++++++++++++++++++++++++ DBSetup function +++++++++++++++++++++++++++ // This DBSetup() method instantiates all the DB objects needed to access a DB, // including OleDbDataAdapter, which contains 4 other objects(OlsDbSelectCommand, // oleDbInsertCommand, oleDbUpdateCommand, oleDbDeleteCommand.) And each // Command object contains a Connection object and an SQL string object. OleDbDataAdapter2 = new System.Data.OleDb.OleDbDataAdapter(); OleDbSelectCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbInsertCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbUpdateCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbDeleteCommand2 = new System.Data.OleDb.OleDbCommand(); OleDbConnection2 = new System.Data.OleDb.OleDbConnection(); OleDbDataAdapter2.DeleteCommand = OleDbDeleteCommand2; OleDbDataAdapter2.InsertCommand = OleDbInsertCommand2; OleDbDataAdapter2.SelectCommand = OleDbSelectCommand2; OleDbDataAdapter2.UpdateCommand = OleDbUpdateCommand2; // The highlighted text below should be changed to the location of your own database OleDbConnection2.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Reg" + "istry Path =; Jet OLEDB:Database L" + "ocking Mode=1;Data Source= C:\\Users\\Trenton MCleod\\Desktop\\RegistrationDB.mdb;J" + "et OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System datab" + "ase=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=S" + "hare Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet " + "OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repai" + "r=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"; } //end DBSetup()
private DataSet getFillSchema(ref StringBuilder sbSql, string strConnection) { try { conn = new System.Data.OleDb.OleDbConnection(strConnection); conn.Open(); } catch (Exception ex) { string msg = "Open Connection Exception / Check Connection String."; throw new Exception(msg, ex); } DataSet dataSet = new DataSet(); try { System.Data.OleDb.OleDbDataAdapter dbAdapter = new System.Data.OleDb.OleDbDataAdapter(); dbAdapter.SelectCommand = new System.Data.OleDb.OleDbCommand(sbSql.ToString(), conn); dbAdapter.FillSchema(dataSet, SchemaType.Source); } catch (Exception ex) { throw (ex); } return(dataSet); }
private void button5_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); ofd.DefaultExt = "*.xls;*.xlsx"; ofd.Filter = "Microsoft Excel (*.xls*)|*.xls*"; ofd.Title = "Выберите документ для загрузки данных"; if (ofd.ShowDialog() != DialogResult.OK) { MessageBox.Show("Вы не выбрали файл для открытия", "Загрузка данных...", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofd.FileName + ";Extended Properties='Excel 12.0 XML;HDR=YES;IMEX=1';"; System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(constr); con.Open(); DataSet ds = new DataSet(); DataTable schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string sheet1 = (string)schemaTable.Rows[0].ItemArray[2]; string select = String.Format("SELECT * FROM [{0}]", sheet1); System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter(select, con); ad.Fill(ds); DataTable dt = ds.Tables[0]; con.Close(); con.Dispose(); dataGridView1.DataSource = dt; }
/// <summary> /// Método necesario para admitir el Diseñador, no se puede modificar /// el contenido del método con el editor de código. /// </summary> private void InitializeComponent() { System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); this.dataSet11 = new contratos_otec.DataSet1(); ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit(); // // oleDbSelectCommand1 // this.oleDbSelectCommand1.CommandText = @"SELECT '' AS monto_total, '' AS jorn_tdesc, '' AS emailp, '' AS eciv_tdescp, '' AS pais_tdescp, ' ' AS pers_tprofesionp, '' AS emailppc, '' AS eciv_tdescppc, '' AS pais_tdescppc, '' AS pers_tprofesionppc, 0 AS nro_informe, '' AS NOMBRE_INFORME, '' AS NRO_CONTRATO, '' AS DD_HOY, '' AS MM_HOY, '' AS YY_HOY, '' AS NOMBRE_INSTITUCION, '' AS PERIODO_ACADEMICO, '' AS RUT_INSTITUCION, '' AS NOMBRE_REPRESENTANTE, '' AS RUT_POSTULANTE, '' AS EDAD, '' AS NOMBRE_ALUMNO, '' AS CARRERA, '' AS RUT_CODEUDOR, '' AS NOMBRE_CODEUDOR, '' AS PROFESION, '' AS DIRECCION, '' AS DIRECCION_ALUMNO, '' AS CIUDAD, '' AS COMUNA, '' AS CIUDAD_ALUMNO, '' AS COMUNA_ALUMNO, '' AS TIPO_DOCUMENTO, '' AS DOCUMENTO, '' AS NOMBRE_BANCO, '' AS VALOR_DOCTO, '' AS NRO_DOCTO, '' AS FECHA_VENCIMIENTO, '' AS TOTAL_M, '' AS TOTAL_A, '' AS matricula, '' AS arancel, '' AS sede, '' AS comuna_sede"; this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = ((string)(configurationAppSettings.GetValue("cadenaConexion", typeof(string)))); // // oleDbDataAdapter1 // this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1; this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "Table", new System.Data.Common.DataColumnMapping[0]) }); // // dataSet11 // this.dataSet11.DataSetName = "DataSet1"; this.dataSet11.Locale = new System.Globalization.CultureInfo("es-CL"); this.dataSet11.Namespace = "http://www.tempuri.org/DataSet1.xsd"; this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit(); }
private DataTable readXlsx(String tableName, String wherecause, String fileName) { try { String appPath = System.IO.Path.GetDirectoryName(Application.ExecutablePath) + "\\menu\\"; String menu = "[" + tableName + "$]" + wherecause; MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Ace.OLEDB.12.0;Data Source='" + appPath + fileName + "';Extended Properties=Excel 12.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from " + menu, MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); MyConnection.Close(); return(DtSet.Tables[0]); } catch { String[] temp = { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" }; String s = ""; for (int i = 0; i < temp.Length; i++) { if (temp[i].Equals(DateTime.Now.DayOfWeek.ToString())) { s = i + 1 + temp[i]; } } fileName = s + "\\test.xlsx"; DataTable a = readXlsx(tableName, wherecause, fileName); return(a); } }
//GetDataTableToImport public DataTable GetDataTableToImport(string path_excel_file, string excel_worksheet) { string connectionstring = ""; DataTable data_tablename = new DataTable(); string file_type = System.IO.Path.GetExtension(path_excel_file).ToLower(); if (file_type.Trim() == ".xlsx") { try { connectionstring = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + path_excel_file + "; Extended Properties=\"Excel 12.0 Xml; HDR=YES\""; string query = "SELECT * FROM [" + excel_worksheet + "$]"; System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionstring); if (connection.State == ConnectionState.Closed) { connection.Open(); } System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, connection); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd); da.Fill(data_tablename); } catch (Exception) { } } return(data_tablename); }
public static List <List <string> > LoadXlsx(string filePath, string sheetName = "Sheet1") { // http://blog.163.com/china__xuhua/blog/static/19972316920111028105011136/ var strConn = string.Format("Provider=Microsoft.Ace.OleDb.12.0; data source={0}; Extended Properties='Excel 12.0; HDR=NO; IMEX=1'", filePath); var conn = new System.Data.OleDb.OleDbConnection(strConn); conn.Open(); var ds = new System.Data.DataSet(); var odda = new System.Data.OleDb.OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", sheetName), conn); odda.Fill(ds, sheetName); conn.Close(); var list = new List <List <string> >(); var colsCount = ds.Tables[0].Columns.Count; var rowsCount = ds.Tables[0].Rows.Count; for (int y = 0; y < rowsCount; y++) { var colList = new List <string>(); var row = ds.Tables[0].Rows[y]; for (int x = 0; x < colsCount; x++) { var col = row[x]; colList.Add(Convert.ToString(col)); } list.Add(colList); } return(list); }
private void rpt3DPie_ReportStart(object sender, EventArgs e) { // Set the data source in the chart. System.Data.DataTable dt = new System.Data.DataTable(); // Change the datasource path if required. string constr = Properties.Resources.ConnectionString; System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT DISTINCTROW Categories.CategoryName, "); sb.Append("Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSales, "); sb.Append("Employees.EmployeeID FROM Employees INNER JOIN ((Categories INNER JOIN Products "); sb.Append("ON Categories.CategoryID = Products.CategoryID) INNER JOIN "); sb.Append("(Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) "); sb.Append("ON Products.ProductID = [Order Details].ProductID) "); sb.Append("ON Employees.EmployeeID = Orders.EmployeeID "); sb.Append("WHERE (((Orders.ShippedDate) Between #1/1/1995# And #12/31/1995#)) "); sb.Append("GROUP BY Categories.CategoryName, Employees.EmployeeID HAVING Employees.EmployeeID = 1"); using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(constr)) { System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(sb.ToString(), conn); conn.Open(); adp.Fill(dt); conn.Close(); } ChartSalesCategories.DataSource = dt; // Set the direction of rotation. ChartSalesCategories.Series[0].Properties["Clockwise"] = boClockWise; }
public static DataTable ConvertExcelToDataTable(string FileName) { DataTable dtResult = null; int totalSheet = 0; //No of sheets on excel file using (System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';")) { objConn.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); System.Data.OleDb.OleDbDataAdapter oleda = new System.Data.OleDb.OleDbDataAdapter(); DataSet ds = new DataSet(); DataTable dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); string sheetName = string.Empty; if (dt != null) { var tempDataTable = (from dataRow in dt.AsEnumerable() where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase") select dataRow).CopyToDataTable(); dt = tempDataTable; totalSheet = dt.Rows.Count; sheetName = dt.Rows[0]["TABLE_NAME"].ToString(); } cmd.Connection = objConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; oleda = new System.Data.OleDb.OleDbDataAdapter(cmd); oleda.Fill(ds, "excelData"); dtResult = ds.Tables["excelData"]; objConn.Close(); return(dtResult); } }
/// <summary> /// Método necesario para admitir el Diseñador, no se puede modificar /// el contenido del método con el editor de código. /// </summary> private void InitializeComponent() { System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.datosInforme1 = new informe_1.datosInforme(); ((System.ComponentModel.ISupportInitialize)(this.datosInforme1)).BeginInit(); // // oleDbDataAdapter1 // this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1; this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "Table", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("NOMBRE", "NOMBRE") }) }); // // oleDbSelectCommand1 // this.oleDbSelectCommand1.CommandText = "SELECT \'\' AS NOMBRE FROM DUAL"; this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = ((string)(configurationAppSettings.GetValue("cadenaConexion", typeof(string)))); // // datosInforme1 // this.datosInforme1.DataSetName = "datosInforme"; this.datosInforme1.Locale = new System.Globalization.CultureInfo("es-ES"); this.datosInforme1.Namespace = "http://www.tempuri.org/datosInforme.xsd"; this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.datosInforme1)).EndInit(); }
private DataSet getDataSet(ref StringBuilder sbSql, string strConnection, out int intEffected) { intEffected = -1; try { conn = new System.Data.OleDb.OleDbConnection(strConnection); conn.Open(); } catch (Exception ex) { string msg = "Open Connection Exception / Check Connection String."; throw new Exception(msg, ex); } DataSet dataSet = new DataSet(); try { System.Data.OleDb.OleDbDataAdapter dbAdapter = new System.Data.OleDb.OleDbDataAdapter(); dbAdapter.SelectCommand = new System.Data.OleDb.OleDbCommand(sbSql.ToString(), conn); dbAdapter.FillLoadOption = LoadOption.PreserveChanges; intEffected = dbAdapter.Fill(dataSet); } catch (Exception ex) { throw (ex); } return(dataSet); }
public static DbDataAdapter CreateDataAdapter(DbCommand dbCommand, DbConnection connection) { DbDataAdapter adapter; //we can't construct an adapter directly //So let's run around the block 3 times, before potentially crashing if (connection is System.Data.SqlClient.SqlConnection) { adapter = new System.Data.SqlClient.SqlDataAdapter(dbCommand.CommandText, connection as SqlConnection); } else if (connection is System.Data.OleDb.OleDbConnection) { adapter = new System.Data.OleDb.OleDbDataAdapter(dbCommand.CommandText, connection as System.Data.OleDb.OleDbConnection); } else if (connection is System.Data.Odbc.OdbcConnection) { adapter = new System.Data.Odbc.OdbcDataAdapter(dbCommand.CommandText, connection as System.Data.Odbc.OdbcConnection); } else if (connection is System.Data.SqlServerCe.SqlCeConnection) { adapter = new System.Data.SqlServerCe.SqlCeDataAdapter(dbCommand.CommandText, connection as System.Data.SqlServerCe.SqlCeConnection); } //TODO: Add more DbConnection kinds as they become invented else { throw new Exception("[CreateDataAdapter] Unknown DbConnection type: " + connection.GetType().FullName); } return(adapter); }
public void LoadMe() { System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(); con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=acct.mdb;"; con.Open(); string quryString = "select * from AcctAR where id=" + this.arId + " and accountid=" + this.accountid; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(quryString, con); DataTable dt = new DataTable(); da.Fill(dt); foreach (DataRow row in dt.Rows) { datetb.Text = row["date"].ToString(); datetb.Enabled = false; timetb.Text = row["time"].ToString(); timetb.Enabled = false; companytb.Text = row["company"].ToString(); firstNametb.Text = row["firstname"].ToString(); lastnametb.Text = row["lastname"].ToString(); amounttb.Text = row["amount"].ToString(); invoiceNotb.Text = row["invoiceNo"].ToString(); description.Text = row["description"].ToString(); } }
protected void Application_Start(Object sender, EventArgs e) { System.Data.DataSet ds; System.Data.OleDb.OleDbConnection oleDbConnection1; System.Data.OleDb.OleDbDataAdapter daAttendees; System.Data.OleDb.OleDbDataAdapter daRooms; System.Data.OleDb.OleDbDataAdapter daEvents; oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Inetpub\wwwroot\PCSWebApp3\PCSWebApp3.mdb;Mode=ReadWrite|Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"; oleDbConnection1.Open(); ds = new DataSet(); daAttendees = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Attendees", oleDbConnection1); daRooms = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Rooms", oleDbConnection1); daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1); daAttendees.Fill(ds, "Attendees"); daRooms.Fill(ds, "Rooms"); daEvents.Fill(ds, "Events"); oleDbConnection1.Close(); Application["ds"] = ds; }
public void Button1_Click(System.Object sender, System.EventArgs e) { if (lblnewbal.Text == "") { } else { con.Open(); int total = int.Parse(lblnewbal.Text); System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter("select * from tblinfo", con); sql = "UPDATE tblinfo SET balance=\'" + total.ToString() + "\'" + " Where Firstname=\'" + lblname.Text + "\'"; cmd.CommandText = sql; cmd.Connection = con; cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); } if (MessageBox.Show("Do You Want to Continue Your transaction??", "Continue", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes) { Mainmenu.Default.Show(); } else { MessageBox.Show("Thank You Come Again"); Login_frm.Default.Show(); } this.Close(); }
//private void aButton1_Click(object sender, EventArgs e) //{ // if (openFileDialog1.ShowDialog() == DialogResult.OK) // { // Application.DoEvents(); // textBoxFile.Text = openFileDialog1.FileName; // textBoxFile.SelectionStart = textBoxFile.Text.Length; // ds = GetDataSet(textBoxFile.Text, MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_AwardUserSheetName), "1=1"); // if (ds != null) // { // DGVGameUser.AutoGenerateColumns = false; // DGVGameUser.DataSource = ds.Tables[0]; // labelUserCount.Text = "(" + ds.Tables[0].Rows.Count.ToString() + ")"; // } // else // { // DGVGameUser.Rows.Clear(); // labelUserCount.Text = "(0)"; // } // } //} public DataSet GetDataSet(string filename, string tname, string wherestr)//返回excel中不把第一行当做标题看待的数据集 { try { //OleDbDataAdapter read column donot support ko-kr ,first read the sheet string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0 Xml;HDR=yes\""; System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(sConnectionString); string sql_select_commands = "Select * from [" + tname + "$] where len([" + MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_UserNo) + "])>0 and len([" + MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_RoleNo) + "])>0 and len([" + MapLanguageManage.GetStringByMapLanguageConfig(MapLanguageConfig.Map_ZoneNo) + "])>0"; if (wherestr.Length != 0 && wherestr != "1=1") { sql_select_commands += " where " + wherestr; } System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(sql_select_commands, connection); DataSet ds = new DataSet(); adp.Fill(ds, "table_a"); adp.Dispose(); connection.Close(); return(ds); } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message.ToString()); return(null); } }
public static IDbDataAdapter CreateDataAdapter(EnumProviders provider) { IDbDataAdapter da; switch (provider) { case EnumProviders.SqlServer: da = new System.Data.SqlClient.SqlDataAdapter(); break; case EnumProviders.OleDb: da = new System.Data.OleDb.OleDbDataAdapter(); break; case EnumProviders.Odbc: da = new System.Data.Odbc.OdbcDataAdapter(); break; case EnumProviders.Oracle: throw new NotImplementedException("Provider not implemented"); break; default: da = new System.Data.SqlClient.SqlDataAdapter(); break; } return da; }
public static DbDataAdapter GetDbDataAdapter(DbConnection conn) { DbDataAdapter da = null; if (conn is System.Data.SqlClient.SqlConnection) { da = new System.Data.SqlClient.SqlDataAdapter(); } if (conn is System.Data.OleDb.OleDbConnection) { da = new System.Data.OleDb.OleDbDataAdapter(); } if (conn is Devart.Data.Oracle.OracleConnection) { da = new Devart.Data.Oracle.OracleDataAdapter(); } if (conn is System.Data.SQLite.SQLiteConnection) { da = new System.Data.SQLite.SQLiteDataAdapter(); } if (conn is MySql.Data.MySqlClient.MySqlConnection) { da = new MySql.Data.MySqlClient.MySqlDataAdapter(); } return(da); }
public static DataTable EseguiSQLDataTable(string sql, DbParameter[] param, DataColumn[] colonne = null) { var t = new DataTable(); if (colonne != null) { t.Columns.AddRange(colonne); } using (var cm = CreaCommandNoConnection(sql, param)) { if (DataBaseAttuale == DataBase.Access) { using (var a = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)cm)) a.Fill(t); } else if (DataBaseAttuale == DataBase.SQLite) #if __MonoCS__ { using (var a = new Mono.Data.Sqlite.SqliteDataAdapter((Mono.Data.Sqlite.SqliteCommand)cm)) a.Fill(t); } #else { using (var a = new System.Data.SQLite.SQLiteDataAdapter((System.Data.SQLite.SQLiteCommand)cm)) a.Fill(t); } #endif } return(t); }
public System.Data.DataTable ExecuteScalar(string sql, ADODB.Connection cn2) { rs.Open(sql, cn2, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, -1); System.Data.OleDb.OleDbDataAdapter ol = new System.Data.OleDb.OleDbDataAdapter(); System.Data.DataTable dt = new System.Data.DataTable(); ol.Fill(dt, rs); rs.Close(); return dt; }
public static 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 Button1_Click(object sender, EventArgs e) { var dt2 = new DataTable(); System.Data.OleDb.OleDbDataAdapter ol = new System.Data.OleDb.OleDbDataAdapter(); System.Data.DataTable dt = new System.Data.DataTable(); ol.Fill(dt2, product.selectTest2(test.cn)); gvProduct.DataSource = dt2; gvProduct.DataBind(); }
private void m_select() { this.oDS = new DataSet(); this.rq_sql = "SELECT * FROM TB_PERSONNE;"; //Requête SELECT Paramétrée this.oCMD = new System.Data.OleDb.OleDbCommand(this.rq_sql, this.oCNX); //Création d'un objet commande qui prend en paramètre la requête SQL et la l'objet de Connexion a la BDD. this.oDA = new System.Data.OleDb.OleDbDataAdapter(this.oCMD); //Création d'un objet DataAdapter qui prend en paramètre l'objet commande. (DataAdapter et l'interface réel entre l'application et la BDD. this.oDA.Fill(this.oDS, "personne"); //Execution de la requête SQL, si retournes des enregistrements, elle sont placé dans l'objet DataSet, dans la DataTable "Personne". this.dataGridView1.DataSource = this.oDS; //On attribue le modèle de mon DataSet au gridview pour afficher mes enregistrement. this.dataGridView1.DataMember = "personne"; //Mais bien définir la table a afficher, car le DataSet peut en contenir plusieur. }
//########################################################## //# Changer l'emplacement de la BDD # //########################################################## private void Form1_Load() { this.rq_sql = null; //Changer l'emplacement ou ce trouve votre BDD this.cnx = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Users\Orage\Documents\Visual Studio 2015\Projects\AppBaseDeDonnee\AppBaseDeDonnee\MyBDD.mdb"; this.oCNX = new System.Data.OleDb.OleDbConnection(this.cnx); this.oCMD = null; this.oDA = null; this.oDS = new DataSet(); this.m_select(); }
public DataSet Method1() { string s = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Northwind1.mdb") + ";Persist Security Info=False"; string z = "select * from Клиенты"; System.Data.OleDb.OleDbConnection olcon = new System.Data.OleDb.OleDbConnection(s); System.Data.OleDb.OleDbCommand olcom = new System.Data.OleDb.OleDbCommand(z, olcon); System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter(olcom); System.Data.DataSet DS = new System.Data.DataSet(); DA.Fill(DS); return DS; }
public DataTable GetSheetDataForInsert(string qrySecondPart) { var conn = new System.Data.OleDb.OleDbConnection( String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';", _path)); DataSet ds = new DataSet(); var adapter = new System.Data.OleDb.OleDbDataAdapter( qrySecondPart, conn); adapter.Fill(ds); return ds.Tables[0]; }
public static DataTable GetDataTable(string strFileName) { ADODB.Connection oConn = new ADODB.Connection(); oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\";", "", "", 0); string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]"; ADODB.Recordset rs = new ADODB.Recordset(); System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(); DataTable dt = new DataTable(); rs.Open(strQuery, "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\";", ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1); adapter.Fill(dt, rs); return dt; }
public System.Data.DataTable Read(string filename) { var excelDataAdapter = new System.Data.OleDb.OleDbDataAdapter(); _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + "; Extended Properties = Excel 8.0;"; var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString); excelConn.Open(); var dtProtocol = new System.Data.DataTable { TableName = "Protocol" }; var excelCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [протокол плавки$]", excelConn); excelDataAdapter.SelectCommand = excelCommand; excelDataAdapter.Fill(dtProtocol); excelConn.Close(); return dtProtocol; }
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.dgNotes = new System.Windows.Forms.DataGrid(); this.daJoin = new System.Data.OleDb.OleDbDataAdapter(); this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); ((System.ComponentModel.ISupportInitialize)(this.dgNotes)).BeginInit(); this.SuspendLayout(); // // dgNotes // this.dgNotes.DataMember = ""; this.dgNotes.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dgNotes.Location = new System.Drawing.Point(16, 16); this.dgNotes.Name = "dgNotes"; this.dgNotes.Size = new System.Drawing.Size(744, 336); this.dgNotes.TabIndex = 0; // // daJoin // this.daJoin.SelectCommand = this.oleDbSelectCommand1; this.daJoin.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "Notes", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("primulatext", "primulatext"), new System.Data.Common.DataColumnMapping("visitnote", "visitnote"), new System.Data.Common.DataColumnMapping("noteid", "noteid"), new System.Data.Common.DataColumnMapping("primula", "primula")})}); this.daJoin.RowUpdated += new System.Data.OleDb.OleDbRowUpdatedEventHandler(this.oleDbDataAdapter1_RowUpdated); // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\Documents and Settings\Anders Ruberg\Mina dokument\Visual Studio Projects\RehabLight\bin\Debug\rehab.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"; // // oleDbSelectCommand1 // this.oleDbSelectCommand1.CommandText = "SELECT Charges.primulatext, Notes.visitnote, Notes.noteid, Notes.primula FROM (No" + "tes INNER JOIN Charges ON Charges.chargeid = Notes.chargeid) WHERE (Notes.primul" + "a = FALSE) AND (Notes.visitnote = TRUE) AND (MID([Notes.visitdatetime], 1, 4) = " + "\'2007\')"; this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // FilteredNotes // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(768, 390); this.Controls.Add(this.dgNotes); this.Name = "FilteredNotes"; this.Text = "FilteredNotes"; ((System.ComponentModel.ISupportInitialize)(this.dgNotes)).EndInit(); this.ResumeLayout(false); }
/// <summary> /// M�todo necesario para admitir el Dise�ador, no se puede modificar /// el contenido del m�todo con el editor de c�digo. /// </summary> private void InitializeComponent() { System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.contrato1 = new reporte_contrato.contrato(); ((System.ComponentModel.ISupportInitialize)(this.contrato1)).BeginInit(); // // oleDbDataAdapter1 // this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1; this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "contrato", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("NOMBRE_INSTITUCION", "NOMBRE_INSTITUCION"), new System.Data.Common.DataColumnMapping("RUT_INSTITUCION", "RUT_INSTITUCION"), new System.Data.Common.DataColumnMapping("RUT_POSTULANTE", "RUT_POSTULANTE"), new System.Data.Common.DataColumnMapping("EDAD", "EDAD"), new System.Data.Common.DataColumnMapping("NOMBRE_ALUMNO", "NOMBRE_ALUMNO"), new System.Data.Common.DataColumnMapping("CARRERA", "CARRERA"), new System.Data.Common.DataColumnMapping("RUT_CODEUDOR", "RUT_CODEUDOR"), new System.Data.Common.DataColumnMapping("NOMBRE_CODEUDOR", "NOMBRE_CODEUDOR"), new System.Data.Common.DataColumnMapping("DIRECCION", "DIRECCION"), new System.Data.Common.DataColumnMapping("CIUDAD", "CIUDAD"), new System.Data.Common.DataColumnMapping("COMUNA", "COMUNA"), new System.Data.Common.DataColumnMapping("TIPO_DOCUMENTO", "TIPO_DOCUMENTO"), new System.Data.Common.DataColumnMapping("DOCUMENTO", "DOCUMENTO"), new System.Data.Common.DataColumnMapping("NOMBRE_BANCO", "NOMBRE_BANCO"), new System.Data.Common.DataColumnMapping("VALOR_DOCTO", "VALOR_DOCTO"), new System.Data.Common.DataColumnMapping("FECHA_VENCIMIENTO", "FECHA_VENCIMIENTO"), new System.Data.Common.DataColumnMapping("TOTAL_M", "TOTAL_M"), new System.Data.Common.DataColumnMapping("TOTAL_A", "TOTAL_A")})}); // // oleDbSelectCommand1 // this.oleDbSelectCommand1.CommandText = @"SELECT '' AS NOMBRE_INSTITUCION, '' AS RUT_INSTITUCION, '' AS RUT_POSTULANTE, '' AS EDAD, '' AS NOMBRE_ALUMNO, '' AS CARRERA, '' AS RUT_CODEUDOR, '' AS NOMBRE_CODEUDOR, '' AS DIRECCION, '' AS CIUDAD, '' AS COMUNA, '' AS TIPO_DOCUMENTO, '' AS DOCUMENTO, '' AS NOMBRE_BANCO, '' AS VALOR_DOCTO, '' AS FECHA_VENCIMIENTO, '' AS TOTAL_M, '' AS TOTAL_A FROM DUAL"; this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = ((string)(configurationAppSettings.GetValue("cadenaConexion", typeof(string)))); // // contrato1 // this.contrato1.DataSetName = "contrato"; this.contrato1.Locale = new System.Globalization.CultureInfo("es-ES"); this.contrato1.Namespace = "http://www.tempuri.org/contrato.xsd"; this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.contrato1)).EndInit(); }
public static DataTable GetDataTableFromCSV(string strFileName) { try { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\""); conn.Open(); string strQuery = "Select * from [" + System.IO.Path.GetFileName(strFileName) + "]"; System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn); System.Data.DataSet ds = new System.Data.DataSet(); da.Fill(ds); return ds.Tables[0]; } catch (Exception ex) { } return new DataTable(); }
public void ConvertExcelToCsv(int worksheetNumber = 1) { if (!System.IO.File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath); if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile); // connection string var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath); var cnn = new System.Data.OleDb.OleDbConnection(cnnStr); // get schema, then data var dt = new DataTable(); try { cnn.Open(); var schemaTable = cnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet"); string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", ""); string sql = String.Format("select * from [{0}]", worksheet); var da = new System.Data.OleDb.OleDbDataAdapter(sql, cnn); da.Fill(dt); } catch (Exception e) { Console.WriteLine(e.ToString()); throw e; } finally { // free resources cnn.Close(); } // write out CSV data using (var wtr = new StreamWriter(csvOutputFile)) { foreach (DataRow row in dt.Rows) { bool firstLine = true; foreach (DataColumn col in dt.Columns) { if (!firstLine) { wtr.Write(","); } else { firstLine = false; } var data = row[col.ColumnName].ToString().Replace("\"", "\"\""); wtr.Write(String.Format("\"{0}\"", data)); } wtr.WriteLine(); } } }
private DataSet GetMeSomeDataIntoDataSet(string sql) { System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Jason\Dropbox\Devry\CIS336\SimpleQueryAnalyzer\SimpleQueryAnalyzer\northwind.mdb;"; conn.Open(); System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand(); comm.Connection = conn; comm.CommandText = sql; System.Data.DataSet ds = new DataSet(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(); da.SelectCommand = comm; da.Fill(ds); return ds; }
private static DataSet CreateDataSet() { DataSet ds = new DataSet(); IDbConnection connection = new System.Data.OleDb.OleDbConnection(); connection.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Database Password=;Data Source=""NWind.mdb"";Password=;Jet OLEDB:Engine Type=3;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"; IDbDataAdapter adapterSender = new System.Data.OleDb.OleDbDataAdapter(); IDbCommand oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); oleDbSelectCommand1.Connection = connection; adapterSender.SelectCommand = oleDbSelectCommand1; oleDbSelectCommand1.CommandText = "SELECT TOP 10 OrderID, CustomerID , Freight, OrderDate, ShipVia FROM Orders"; //oleDbSelectCommand1.CommandText = "SELECT * FROM Orders"; adapterSender.Fill(ds); ds.Tables[0].TableName = "Orders"; return ds; }
internal DataTable ExecuteReader(System.Data.OleDb.OleDbCommand cmd) { try { cmd.Connection = conn; conn.Open(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); return dt; } finally { conn.Close(); } }
//載入Excel檔案中成員資料 public static DataTable LoadExcelFile(string strFilePath, string strSheetName) { System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(); cn.ConnectionString = "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source = " + strFilePath + "; Extended Properties ='Excel 8.0;'"; cn.Open(); System.Data.OleDb.OleDbDataAdapter da; da = new System.Data.OleDb.OleDbDataAdapter("Select * from [" + strSheetName + "$]", cn); DataTable dt = new DataTable(); try { da.Fill(dt); } finally { da.Dispose(); } return dt; }
private void button1_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='c:\\csharp.net-informations.xls';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); dataGridView1.DataSource = DtSet.Tables[0]; MyConnection.Close(); } catch (Exception ex) { MessageBox.Show (ex.ToString()); } }
private void Excel() { DataSet ds = new DataSet(); string cnnString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", @"C:\IHMHR\BaseConhecimento.xlsx");//@"C:\Users\Martinelli\Desktop\N8\Projeto Base de conhecimento\Base de Conhecimento\Base de Conhecimento\bin\Debug\BaseConhecimento.xlsx"); string isql = "SELECT TOP 50 Chamado AS [Numero do Chamado],Desc_Chamado AS [Descrição do Chamado],Desc_detalhada AS [Detalhes do Chamado],Causa,resolucao AS [Resolução],sintoma AS [Sintoma],Resposta_cliente AS [Resposta ao Cliente],[Solucionador por] AS [Analista responsável] FROM [{0}$] WHERE chamado IS NOT NULL AND resolucao IS NOT NULL AND Resposta_cliente IS NOT NULL AND " + FormatarLike(MyProperty, false) + " ORDER BY Chamado DESC"; System.Data.OleDb.OleDbConnection cnn = new System.Data.OleDb.OleDbConnection(cnnString); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(String.Format(isql, "Plan1"), cnn); comando = String.Format(isql, "Plan1"); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; //lblQtdRegistros.Text = dataGridView1.RowCount.ToString(); if (dataGridView1.RowCount <= 0) { MessageBox.Show("Nenhum dado encontrado"); this.Close(); FrmMain mm = new FrmMain(); mm.Show(); } }
private System.Data.DataSet GetProductOrderDataSet() { //retrieve the connection string from the ConnectionString Key in Web.Config //string connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Talha\Documents\Database2.accdb;Persist Security Info=False;"; //create a new OleDB connection System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString); //pass the Select statement and connection information to the initializxation constructor for the OleDBDataAdapter System.Data.OleDb.OleDbDataAdapter myDataAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT Products.ProductName, Products.ProductCode, OrderDetails.Quantity FROM (Products INNER JOIN OrderDetails ON Products.ID = OrderDetails.ProductID)", conn); //Create a new dataset with a table : CLIENTS System.Data.DataSet myDataSet = new System.Data.DataSet("ProductOrder"); //Fill the dataset and table with the data retrieved by the select command myDataAdapter.Fill(myDataSet, "ProductOrder"); //return the new dataset created return myDataSet; }
private DataSet GetClientDataSet() { //retrieve the connection string from the ConnectionString Key in Web.Config //string connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Talha\Documents\Database2.accdb;Persist Security Info=False;"; //create a new OleDB connection System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString); //pass the Select statement and connection information to the initializxation constructor for the OleDBDataAdapter System.Data.OleDb.OleDbDataAdapter myDataAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT Email As pictID FROM Employees", conn); //Create a new dataset with a table : CLIENTS System.Data.DataSet myDataSet = new System.Data.DataSet("Users"); //Fill the dataset and table with the data retrieved by the select command myDataAdapter.Fill(myDataSet, "Users"); //return the new dataset created return myDataSet; }
public static DbDataAdapter CreateDataAdapter(DbCommand dbCommand, DbConnection connection) { DbDataAdapter adapter; //we can't construct an adapter directly //So let's run around the block 3 times, before potentially crashing if (connection is System.Data.SqlClient.SqlConnection) adapter = new System.Data.SqlClient.SqlDataAdapter(dbCommand.CommandText, connection as SqlConnection); else if (connection is System.Data.OleDb.OleDbConnection) adapter = new System.Data.OleDb.OleDbDataAdapter(dbCommand.CommandText, connection as System.Data.OleDb.OleDbConnection); else if (connection is System.Data.Odbc.OdbcConnection) adapter = new System.Data.Odbc.OdbcDataAdapter(dbCommand.CommandText, connection as System.Data.Odbc.OdbcConnection); else if (connection is System.Data.SqlServerCe.SqlCeConnection) adapter = new System.Data.SqlServerCe.SqlCeDataAdapter(dbCommand.CommandText, connection as System.Data.SqlServerCe.SqlCeConnection); //TODO: Add more DbConnection kinds as they become invented else { throw new Exception("[CreateDataAdapter] Unknown DbConnection type: " + connection.GetType().FullName); } return adapter; }