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