Question

C#中如何定义数据库操作类，并调用？

麻烦给个例子，最好是完整的源码，有注释的，谢谢各位大虾。
就30分了，全给

Answer 1

以下是我编写的一个操作access数据库的类，其他数据库可以参考修改，原理差不多的。希望对你有帮助。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Configuration;
using System.Data;

namespace AutoEmailSender
{
/// <summary>
/// 数据库交互类
/// </summary>
public class DB
{
/// <summary>
/// 获得数据库连接
/// </summary>
/// <returns></returns>
public static OleDbConnection GetDBConnection()
{
return new OleDbConnection(ConfigurationManager.AppSettings["ConnectString"]);
}

/// <summary>
/// 查询结果集
/// </summary>
/// <param name="sql">执行语句</param>
/// <returns>返回一个DataTable对象</returns>
public static DataTable ExecuteDataTable(string sql)
{
using (OleDbConnection con = GetDBConnection())
{
OleDbCommand cmd = new OleDbCommand(sql, con);
return ExecuteDataTable(cmd);
}
}

/// <summary>
/// 查询结果集
/// </summary>
/// <param name="cmd">执行语句的OleDbCommand命令</param>
/// <returns>返回一个DataTable对象</returns>
public static DataTable ExecuteDataTable(OleDbCommand cmd)
{
DataSet ds = new DataSet();
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
try
{
da.Fill(ds);
}
catch (Exception e)
{
throw e;
}
}
if (ds.Tables.Count > 0)
{
ds.Tables[0].DefaultView.RowStateFilter = DataViewRowState.Unchanged | DataViewRowState.Added | DataViewRowState.ModifiedCurrent | DataViewRowState.Deleted;
return ds.Tables[0];
}
else
return null;
}

/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>返回结果集中第一行的第一列的object值</returns>
public static object ExecuteScalar(string sql)
{
using (OleDbConnection con = GetDBConnection())
{
OleDbCommand cmd = new OleDbCommand(sql, con);
return ExecuteScalar(cmd);
}
}

/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <param name="cmd">查询命令</param>
/// <returns>返回结果集中第一行的第一列的object值</returns>
public static object ExecuteScalar(OleDbCommand cmd)
{
try
{
cmd.Connection.Open();
object obj = cmd.ExecuteScalar();
cmd.Connection.Close();
return obj;
}
catch (Exception error)
{
cmd.Connection.Close();
throw error;
}
}

/// <summary>
/// 更新数据集
/// </summary>
/// <param name="dt">要更新的数据集</param>
/// <param name="insertCmd">插入SQL语句</param>
/// <param name="updateCmd">更新SQL语句</param>
/// <param name="deleteCmd">删除SQL语句</param>
/// <returns></returns>
public static int UpdateDataSet(DataTable dt, OleDbCommand insertCmd, OleDbCommand updateCmd, OleDbCommand deleteCmd)
{
using (OleDbDataAdapter da = new OleDbDataAdapter())
{
da.InsertCommand = insertCmd;
da.UpdateCommand = updateCmd;
da.DeleteCommand = deleteCmd;
//da.UpdateBatchSize = 0; //UpdateBatchSize:指定可在一次批处理中执行的命令的数量,在Access不被支持。0:批大小没有限制。1:禁用批量更新。>1:更改是使用 UpdateBatchSize 操作的批处理一次性发送的。
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
try
{
int row = da.Update(dt);
return row;
}
catch (Exception e)
{
throw e;
}
}
}

/// <summary>
/// 返回一个查询语句执行结果的表结构
/// </summary>
/// <param name="sql">查询语句,不支持复杂SQL</param>
/// <returns></returns>
public static DataTable GetTableSchema(string sql)
{
sql = sql.ToUpper();
DataTable dt = null;
using (OleDbConnection con = GetDBConnection())
{
OleDbCommand cmd = new OleDbCommand(sql, con);
con.Open();
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly | CommandBehavior.CloseConnection))
{
dt = dr.GetSchemaTable();
}
}
return dt;
}

/// <summary>
/// 根据输入的查询语句自动生成插入,更新,删除命令
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="insertCmd">插入命令</param>
/// <param name="updateCmd">更新命令</param>
/// <param name="deleteCmd">删除命令</param>
public static void GenerateUpdateSQL(string sql, OleDbCommand insertCmd, OleDbCommand updateCmd, OleDbCommand deleteCmd)
{
sql = sql.ToUpper();
DataTable dt = GetTableSchema(sql);
string tableName = dt.Rows[0]["BaseTableName"].ToString();
List<OleDbParameter> updatePrimarykeys = new List<OleDbParameter>();//主键参数集合
List<OleDbParameter> deletePrimarykeys = new List<OleDbParameter>();//主键参数集合,因为不能同时被OleDbCommand个命令引用,所以多申明一个
List<OleDbParameter> insertFields = new List<OleDbParameter>();//字段参数集合
List<OleDbParameter> updateFields = new List<OleDbParameter>();//字段参数集合
string columns = string.Empty, values = "", set = "", where = "";
foreach (DataRow dr in dt.Rows)
{
if (dr["IsAutoIncrement"].ToString().Equals("False"))
{
insertFields.Add(new OleDbParameter("@" + dr["BaseColumnName"].ToString(),
(OleDbType)dr["ProviderType"],
Convert.ToInt32(dr["ColumnSize"]),
dr["BaseColumnName"].ToString()));
updateFields.Add(new OleDbParameter("@" + dr["BaseColumnName"].ToString(),
(OleDbType)dr["ProviderType"],
Convert.ToInt32(dr["ColumnSize"]),
dr["BaseColumnName"].ToString()));

if (!string.IsNullOrEmpty(columns))
columns += ",";
columns += dr["BaseColumnName"].ToString();
if (!string.IsNullOrEmpty(values))
values += ",";
values += "@" + dr["BaseColumnName"].ToString();
if (!string.IsNullOrEmpty(set))
set += ",";
set += dr["BaseColumnName"].ToString() + "=@" + dr["BaseColumnName"].ToString();
}
if (dr["IsKey"].ToString().Equals("True"))
{
updatePrimarykeys.Add(new OleDbParameter("@OLD_" + dr["BaseColumnName"].ToString(),
(OleDbType)dr["ProviderType"],
Convert.ToInt32(dr["ColumnSize"]),
ParameterDirection.Input,
Convert.ToBoolean(dr["AllowDBNull"]),
Convert.ToByte(dr["NumericScale"]),
Convert.ToByte(dr["NumericPrecision"]),
dr["BaseColumnName"].ToString(), DataRowVersion.Original, null));
deletePrimarykeys.Add(new OleDbParameter("@OLD_" + dr["BaseColumnName"].ToString(),
(OleDbType)dr["ProviderType"],
Convert.ToInt32(dr["ColumnSize"]),
ParameterDirection.Input,
Convert.ToBoolean(dr["AllowDBNull"]),
Convert.ToByte(dr["NumericScale"]),
Convert.ToByte(dr["NumericPrecision"]),
dr["BaseColumnName"].ToString(), DataRowVersion.Original, null));
if (!string.IsNullOrEmpty(where))
where += " and ";
where += dr["BaseColumnName"].ToString() + "=@OLD_" + dr["BaseColumnName"].ToString();
}
}

insertCmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, columns, values);
updateCmd.CommandText = string.Format("update {0} set {1} where {2}", tableName, set, where);
deleteCmd.CommandText = string.Format("delete from {0} where {1}", tableName, where);
insertCmd.Connection = GetDBConnection();
updateCmd.Connection = GetDBConnection();
deleteCmd.Connection = GetDBConnection();
foreach (OleDbParameter pa in insertFields)
{
insertCmd.Parameters.Add(pa);
}
foreach (OleDbParameter pa in updateFields)
{
updateCmd.Parameters.Add(pa);
}
foreach (OleDbParameter pa in updatePrimarykeys)
{
updateCmd.Parameters.Add(pa);
}
foreach (OleDbParameter pa in deletePrimarykeys)
{
deleteCmd.Parameters.Add(pa);
}
}
}
}

Answer 2

1，连接字符串：表示数据库是什么，密码是什么，用户名是什么
2，查询，修改，添加，删除命令
3，数据库函数调用
4，得出结果
给你一个例子如下
public bool Insert(Device.Transmitter transmitter)
{
string sql = "Insert Into Device (SerialNumber,Address,CommunicationModule,PhoneNumber,InitInfo) Values (@SerialNumber,@Address,@CommunicationModule,@PhoneNumber,@InitInfo)";
OleDbParameter[] param = new OleDbParameter[5];
param[0] = new OleDbParameter("SerialNumber", OleDbType.VarChar, 10);
param[0].Value = transmitter.SerialNumber;
param[1] = new OleDbParameter("Address", OleDbType.VarChar, 50);
param[1].Value = transmitter.Address;
param[2] = new OleDbParameter("CommunicationModule", OleDbType.Integer);
param[2].Value = transmitter.CommunicationModule;
param[3] = new OleDbParameter("PhoneNumber", OleDbType.VarChar, 20);
param[3].Value = transmitter.PhoneNumber;
param[4] = new OleDbParameter("InitInfo", OleDbType.VarChar, 100);
param[4].Value = transmitter.InitInfo;

int count=AccessDB.ExecuteNonQuery(AccessDB.ConnectionString, System.Data.CommandType.Text, sql, param);
return Convert.ToBoolean(count);
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a OleDbConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
return val;
}
catch
{
conn.Close();
throw;
}
}
}

Answer 3

要源码是吗？这个给你。
虽然是在网上Ctrl+C/Ctrl+V来的，但辛苦搜索了半天，希望
对楼主有所帮助吧==========================================================

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace GuestBook
{
/// <summary>
/// DbControl 的摘要描述。
/// </summary>
public class DbControl:Classes.DataBaseType.DbOpen
{
//类成员定义。
private int record_total = 0;
protected string DBTYPE,SQL_SELECT;
protected SqlConnection sqlConn;
protected OleDbConnection oleConn;
protected SqlCommand sqlCmd;
protected OleDbCommand oleCmd;
protected DataSet ds = new DataSet();

public int RecordTotal
{
get
{
switch (DBTYPE)
{
case "SQL":
sqlCmd.Cancel();
sqlCmd.CommandText = SQL_SELECT;
SqlDataReader sqlDr;
sqlDr = sqlCmd.ExecuteReader();
while (sqlDr.Read())
{
record_total++;
}
sqlDr.Close();
break;
case "OLE":
oleCmd.Cancel();
oleCmd.CommandText = SQL_SELECT;
OleDbDataReader oleDr;
oleDr = oleCmd.ExecuteReader();
while (oleDr.Read())
{
record_total++;
}
oleDr.Close();
break;
}
return record_total;
}
}

public DbControl(string dbType,string dbName)
{
//重载构造函数。
DBTYPE = dbType.ToUpper();
switch (dbType.ToUpper())
{
case "SQL":
sqlConn = this.SqlConnect(dbName);
oleConn.Close();
oleConn.Dispose();
break;
case "OLE":
oleConn = this.OleConnect(dbName);
oleConn.Close();
oleConn.Dispose();
break;
}
}

public DbControl():base()
{
//
// TODO: 在这里加入建构函式的程式码
//
}

public void Open(string dbType,string dbName)
{
//数据库文件打开。
DBTYPE = dbType.ToUpper();
switch (dbType.ToUpper())
{
case "SQL":
sqlConn = this.SqlConnect(dbName);
break;
case "OLE":
oleConn = this.OleConnect(dbName);
break;
}
}

public SqlDataReader SqlGetReader(string strQuery)
{
//返回一个SqlDataReader。用於Sql server
SQL_SELECT = strQuery;
sqlCmd = new SqlCommand(strQuery,sqlConn);
SqlDataReader dr;
try
{
sqlCmd.Connection.Open();
}
catch (Exception e)
{
throw e;
}
dr = sqlCmd.ExecuteReader();
return dr;
}

public OleDbDataReader OleGetReader(string strQuery)
{
//返回一个OleDbDataReader。用於OleDb
SQL_SELECT = strQuery;
oleCmd = new OleDbCommand(strQuery,oleConn);
OleDbDataReader dr;
try
{
oleCmd.Connection.Open();
}
catch (Exception e)
{
throw e;
}
dr = oleCmd.ExecuteReader();
return dr;
}

public int SqlRunCommand(string strQuery)
{
//执行一条SQL语句。包括记录插入、更新、删除。用於Sql server
SQL_SELECT = strQuery;
sqlCmd = new SqlCommand(strQuery,sqlConn);
try
{
sqlCmd.Connection.Open();
}
catch (Exception e)
{
throw e;
}
return sqlCmd.ExecuteNonQuery();
}

public int OleRunCommand(string strQuery)
{
//执行一条SQL语句。包括记录插入、更新、删除。用於OleDb
SQL_SELECT = strQuery;
oleCmd = new OleDbCommand(strQuery,oleConn);
try
{
oleCmd.Connection.Open();
}
catch (Exception e)
{
throw e;
}
return oleCmd.ExecuteNonQuery();
}

public DataView SqlGetDataSet(string strQuery)
{
//返回一个DataSet。用於Sql server
SQL_SELECT = strQuery;
sqlCmd = new SqlCommand(strQuery,sqlConn);
try
{
sqlCmd.Connection.Open();
}
catch (Exception e)
{
throw e;
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCmd;
da.Fill(ds,"DefaultTable");
return ds.Tables["DefaultTable"].DefaultView;
}

public DataView OleGetDataSet(string strQuery)
{
//返回一个DataSet。用於OleDb
SQL_SELECT = strQuery;
oleCmd = new OleDbCommand(strQuery,oleConn);
try
{
oleCmd.Connection.Open();
}
catch (Exception e)
{
throw e;
}
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = oleCmd;
da.Fill(ds,"DefaultTable");
return ds.Tables["DefaultTable"].DefaultView;
}

public void Close()
{
//数据库关闭。
switch (DBTYPE)
{
case "SQL":
sqlCmd.Cancel();
sqlCmd.Dispose();
sqlConn.Close();
sqlConn.Dispose();
break;
case "OLE":
oleCmd.Cancel();
oleCmd.Dispose();
oleConn.Close();
oleConn.Dispose();
break;
}
ds.Clear();
ds.Dispose();
}
}
}

============================================================

\Classes\DataBaseType\DbOpen.cs
============================================================

using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;

namespace GuestBook.Classes.DataBaseType
{
/// <summary>
/// DbOpen 的摘要描述。
/// </summary>
public class DbOpen
{
public DbOpen()
{
//
// TODO: 在这里加入建构函式的程式码
//
}

protected SqlConnection SqlConnect(string dbName)
{
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings[dbName]);
return conn;
}

protected OleDbConnection OleConnect(string dbName)
{
OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings[dbName]);
return conn;
}
}
}

==============================================================

类的调用方法：
==============================================================

DbControl objDbControl = new DbControl();
string SQL = "SELECT * FROM content";

objDbControl.Open("sql","sqlConnection");
dgList.DataSource = objDbControl.SqlGetDataSet(SQL);
intTotal = objDbControl.RecordTotal;
dgList.DataBind();
objDbControl.Close();

==============================================================
其中“intTotal = objDbControl.RecordTotal;”即返回记录总数。
可以把它插入到DataGrid中。如：
==============================================================

private void dgList_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Pager)
{
System.Text.StringBuilder pagerString = new System.Text.StringBuilder();
pagerString.Append("总计" + intTotal.ToString() + " 共" + dgList.PageCount + "页 每页" + dgList.PageSize + "笔");
e.Item.Cells[0].Controls.AddAt(0,new LiteralControl(pagerString.ToString())) ;
}

Answer 4

这是我自己写的一个sql操作类，希望你能用上：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace HotelDAL
{
public abstract class MyDB
{
//连接对象
private SqlConnection con = null;

protected SqlConnection Con
{
get { return con; }
set { con = value; }
}

protected MyDB()
{
if (con==null)
{
string s=ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString;
con = new SqlConnection(s);
}
}

protected SqlCommand CreateCommand(string text,CommandType type,SqlParameter [] param)
{
SqlCommand cmd = new SqlCommand(text,Con);
cmd.CommandType = type;
if (param != null)
{
cmd.Parameters.AddRange(param);
}

return cmd;
}
protected SqlParameter CreateParam(string paramName,SqlDbType type,int size,object value)
{
SqlParameter param = new SqlParameter();
param.ParameterName = paramName;
param.SqlDbType = type;
param.Size = size;
param.Value = value;
return param;
}

protected int ExecuteNonQuery(SqlCommand cmd)
{
cmd.Connection.Open();
int i = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return i;
}

protected SqlDataReader ExecuteReader(SqlCommand cmd)
{
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}

protected object ExecuteScalar(SqlCommand cmd)
{
cmd.Connection.Open();
object i = cmd.ExecuteScalar();
cmd.Connection.Close();
return i;
}

protected DataTable GetTable(SqlCommand cmd,string tableName)
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable(tableName);
da.Fill(dt);
return dt;
}

}
}