.NET Framework SDK上有好些这样的例子。比如:
SqlClient
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasicPublic Class Sample Public Shared Sub Main()
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind") Dim catCMD As SqlCommand = nwindConn.CreateCommand()
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories" nwindConn.Open() Dim myReader As SqlDataReader = catCMD.ExecuteReader() Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop myReader.Close()
nwindConn.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;class Sample
{
public static void Main()
{
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); SqlCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"; nwindConn.Open(); SqlDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
} myReader.Close();
nwindConn.Close();
}
}
OleDb
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasicPublic Class Sample Public Shared Sub Main()
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind") Dim catCMD As OleDbCommand = nwindConn.CreateCommand()
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories" nwindConn.Open() Dim myReader As OleDbDataReader = catCMD.ExecuteReader() Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop myReader.Close()
nwindConn.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.OleDb;class Sample
{
public static void Main()
{
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); OleDbCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"; nwindConn.Open(); OleDbDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
} myReader.Close();
nwindConn.Close();
}
}
SqlClient
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasicPublic Class Sample Public Shared Sub Main()
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind") Dim catCMD As SqlCommand = nwindConn.CreateCommand()
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories" nwindConn.Open() Dim myReader As SqlDataReader = catCMD.ExecuteReader() Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop myReader.Close()
nwindConn.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;class Sample
{
public static void Main()
{
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); SqlCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"; nwindConn.Open(); SqlDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
} myReader.Close();
nwindConn.Close();
}
}
OleDb
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasicPublic Class Sample Public Shared Sub Main()
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind") Dim catCMD As OleDbCommand = nwindConn.CreateCommand()
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories" nwindConn.Open() Dim myReader As OleDbDataReader = catCMD.ExecuteReader() Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop myReader.Close()
nwindConn.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.OleDb;class Sample
{
public static void Main()
{
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); OleDbCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"; nwindConn.Open(); OleDbDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
} myReader.Close();
nwindConn.Close();
}
}
using System;
using System.Data;
using System.Data.OleDb;
namespace AdoExecuteLib
{
//OleDb执行类
public class OleDbExecute
{
//执行存储过程,用out型参数返回DataSet对象
public void ExecuteSP(out DataSet MyDataSet,string TableName,OleDbConnection MyConnection,
string StoredProcName,params object[] ParamsArray)
{
try
{
OleDbDataAdapter da=new OleDbDataAdapter();
da.SelectCommand=new OleDbCommand(StoredProcName,MyConnection);
da.SelectCommand.CommandType=CommandType.StoredProcedure;
for (int i=0;i<ParamsArray.Length;i++)
{
da.SelectCommand.Parameters.Add("param"+i,ParamsArray[i]);
}
MyConnection.Open();
MyDataSet=new DataSet();
da.SelectCommand.Prepare();
da.Fill(MyDataSet,TableName);
MyConnection.Close();
}
catch
{
throw;
}
}
//执行存储过程,用out型参数返回DataTable对象
public void ExecuteSP(out DataTable MyDataTable,OleDbConnection MyConnection,
string StoredProcName,params object[] ParamsArray)
{
try
{
DataSet ds;
this.ExecuteSP(out ds,"a",MyConnection,StoredProcName,ParamsArray);
MyDataTable=ds.Tables["a"];
}
catch
{
throw;
}
}
//执行不返回结果集的SQL语句,返回执行影响的行数
public void ExecuteSQL(OleDbConnection MyConnection,string SqlStatment,out int AffectedRows)
{
try
{
OleDbCommand cmd=new OleDbCommand(SqlStatment,MyConnection);
MyConnection.Open();
AffectedRows=cmd.ExecuteNonQuery();
MyConnection.Close();
}
catch
{
throw;
}
}
//执行不返回结果集的SQL语句
public void ExecuteSQL(OleDbConnection MyConnection,string SqlStatment)
{
try
{
int AffectedRows;
this.ExecuteSQL(MyConnection,SqlStatment,out AffectedRows);
}
catch
{
throw;
}
}
//执行返回DataSet对象的SQL语句
public void ExecuteSQL(out DataSet MyDataSet,string TableName,OleDbConnection MyConnection,string SqlStatment)
{
try
{
OleDbDataAdapter da=new OleDbDataAdapter(SqlStatment,MyConnection);
MyDataSet=new DataSet();
MyConnection.Open();
da.Fill(MyDataSet,TableName);
MyConnection.Close();
}
catch
{
throw;
}
}
//执行返回DataTable对象的SQL语句
public void ExecuteSQL(out DataTable MyDataTable,OleDbConnection MyConnection,string SqlStatment)
{
try
{
DataSet ds;
this.ExecuteSQL(out ds,"a",MyConnection,SqlStatment);
MyDataTable=ds.Tables["a"];
}
catch
{
throw;
}
}
}
}
{
using System;
using System.Data;
using System.Data.SQL; /// <summary>
/// PURPOSE: A generic database access class for SQL server 7.0 / 2000
/// FILEREV: 9/1/2001 - initial version
/// CODE BY: Whiten Shen
/// Mail to: [email protected]
/// OICQ : 6671258
/// </summary>
public class SQLService
{
/// <summary>
/// A string variable to store connection string
/// </summary>
private string m_ConnectionString;
/// <summary>
/// A SQlConnection object for database access
/// </summary>
private SQLConnection m_Connection; /// <summary>
/// A string variable to store SQL Message if necessary
/// </summary>
private string m_Message = "";
/// <summary>
/// Property to get/set connection string
/// Connection string can also be set via the overloaded constructor
/// </summary>
public string ConnectionString
{
get
{
return this.m_ConnectionString;
}
set
{
this.m_ConnectionString = value;
}
} /// <summary>
/// Property to get/set SQL connection
/// SQL Connection can also be set
/// </summary>
public SQLConnection Connection
{
get
{
return this.m_Connection;
}
set
{
this.m_Connection = value;
}
} /// <summary>
/// Property to get SQL Message
/// SQL Message can only be get if necessary
/// </summary>
public string Message
{
get
{
return this.m_Message;
}
} /// <summary>
/// Default constructor
/// </summary>
public SQLService()
{
} /// <summary>
/// An overloaded constructor for setting connection string
/// </summary>
/// <param name="ConnectionString">Example: "server=WHITEN;uid=sa;pwd=admin;database=WhitenShen"</param>
public SQLService(string ConnectionString)
{
this.m_ConnectionString = ConnectionString;
}
/// <summary>
/// Opens a connection, throws an exception if errors occur.
/// </summary>
/// <returns>System.Data.Sql.SqlConnection</returns>
public SQLConnection StartSQLService()
{
if(this.m_Connection == null)
{
if(this.m_ConnectionString == null || this.m_ConnectionString.Length == 0)
{
this.m_ConnectionString = "server=WHITEN;uid=sa;password=admin;database=WhitenShen";
} try
{
this.m_Connection = new SQLConnection(this.m_ConnectionString);
this.m_Connection.Open();
}
catch(System.Exception e)
{
throw e;
}
}
else if(this.m_Connection.State.ToString() != "1")
{
this.m_Connection.ConnectionString = this.m_ConnectionString; try
{
this.m_Connection.Open();
}
catch(Exception e)
{
throw e;
}
} return this.m_Connection;
}
/// <summary>
/// Get dataset by sql selection statement or stored procedure
/// </summary>
/// <param name="QueryString">Selection statement or stored procedure name with parameters</param>
/// <param name="TableName">A table name such as "Customers"</param>
/// <returns>A DataSet contains data or null</returns>
public DataSet SelectSQLData(string QueryString, string TableName, bool MustClose)
{
DataSet ds = new DataSet();
SQLDataSetCommand dsComm = new SQLDataSetCommand(); try
{
dsComm.SelectCommand = new SQLCommand(QueryString, this.StartSQLService());
if(TableName.Trim().Length > 0)
{
dsComm.FillDataSet(ds, TableName);
}
else
{
dsComm.FillDataSet(ds);
}
}
catch(System.Exception e)
{
throw e;
}
finally
{
if(MustClose == true)
{
this.CloseSQLService();
}
} return ds;
} /// <summary>
/// An overloaded methods without MustClose, the connection will remain open
/// </summary>
/// <param name="QueryString">See method being overloaded</param>
/// <returns>See method being overloaded</returns>
public DataSet SelectSQLData(string QueryString, string TableName)
{
return this.SelectSQLData(QueryString, TableName, false);
} /// <summary>
/// An overloaded methods without table name
/// </summary>
/// <param name="QueryString">See method being overloaded</param>
/// <returns>See method being overloaded</returns>
public DataSet SelectSQLData(string QueryString)
{
return this.SelectSQLData(QueryString , "", false);
}
/// <summary>
/// Insert data using sql statement or call a store procedure
/// </summary>
/// <param name="InsertString">a sql statement or stored procedure name with parameters</param>
public void InsertSQLData(string InsertString, bool MustClose)
{
SQLDataSetCommand dsComm = new SQLDataSetCommand(); try
{
dsComm.InsertCommand = new SQLCommand(InsertString, this.StartSQLService());
dsComm.InsertCommand.ExecuteNonQuery();
}
catch(Exception e)
{
throw e;
}
finally
{
if(MustClose == true)
{
this.CloseSQLService();
}
}
} /// <summary>
/// An overloaded function
/// </summary>
/// <param name="InsertString"></param>
/// <param name="MustClose"></param>
public void InsertSQLData(string InsertString)
{
this.InsertSQLData(InsertString, false);
}
/// <summary>
/// A function to delete date
/// </summary>
/// <param name="DeleteString">sql detele statement or stored procedure</param>
public void DeleteSQLData(string DeleteString, bool MustClose)
{
SQLDataSetCommand dsComm = new SQLDataSetCommand(); try
{
dsComm.DeleteCommand = new SQLCommand(DeleteString, this.StartSQLService());
dsComm.DeleteCommand.ExecuteNonQuery();
}
catch(Exception e)
{
throw e;
}
finally
{
if(MustClose == true)
{
this.CloseSQLService();
}
}
} /// <summary>
/// An overloaded function
/// </summary>
/// <param name="DeleteString"></param>
/// <param name="MustClose"></param>
public void DeleteSQLData(string DeleteString)
{
this.DeleteSQLData(DeleteString, false);
}
/// <summary>
/// To update sql database using sql statement or stored procedure
/// </summary>
/// <param name="UpdateString">SQL statement or stored procedure</param>
/// <param name="MustClose">Close connection if true</param>
public void UpdateSQLData(string UpdateString, bool MustClose)
{
SQLDataSetCommand dsComm = new SQLDataSetCommand(); try
{
dsComm.UpdateCommand = new SQLCommand(UpdateString, this.StartSQLService());
dsComm.UpdateCommand.ExecuteNonQuery();
}
catch(Exception e)
{
throw e;
}
finally
{
if(MustClose == true)
{
this.CloseSQLService();
}
}
} /// <summary>
/// An overloaded method, leaving connection open
/// </summary>
/// <param name="UpdateString">See overloaded function</param>
public void UpdateSQLData(string UpdateString)
{
this.UpdateSQLData(UpdateString, false);
}
/// <summary>
/// A destructor to close connection if any
/// </summary>
~SQLService()
{
this.CloseSQLService();
} /// <summary>
/// Close connection if any, then set m_Connection to null
/// </summary>
public void CloseSQLService()
{
if(this.m_Connection != null)
{
if(this.m_Connection.State.ToString() == "1")
{
this.m_Connection.Close();
}
this.m_Connection = null;
}
}
}
}