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

以下是我编写的一个 *** 作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)

}

}

}

}

方法还是很多的

方法一：可以在数据库中建立Button表、Label表等数据表存储控件的有用属性

方法二：可以自定义一个可序列化的类来存储控件的属性，将序列化后的数据保存在数据库，序列化方式也有好几种

这两种方法保存事件恐怕不比较困难

方法三：脚本引擎，生成脚本保存

存储控件到数据库可能绝大多数时候都没什么意义，控件脱离了上下文本身也没意义，如果要保存界面的话，选取几个关键的属性保存就可以了，比如Location、Size这些

---