Csharp/C Sharp/Database ADO.net/Access
Содержание
Connects to a Microsoft Access database
<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class Connect {
public static void Main () {
String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
OleDbConnection con = new OleDbConnection(connect);
con.Open();
Console.WriteLine("Made the connection to the Sales database");
con.Close();
}
}
</source>
Connect to an Access database file
<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; class AccessConnect {
static void Main()
{
string connString = @"provider = microsoft.jet.oledb.4.0;data source = Employee.mdb;";
OleDbConnection conn = new OleDbConnection(connString);
try
{
conn.Open();
Console.WriteLine("Connection opened.");
Console.WriteLine("Connection Properties:");
Console.WriteLine("\tConnection String: {0}", conn.ConnectionString);
Console.WriteLine("\tDatabase: {0}",conn.Database);
Console.WriteLine("\tDataSource: {0}",conn.DataSource);
Console.WriteLine("\tServerVersion: {0}", conn.ServerVersion);
Console.WriteLine("\tState: {0}", conn.State);
} catch (OleDbException e) {
Console.WriteLine("Error: " + e);
} finally {
conn.Close();
Console.WriteLine("Connection closed.");
}
}
}
</source>
How to use an OleDbConnection object to connect to an Access database
<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; class OleDbConnectionAccess {
public static void Main() {
string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=.\\Northwind.mdb";
OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
myOleDbCommand.rumandText =
"SELECT CustomerID, CompanyName, ContactName, Address "+
"FROM Customers "+
"WHERE CustomerID = "ALFKI"";
myOleDbConnection.Open();
OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();
myOleDbDataReader.Read();
Console.WriteLine("myOleDbDataReader[\" CustomerID\"] = "+ myOleDbDataReader["CustomerID"]);
Console.WriteLine("myOleDbDataReader[\" CompanyName\"] = "+ myOleDbDataReader["CompanyName"]);
Console.WriteLine("myOleDbDataReader[\" ContactName\"] = "+ myOleDbDataReader["ContactName"]);
Console.WriteLine("myOleDbDataReader[\" Address\"] = "+ myOleDbDataReader["Address"]);
myOleDbDataReader.Close();
myOleDbConnection.Close();
}
}
</source>
Reading Access Data
<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; using System.Collections.Generic; using System.Text; class Program {
static void Main(string[] args) {
OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb");
thisConnection.Open();
OleDbCommand thisCommand = thisConnection.CreateCommand();
thisCommand.rumandText = "SELECT CustomerID, CompanyName FROM Customers";
OleDbDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read()) {
Console.WriteLine("\t{0}\t{1}",thisReader["CustomerID"], thisReader["CompanyName"]);
}
thisReader.Close();
thisConnection.Close();
}
}
</source>
Read query result data from Access database
<source lang="csharp"> using System; using System.Data; using System.Data.OleDb;
class CommandOleDbQuery
{
static void Main()
{
OleDbConnection thisConnection = new OleDbConnection("provider = microsoft.jet.oledb.4.0;data source = Employee.mdb;");
OleDbCommand thisCommand = new OleDbCommand("SELECT ID, FirstName FROM Employee",thisConnection);
try
{
thisConnection.Open();
OleDbDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read()) {
Console.WriteLine("Product ID and Name: {0} {1}",
thisReader.GetValue(0),
thisReader.GetValue(1));
}
}
catch (OleDbException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
thisConnection.Close();
Console.WriteLine("Connection Closed.");
}
}
}
</source>
Use OdbcCommand to read data in Access database and fill the DataGrid
<source lang="csharp"> using System; using System.Diagnostics; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.Odbc;
public class Form1 : System.Windows.Forms.Form {
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button2;
public Form1() {
this.button1 = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.button2 = new System.Windows.Forms.Button();
((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
this.button1.Location = new System.Drawing.Point(8, 240);
this.button1.Size = new System.Drawing.Size(104, 32);
this.button1.Text = "Get Data From Biblio";
this.button1.Click += new System.EventHandler(this.button1_Click);
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(8, 8);
this.dataGrid1.Size = new System.Drawing.Size(280, 224);
this.button2.Location = new System.Drawing.Point(120, 240);
this.button2.Size = new System.Drawing.Size(152, 32);
this.button2.Text = "Get Data From Access Northwind";
this.button2.Click += new System.EventHandler(this.button2_Click);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.button2,
this.dataGrid1,
this.button1});
((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
[STAThread]
static void Main() {
Application.Run(new Form1());
}
private OdbcConnection cn;
private void button1_Click(object sender, System.EventArgs e) {
try {
cn = new OdbcConnection("DRIVER={SQL Server};SERVER=.;uid=admin;pwd=pw;database=biblio");
cn.Open();
OdbcCommand sc = new OdbcCommand("SELECT title, price FROM titles WHERE title LIKE "Hi%"", cn);
OdbcDataAdapter da = new OdbcDataAdapter(sc);
DataSet ds = new DataSet("TitlesDS");
da.Fill(ds);
dataGrid1.DataSource = ds.Tables[0];
} catch (Exception ex) {
Debug.WriteLine(ex.ToString());
} finally {
cn.Close();
}
}
private void button2_Click(object sender, System.EventArgs e) {
try {
cn = new OdbcConnection(@"DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\NorthWind.mdb");
cn.Open();
OdbcCommand sc = new OdbcCommand("SELECT * from Employees", cn);
OdbcDataAdapter da = new OdbcDataAdapter(sc);
DataSet ds = new DataSet("TitlesDS");
da.Fill(ds);
dataGrid1.DataSource = ds.Tables[0];
} catch (Exception ex) {
MessageBox.Show("You probably need to change the file path to NorthWind.MDB used in the OdbcConnection Constructor\n or move a copy of the NorthWind.mdb to c:\\");
Debug.WriteLine(ex.ToString());
} finally {
cn.Close();
}
}
}
</source>
