в 15:31, 26 мая 2010

2010-05-26T15:31:19Z

Admin: 1 версия

2010-05-26T11:42:30Z

1 версия

Новая страница

==Between certain dates==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class ExtractInfo {
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");
OleDbCommand cmd = con.CreateCommand();

cmd.rumandText = "SELECT OrderNumber FROM Orders "
+ "WHERE OrderDate BETWEEN #4/1/99# AND #4/30/99#";
OleDbDataReader reader = cmd.ExecuteReader();
Console.WriteLine();
Console.WriteLine
(" Order numbers of orders from 4/1/99 to 4/30/99");
while(reader.Read())
Console.WriteLine(reader.GetString(0));
reader.Close();

con.Close();
}
}

</source>

==Create table and populate data==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class Create {
public static void Main () {
String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
OleDbConnection con = new OleDbConnection(connect);
con.Open();
OleDbCommand cmd = con.CreateCommand();
cmd.rumandText = "CREATE TABLE Customer (CustomerID "
+ "CHAR(4), CustomerName VARCHAR(25), Address "
+ "VARCHAR(25), BalanceDue DECIMAL)";
cmd.ExecuteNonQuery();
cmd.rumandText = "INSERT INTO Customer VALUES (1234,"F F","22 First St.",67.00)";
cmd.ExecuteNonQuery();
cmd.rumandText = "INSERT INTO Customer VALUES (5678,"D D","33 Second St.",130.95)";
cmd.ExecuteNonQuery();
cmd.rumandText = "INSERT INTO Customer VALUES (4321,"M M","44 Third St.",0)";
cmd.ExecuteNonQuery();
cmd.rumandText = "INSERT INTO Customer VALUES (8765,"C C","55 Fourth St.", 0)";
cmd.ExecuteNonQuery();
con.Close();
}
}

</source>

==Delete database records through OleDbCommand and verify the results==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class ExtractInfo {
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");
OleDbCommand cmd = con.CreateCommand();

cmd.rumandText ="DELETE FROM Employee WHERE ID = 01";
cmd.ExecuteNonQuery();
cmd.rumandText = "SELECT First_Name FROM Employee";
OleDbDataReader reader = cmd.ExecuteReader();
Console.WriteLine();
while(reader.Read())
Console.WriteLine(reader.GetString(0));
reader.Close();
con.Close();
}
}

</source>

==Execute aggregate function: sum==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {
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 database");
OleDbCommand command = con.CreateCommand();
command.rumandText ="SELECT SUM(salary) FROM Employee";
Console.WriteLine("Sum of salary: {0:C}",(decimal)command.ExecuteScalar());

con.Close();
}
}

</source>

==Get column average==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {
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 database");
OleDbCommand command = con.CreateCommand();
command.rumandText = "SELECT AVG(Salary) FROM Employee";
Console.WriteLine("Average of salary: {0:C}",(decimal)command.ExecuteScalar());

con.Close();
}
}

</source>

==Get count value from a select query==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {
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 database");
OleDbCommand command = con.CreateCommand();
command.rumandText = "SELECT COUNT(*) FROM Employee";
Console.WriteLine("Number of Employee rows: {0}",(int)command.ExecuteScalar());
con.Close();
}
}

</source>

==Get max column value==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {
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 database");
OleDbCommand command = con.CreateCommand();
command.rumandText ="SELECT MAX(Salary) FROM Employee";
Console.WriteLine("Max of salary: {0:C}",(decimal)command.ExecuteScalar());
con.Close();
}
}

</source>

==How to execute a TableDirect command==



<source lang="csharp">

using System;
using System.Data;
using System.Data.OleDb;
class ExecuteTableDirect
{
public static void Main()
{
OleDbConnection myOleDbConnection =new OleDbConnection("provider=sqloledb;server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
myOleDbCommand.rumandType = CommandType.TableDirect;
myOleDbCommand.rumandText = "Employee";
myOleDbConnection.Open();
OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();
for (int count = 1; count <= 2; count++)
{
myOleDbDataReader.Read();
Console.WriteLine("myOleDbDataReader[\" ID\"] = " +
myOleDbDataReader["ID"]);
Console.WriteLine("myOleDbDataReader[\" FirstName\"] = " +
myOleDbDataReader["FirstName"]);
Console.WriteLine("myOleDbDataReader[\" LastName\"] = " +
myOleDbDataReader["LastName"]);
}
myOleDbDataReader.Close();
myOleDbConnection.Close();
}
}

</source>

==Pass parameter to OleDbCommand==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class Prepare {
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 database");
OleDbCommand cmd1 = con.CreateCommand();
cmd1.rumandText = "SELECT ID FROM Employee "
+ "WHERE id BETWEEN ? AND ?";
OleDbParameter p1 = new OleDbParameter();
OleDbParameter p2 = new OleDbParameter();
cmd1.Parameters.Add(p1);
cmd1.Parameters.Add(p2);
p1.Value = "01";
p2.Value = "03";
OleDbDataReader reader = cmd1.ExecuteReader();
while(reader.Read())
Console.WriteLine("{0}", reader.GetInt32(0));
reader.Close();
con.Close();
}
}

</source>

← Предыдущая	Версия 15:31, 26 мая 2010
(нет различий)

Csharp/C Sharp/Database ADO.net/OleDbCommand - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия