c# mysql类使用方法_C#中如何定义数据库操作类，并调用？

展开全部

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

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.OleDb;

using System.Configuration;

using System.Data;

namespace AutoEmailSender

{

///

/// 数据库交互类

///

public class DB

{

///

/// 获得数据库连接

///

///

public static OleDbConnection GetDBConnection()

{

return new OleDbConnection(ConfigurationManager.AppSettings["ConnectString"]);

}

///

/// 查询结果集

///

/// 执行语句

/// 返回一个DataTable对象

public static DataTable ExecuteDataTable(string sql)

{

using (OleDbConnection con = GetDBConnection())

{

OleDbCommand cmd = new OleDbCommand(sql, con);

return ExecuteDataTable(cmd);

}

}

///

/// 查询结果集

///

/// 执行语句的OleDbCommand命令

/// 返回一个DataTable对象

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;

}

///

/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。

///

/// 查询语句

/// 返回结果集中第一行的第一列的object值

public static object ExecuteScalar(string sql)

{

using (OleDbConnection con = GetDBConnection())

{

OleDbCommand cmd = new OleDbCommand(sql, con);

return ExecuteScalar(cmd);

}

}

///

/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。

///

/// 查询命令

/// 返回结果集中第一行的第一列的object值

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;

}

}

///

/// 更新数据集

///

/// 要更新的数据集

/// 插入SQL语句

/// 更新SQL语句

/// 删除SQL语句

///

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;

}

}

}

///

/// 返回一个查询语句执行结果的表结构

///

/// 查询语句,不支持复杂SQL

///

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;

}

///

/// 根据输入的查询语句自动生成插入,更新,删除命令

///

/// 查询语句

/// 插入命令

/// 更新命令

/// 删除命令

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 updatePrimarykeys = new List();//主键参数集合

List deletePrimarykeys = new List();//主键参数集合,因为不能同时被OleDbCommand个命令引用,所以多申明一个

List insertFields = new List();//字段参数集合

List updateFields = new List();//字段参数集合

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);

}

}

}

}

本回答被提问者采纳

已赞过

已踩过<

你对这个回答的评价是？

评论

收起