关于C#中SqlServer数据库编程的问题
C#中SqlServer数据库编程：有用oledb的有用ado的，这两个有什么区别？怎么用？有没有一些完整的可以运行的例子借鉴一下！ 问题点数：100、回复次数：3Top
1 楼chechy（www.qdocuments.net）回复于 2002-02-20 18:05:09 得分 50
实际上在.net framework中提供了两种方式，一种是oledb，另一种是sql。
Oledb就是通过ADO连接的，两者没有什么区别。
sql是针对微软的SqlServer，其速度比Oledb快一倍以上。
另外MSDN的实例很多。下面就是一段
public void InsertRow(string myConnection)
{
// If the connection string is null, use a default.
if(myConnection == "")
{
myConnection = "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";
}
SqlConnection mySqlClientSrvConn = new SqlConnection(myConnection);
string myInsertQuery = "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')";
SqlCommand mySqlCommand = new SqlCommand(myInsertQuery);
mySqlCommand.Connection = mySqlClientSrvConn;
mySqlClientSrvConn.Open();
mySqlCommand.ExecuteNonQuery();
mySqlCommand.Connection.Close();
}
Top
2 楼bluesx（独上西楼）回复于 2002-02-21 08:44:39 得分 50
一个完整的类：
using System;
using System.Data;
using System.Data.SqlClient;
namespace Finacy
{
/// <summary>
/// SQL语句执行类.
/// 作者:bluesx.
/// 创建日期:2002.1.30.
/// </summary>
public sealed class SqlExecute
{
/// <summary>
/// 连接对象
/// </summary>
private SqlConnection _Conn;
/// <summary>
/// 事务对象
/// </summary>
private SqlTransaction _Trans;
/// <summary>
/// 命令执行对象
/// </summary>
private SqlCommand _Cmd;
/// <summary>
/// 连接字符串
/// </summary>
private string _ConnString;
/// <summary>
/// 是否开始了事务
/// </summary>
private bool _BeginTrans;
/// <summary>
/// 构造函数
/// </summary>
/// <remarks>初始化数据库连接</remarks>
public SqlExecute()
{
try
{
///从Web.config文件中读取连接字符串
this._ConnString=ConfigurationSettings.AppSettings["ConnString"].ToString().Trim();
///从系统公用变量类的静态属性ConnString中读取连接字符串
if (this._ConnString.Trim()=="")
{
throw new FinacyException("请在Web.config文件中设置数据库连接字符串!");
}
this._Conn=new SqlConnection(this._ConnString.Trim());
this._Cmd=new SqlCommand();
this._Cmd.CommandTimeout=60;
this._Cmd.Connection=this._Conn;
this.Open();
}
catch
{
throw;
}
}
/// <summary>
/// 析构函数，关闭数据库连接，释放资源
/// </summary>
~SqlExecute()
{
if (this._BeginTrans)
{
this.RollBack();
}
this.Close();
this._Conn=null;
this._Trans=null;
this._Cmd=null;
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
try
{
if (this._Conn.State==ConnectionState.Closed)
{
this._Conn.Open();
}
}
catch
{
throw;
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
try
{
if (this._Conn.State!=ConnectionState.Closed)
{
this._Conn.Close();
}
}
catch
{
throw;
}
}
/// <summary>
/// 开始事务
/// </summary>
public void BeginTrans()
{
try
{
if (!this._BeginTrans)
{
if (this._Conn.State==ConnectionState.Closed)
{
throw new FinacyException("数据库连接未打开!");
}
this._Trans=this._Conn.BeginTransaction();
this._Cmd.Transaction=this._Trans;
this._BeginTrans=true;
}
else
{
throw new FinacyException("已经开始事务!");
}
}
catch
{
throw;
}
}
/// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
try
{
if (this._BeginTrans)
{
this._Trans.Commit();
this._Cmd.Transaction=null;
this._BeginTrans=false;
}
}
catch
{
throw;
}
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollBack()
{
try
{
if (this._BeginTrans)
{
this._Trans.Rollback();
this._Cmd.Transaction=null;
this._BeginTrans=false;
}
}
catch
{
throw;
}
}
/// <summary>
/// 执行SQL语句，返回执行影响的行数
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>执行影响行数</returns>
public int ExecuteSQL(string strSql)
{
this._Cmd.CommandType=CommandType.Text;
this._Cmd.CommandText=strSql;
return this._Cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行返回结果集的SQL语句，返回DataSet对象
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="myDataSet">返回DataSet对象</param>
/// <param name="strTableName">DataTable名称</param>
public void ExecuteSQL(string strSql,out DataSet myDataSet,string strTableName)
{
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=this._Cmd;
da.SelectCommand.CommandType=CommandType.Text;
da.SelectCommand.CommandText=strSql;
myDataSet=new DataSet();
da.Fill(myDataSet,strTableName);
da=null;
}
/// <summary>
/// 执行返回结果集的SQL语句，返回DataTable对象
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="myDataTable">返回DataTable对象</param>
/// <param name="strTableName">DataTable名称</param>
public void ExecuteSQL(string strSql,out DataTable myDataTable,string strTableName)
{
DataSet ds;
this.ExecuteSQL(strSql,out ds,strTableName);
myDataTable=ds.Tables[strTableName];
ds.Tables.Remove(strTableName);
ds=null;
}
/// <summary>
/// 执行存储过程，返回DataSet对象
/// </summary>
/// <param name="strStoredProcName">存储过程名称</param>
/// <param name="ParamsArray">参数数组</param>
/// <param name="myDataSet">返回的DataSet</param>
/// <param name="strTableName">DataTable名称</param>
public void ExecuteSP(string strStoredProcName,SqlParameter[] ParamsArray,out DataSet myDataSet,string strTableName)
{
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=this._Cmd;
da.SelectCommand.CommandType=CommandType.StoredProcedure;
da.SelectCommand.CommandText=strStoredProcName;
foreach(SqlParameter param in ParamsArray)
{
da.SelectCommand.Parameters.Add(param);
}
myDataSet=new DataSet();
da.Fill(myDataSet,strTableName);
da=null;
}
/// <summary>
/// 执行存储过程，返回DataTable对象
/// </summary>
/// <param name="strStoredProcName">存储过程名称</param>
/// <param name="ParamsArray">参数数组</param>
/// <param name="myDataTable">返回的DataTable</param>
/// <param name="strTableName">DataTable名称</param>
public void ExecuteSP(string strStoredProcName,SqlParameter[] ParamsArray,out DataTable myDataTable,string strTableName)
{
DataSet ds;
this.ExecuteSP(strStoredProcName,ParamsArray,out ds,strTableName);
myDataTable=ds.Tables[strTableName];
if (myDataTable!=null)
{
ds.Tables.Remove(strTableName);
}
ds=null;
}
}
}Top
3 楼bluesx（独上西楼）回复于 2002-02-21 08:45:29 得分 0
这其中的
if (this._ConnString.Trim()=="")
{
throw new FinacyException("请在Web.config文件中设置数据库连接字符串!");
}
用到了自定义异常。Top
