一个方便的.net数据库操作类(很容易扩展，以支持多种数据库)

转自 http://www.cnblogs.com/dreamszx/archive/2011/01/13/1934598.html

 

本文分三部分

1.数据抽象类(只有抽象，没有实现，由继承类去实现，以支持多种数据库)

2.mssql和sqlce数据库操作类的代码

3.一些用法

1.数据抽象类

using System; using System.Collections.Generic; using System.Text; using System.Data; namespace TestSQL { abstract public class SqlHelper { public abstract IDbConnection DBConn { get;} //事务的时候用到 public abstract IDbConnection GetDBConn(); public abstract Boolean TestConn(); public abstract int ExecSQL(string SqlStr); public abstract int ExecSQL(string SqlStr, params object[] ParaValues); public abstract DataSet DoSelect(string SqlStr); public abstract DataSet DoSelect(string SqlStr, params object[] ParaValues); public abstract DataTable DoSelectToTable(string SqlStr, string tablename); public abstract DataTable DoSelectToTable(string SqlStr, string tablename, params object[] ParaValues); public abstract IDataReader ExecReader(string SqlStr); public abstract IDataReader ExecReader(string SqlStr, params object[] ParaValues); public abstract object GetSingle(string SQLString); public abstract object GetSingle(string SQLString, params object[] ParaValues); public abstract int ExecuteSqlTran(List<String> SQLStringList); public abstract bool ColumnExists(string tableName, string columnName); public abstract bool TabExists(string TableName); #region 公共操作类 #region 取得最大id+1 public int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } #endregion #region 是否存在记录(不带参数) public bool HasRecord(string strSql) { object obj = GetSingle(strSql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 是否存在记录(带参数) public bool HasRecord(string strSql, params object[] ParaValues) { object obj = GetSingle(strSql, ParaValues); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #endregion } }

 

2.mssql数据库操作类

using System; using System.Data; using System.Collections.Generic; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.IO; using System.Collections; namespace TestSQL { public class MssqlDal: SqlHelper { private SqlConnection F_Conn; private string connStr; public MssqlDal(string DBConnStr) { connStr = DBConnStr; F_Conn = GetDBConn() as SqlConnection; } public override IDbConnection DBConn { get { return F_Conn; } } #region 连接数据库 /// <summary> /// 连接数据库 /// </summary> /// <returns>IDbConnection</returns> public override IDbConnection GetDBConn() { F_Conn = new SqlConnection(connStr); try { if (ConnectionState.Closed == F_Conn.State) { F_Conn.Open(); } } catch (System.Data.SqlClient.SqlException ex) { //NLSSysCtrl.NKDbgPrintfW(ex.ToString() + "/r/n"); MessageBox.Show("数据库连接失败："+ex.Message); } return F_Conn; } #endregion #region 测试连接数据库 /// <summary> /// 测试连接数据库 /// </summary> /// <returns>Boolean</returns> public override Boolean TestConn() { using (SqlConnection Conn = new SqlConnection(connStr)) { try { Conn.Open(); return true; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("数据库连接失败：" + ex.Message); return false; } } } #endregion #region 执行SQL语句 /// <summary> /// 执行不带参数的SQL语句 /// </summary> /// <param name="SqlStr">要执行的SQL语句</param> /// <returns>返回影响行数</returns> public override int ExecSQL(string SqlStr) { using (SqlCommand cmd = new SqlCommand(SqlStr, F_Conn)) { try { int val = cmd.ExecuteNonQuery(); return val; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("执行SQL语句失败：" + ex.Message + "/n" + SqlStr); return 0; } } } /// <summary> /// 执行带参数的SQL语句 /// </summary> /// <param name="SqlStr">要执行的SQL语句</param> /// <param name="ParaValues">传入的参数值</param> /// <returns>返回影响行数</returns> public override int ExecSQL(string SqlStr, params object[] ParaValues) { using (SqlCommand cmd = new SqlCommand(SqlStr, F_Conn)) { try { int val = cmd.ExecuteNonQuery(); return val; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("执行SQL语句失败：" + ex.Message + "/n" + SqlStr); return 0; } } } #endregion #region 返回数据集 /// <summary> /// 返回数据集(不带参数) /// </summary> /// <param name="SqlStr">需要查询的SQL语句</param> /// <returns>DataSet</returns> public override DataSet DoSelect(string SqlStr) { try { SqlDataAdapter F_DataApt = new SqlDataAdapter(SqlStr, F_Conn); DataSet F_DataSet = new DataSet(); F_DataApt.Fill(F_DataSet); return F_DataSet; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } /// <summary> /// 返回数据集(带参数) /// </summary> /// <param name="SqlStr">需要查询的SQL语句</param> /// <param name="ParaValues">传入的参数值</param> /// <returns>DataSet</returns> public override DataSet DoSelect(string SqlStr, params object[] ParaValues) { using (SqlCommand cmd = CreateSqlCommand(SqlStr, ParaValues) ) { try { SqlDataAdapter F_DataApt = new SqlDataAdapter(); F_DataApt.SelectCommand = cmd; DataSet F_DataSet = new DataSet(); F_DataApt.Fill(F_DataSet); return F_DataSet; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } } #endregion #region 返回DataTable /// <summary> /// 返回DataTable (不带参数) /// </summary> /// <param name="SqlStr">sql语句</param> /// <param name="tablename">自定义的表名</param> /// <returns>DataTable</returns> public override DataTable DoSelectToTable(string SqlStr, string tablename) { DataTable P_tbl; //声明一个DataTable对象 try { SqlDataAdapter F_DataApt = new SqlDataAdapter(SqlStr, F_Conn); P_tbl = new DataTable(tablename); F_DataApt.Fill(P_tbl); //将表中对象放入P_tbl中 return P_tbl; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } /// <summary> /// 返回DataTable (带参数) /// </summary> /// <param name="SqlStr">sql语句</param> /// <param name="ParaValues">参数数组</param> /// <param name="tablename">自定义的表名</param> /// <returns>DataTable</returns> public override DataTable DoSelectToTable(string SqlStr, string tablename, params object[] ParaValues) { using (SqlCommand cmd = CreateSqlCommand(SqlStr, ParaValues)) { DataTable P_tbl; //声明一个DataTable对象 try { SqlDataAdapter F_DataApt = new SqlDataAdapter(); F_DataApt.SelectCommand = cmd; P_tbl = new DataTable(tablename); F_DataApt.Fill(P_tbl); //将表中对象放入P_tbl中 return P_tbl; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } } #endregion #region 返回SqlDataReader类型数据 /// <summary> /// 返回SqlDataReader类型数据(不带参数) /// </summary> /// <param name="SqlStr">要执行的SQL语句</param> /// <returns>SqlDataReader</returns> public override IDataReader ExecReader(string SqlStr) { using (SqlCommand cmd = new SqlCommand(SqlStr, F_Conn)) { SqlDataReader P_Dr; try { P_Dr = cmd.ExecuteReader(); return P_Dr; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } } /// <summary> /// 返回SqlDataReader类型数据(带参数) /// </summary> /// <param name="ParaValues">参数数组</param> /// <param name="SqlStr">要执行的SQL语句</param> /// <returns>SqlDataReader</returns> public override IDataReader ExecReader(string SqlStr, params object[] ParaValues) { using (SqlCommand cmd = CreateSqlCommand(SqlStr, ParaValues)) { SqlDataReader P_Dr; try { P_Dr = cmd.ExecuteReader(); return P_Dr; } catch (System.Data.SqlClient.SqlException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } } #endregion #region 执行一条计算查询结果语句，返回查询结果（object）。 /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public override object GetSingle(string SQLString) { using (SqlCommand cmd = new SqlCommand(SQLString, F_Conn)) { try { object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw e; } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public override object GetSingle(string SQLString, params object[] ParaValues) { using (SqlCommand cmd = CreateSqlCommand(SQLString, ParaValues)) { try { object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw e; } } } #endregion #region 执行多条Sql语句(带事务) /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public override int ExecuteSqlTran(List<String> SQLStringList) { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = F_Conn; SqlTransaction tx = F_Conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } } #endregion #region 准备sql语句 /// <summary> /// 返回SqlDataReader类型数据(不带参数) /// </summary> /// <param name="SqlStr">要执行的SQL语句</param> /// <param name="values">参数数组</param> /// <returns>SqlCommand实例</returns> private SqlCommand CreateSqlCommand(string SqlStr, object[] values) { SqlCommand cmd = new SqlCommand(SqlStr, F_Conn); //声明SqlCommand对象 //从Sql语句中循环取得参数，并放到arrlist中 ArrayList arrlist = new ArrayList(); int j = 0; Boolean Find = false; for (int i = 0; i < SqlStr.Length; i++) { if (SqlStr[i] == '@') { j = i; Find = true; } if ((SqlStr[i] == ' ' || SqlStr[i] == ')' || SqlStr[i] == ',' || i == SqlStr.Length - 1) && Find == true) //参数结尾标志 { if (i == SqlStr.Length - 1 && SqlStr[i] != ')') arrlist.Add(SqlStr.Substring(j, i - j+1)); else arrlist.Add(SqlStr.Substring(j, i - j)); Find = false; } } //赋值给参数 if (arrlist.Count == values.Length) { for (int k = 0; k < arrlist.Count; k++) { //cmd.Parameters.Add(arrlist[k], null); //cmd.Parameters[k].Value = values[k]; //上面两名等同这句 cmd.Parameters.AddWithValue(arrlist[k].ToString(), values[k]); } } else throw new Exception("参数的个数和传入值的个数不匹配！"); return cmd; } #endregion #region 表是否存在 /// <summary> /// 表是否存在 /// </summary> /// <param name="TableName"></param> /// <returns></returns> public override bool TabExists(string TableName) { string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"; //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')"; object obj = GetSingle(strsql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 字段是否存在 /// <summary> /// 判断是否存在某表的某个字段 /// </summary> /// <param name="tableName">表名称</param> /// <param name="columnName">列名称</param> /// <returns>是否存在</returns> public override bool ColumnExists(string tableName, string columnName) { string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'"; object res = GetSingle(sql); if (res == null) { return false; } return Convert.ToInt32(res) > 0; } #endregion } }

 

3.sqlce数据库操作类

using System; using System.Data; using System.Collections.Generic; using System.Text; using System.Windows.Forms; using System.Data.SqlServerCe; using System.IO; using System.Collections; //在引用添加System.Data.SqlServerCe; namespace TestSQL { public class SqlceDal : SqlHelper { SqlCeConnection F_Conn; public string SqlceConn; public SqlceDal(string DBConnStr) { SqlceConn = DBConnStr; F_Conn = GetDBConn() as SqlCeConnection; } public override IDbConnection DBConn { get { return F_Conn; } } #region 连接数据库 /// <summary> /// 连接数据库 /// </summary> /// <returns>IDbConnection</returns> public override IDbConnection GetDBConn() { F_Conn = new SqlCeConnection(SqlceConn); try { if (ConnectionState.Closed == F_Conn.State) { F_Conn.Open(); } } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("数据库连接失败：" + ex.Message); } return F_Conn; } #endregion #region 测试连接数据库 /// <summary> /// 测试连接数据库 /// </summary> /// <returns>Boolean</returns> public override Boolean TestConn() { using (SqlCeConnection Conn = new SqlCeConnection(SqlceConn)) { try { Conn.Open(); return true; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("数据库连接失败：" + ex.Message); return false; } } } #endregion #region 执行SQL语句 /// <summary> /// 执行不带参数的SQL语句 /// </summary> /// <param name="SqlStr">要执行的SQL语句</param> /// <returns>返回影响行数</returns> public override int ExecSQL(string SqlStr) { using (SqlCeCommand cmd = new SqlCeCommand(SqlStr, F_Conn)) { try { int val = cmd.ExecuteNonQuery(); return val; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("执行SQL语句失败：" + ex.Message + "/n" + SqlStr); return 0; } } } /// <summary> /// 执行带参数的SQL语句 /// </summary> /// <param name="SqlStr">要执行的SQL语句</param> /// <param name="ParaValues">传入的参数值</param> /// <returns>返回影响行数</returns> public override int ExecSQL(string SqlStr, params object[] ParaValues) { using (SqlCeCommand cmd = new SqlCeCommand(SqlStr, F_Conn)) { try { int val = cmd.ExecuteNonQuery(); return val; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("执行SQL语句失败：" + ex.Message + "/n" + SqlStr); return 0; } } } #endregion #region 返回数据集 /// <summary> /// 返回数据集(不带参数) /// </summary> /// <param name="SqlStr">需要查询的SQL语句</param> /// <returns>DataSet</returns> public override DataSet DoSelect(string SqlStr) { try { SqlCeDataAdapter F_DataApt = new SqlCeDataAdapter(SqlStr, F_Conn); DataSet F_DataSet = new DataSet(); F_DataApt.Fill(F_DataSet); return F_DataSet; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } /// <summary> /// 返回数据集(带参数) /// </summary> /// <param name="SqlStr">需要查询的SQL语句</param> /// <param name="ParaValues">传入的参数值</param> /// <returns>DataSet</returns> public override DataSet DoSelect(string SqlStr, params object[] ParaValues) { using (SqlCeCommand cmd = CreateSqlCommand(SqlStr, ParaValues)) { try { SqlCeDataAdapter F_DataApt = new SqlCeDataAdapter(); F_DataApt.SelectCommand = cmd; DataSet F_DataSet = new DataSet(); F_DataApt.Fill(F_DataSet); return F_DataSet; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } } #endregion #region 返回DataTable /// <summary> /// 返回DataTable (不带参数) /// </summary> /// <param name="SqlStr">sql语句</param> /// <param name="tablename">自定义的表名</param> /// <returns>DataTable</returns> public override DataTable DoSelectToTable(string SqlStr, string tablename) { DataTable P_tbl; //声明一个DataTable对象 try { SqlCeDataAdapter F_DataApt = new SqlCeDataAdapter(SqlStr, F_Conn); P_tbl = new DataTable(tablename); F_DataApt.Fill(P_tbl); //将表中对象放入P_tbl中 return P_tbl; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } /// <summary> /// 返回DataTable (带参数) /// </summary> /// <param name="SqlStr">sql语句</param> /// <param name="ParaValues">参数数组</param> /// <param name="tablename">自定义的表名</param> /// <returns>DataTable</returns> public override DataTable DoSelectToTable(string SqlStr, string tablename, params object[] ParaValues) { using (SqlCeCommand cmd = CreateSqlCommand(SqlStr, ParaValues)) { DataTable P_tbl; //声明一个DataTable对象 try { SqlCeDataAdapter F_DataApt = new SqlCeDataAdapter(); F_DataApt.SelectCommand = cmd; P_tbl = new DataTable(tablename); F_DataApt.Fill(P_tbl); //将表中对象放入P_tbl中 return P_tbl; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } } #endregion #region 返回SqlDataReader类型数据 /// <summary> /// 返回SqlDataReader类型数据(不带参数) /// </summary> /// <param name="SqlStr">要执行的SQL语句</param> /// <returns>SqlDataReader</returns> public override IDataReader ExecReader(string SqlStr) { using (SqlCeCommand cmd = new SqlCeCommand(SqlStr, F_Conn)) { SqlCeDataReader P_Dr; try { P_Dr = cmd.ExecuteReader(); return P_Dr; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } } /// <summary> /// 返回SqlDataReader类型数据(带参数) /// </summary> /// <param name="ParaValues">参数数组</param> /// <param name="SqlStr">要执行的SQL语句</param> /// <returns>SqlDataReader</returns> public override IDataReader ExecReader(string SqlStr, params object[] ParaValues) { using (SqlCeCommand cmd = CreateSqlCommand(SqlStr, ParaValues)) { SqlCeDataReader P_Dr; try { P_Dr = cmd.ExecuteReader(); return P_Dr; } catch (System.Data.SqlServerCe.SqlCeException ex) { MessageBox.Show("查询SQL语句失败：" + ex.Message + "/n" + SqlStr); return null; } } } #endregion #region 执行一条计算查询结果语句，返回查询结果（object）。 /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public override object GetSingle(string SQLString) { using (SqlCeCommand cmd = new SqlCeCommand(SQLString, F_Conn)) { try { object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlServerCe.SqlCeException e) { throw e; } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public override object GetSingle(string SQLString, params object[] ParaValues) { using (SqlCeCommand cmd = CreateSqlCommand(SQLString, ParaValues)) { try { object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlServerCe.SqlCeException e) { throw e; } } } #endregion #region 执行多条Sql语句(带事务) /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public override int ExecuteSqlTran(List<String> SQLStringList) { using (SqlCeCommand cmd = new SqlCeCommand()) { cmd.Connection = F_Conn; SqlCeTransaction tx = F_Conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } } #endregion #region 准备sql语句 /// <summary> /// 返回SqlDataReader类型数据(不带参数) /// </summary> /// <param name="SqlStr">要执行的SQL语句</param> /// <param name="values">参数数组</param> /// <returns>SqlCommand实例</returns> private SqlCeCommand CreateSqlCommand(string SqlStr, object[] values) { SqlCeCommand cmd = new SqlCeCommand(SqlStr, F_Conn); //声明SqlCommand对象 //从Sql语句中循环取得参数，并放到arrlist中 ArrayList arrlist = new ArrayList(); int j = 0; Boolean Find = false; for (int i = 0; i < SqlStr.Length; i++) { if (SqlStr[i] == '@') { j = i; Find = true; } if ((SqlStr[i] == ' ' || SqlStr[i] == ')' || SqlStr[i] == ',' || i == SqlStr.Length - 1) && Find == true) //参数结尾标志 { if (i == SqlStr.Length - 1 && SqlStr[i] != ')') arrlist.Add(SqlStr.Substring(j, i - j+1)); else arrlist.Add(SqlStr.Substring(j, i - j)); Find = false; } } //赋值给参数 if (arrlist.Count == values.Length) { for (int k = 0; k < arrlist.Count; k++) { //cmd.Parameters.Add(arrlist[k], null); //cmd.Parameters[k].Value = values[k]; //上面两名等同这句 cmd.Parameters.AddWithValue(arrlist[k].ToString(), values[k]); } } else throw new Exception("参数的个数和传入值的个数不匹配！"); return cmd; } #endregion #region 表是否存在 /// <summary> /// 表是否存在 /// </summary> /// <param name="TableName"></param> /// <returns></returns> public override bool TabExists(string TableName) { string strsql = "select count(*) from information_schema.tables where table_name = '" + TableName + "'"; object obj = GetSingle(strsql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 字段是否存在 /// <summary> /// 判断是否存在某表的某个字段 /// </summary> /// <param name="tableName">表名称</param> /// <param name="columnName">列名称</param> /// <returns>是否存在</returns> public override bool ColumnExists(string tableName, string columnName) { string sql = "select count(1) from information_schema.columns where table_name = '" + TableName + "' and column_name='" + columnName + "'"; object res = GetSingle(sql); if (res == null) { return false; } return Convert.ToInt32(res) > 0; } #endregion } }

 

其它的数据操作类也参考上面的来写.

 

3.一些用法

 

执行SQL语句 private void button1_Click(object sender, EventArgs e) { //生成mssql 实例 SqlHelper mssql = new MssqlDal("Data Source = 192.168.3.104;Initial Catalog = s7100;USER ID = sa;PASSWORD ="); //生成sqlce 实例 string sqlcePath = Path.Combine(Application.StartupPath, "checkgun.sdf"); SqlHelper sqlce = new SqlceDal(@"Data Source=" + sqlcePath); //使用mssql数据库 //object[] paraValues ={ 15, "新品" }; //IDataReader dr = mssql.ExecReader("select * from warebase where wbid>@wbid and wbtype=@type", paraValues); //下面这句跟上面2句的效果一样 IDataReader dr = mssql.ExecReader("select * from warebase where wbid>@wbid and wbtype=@type", 15, "新品"); while (dr.Read()) { txtScript.Text = txtScript.Text + dr["wbcname"] + "/r/n"; } dr.Close(); //务必要释放 DataSet ds = sqlce.DoSelect("select * from warebase"); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { txtScript.Text = txtScript.Text + ds.Tables[0].Rows[i]["wbcname"].ToString() + "/r/n"; } //使用sqlce数据库 DataSet ds = sqlce.DoSelect("select * from globarea where gacode>@code", 1002); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { txtScript.Text = txtScript.Text + ds.Tables[0].Rows[i]["ganame"].ToString() + "/r/n"; } DataTable dt = sql.DoSelectToTable("select * from globarea", "gatable"); for (int i = 0; i < dt.Rows.Count; i++) { txtScript.Text = txtScript.Text + dt.Rows[i]["ganame"].ToString() + "/r/n"; } }

 

范例二:可抽出用于不同数据库间导数据

 

两个库之间拷数据(这里从mssql拷数据到sqlce) private void button2_Click(object sender, EventArgs e) { //生成mssql 实例 SqlHelper mssql = new MssqlDal("Data Source = 192.168.3.104;Initial Catalog = s7100;USER ID = sa;PASSWORD ="); //生成sqlce 实例 string sqlcePath = Path.Combine(Application.StartupPath, "checkgun.sdf"); SqlHelper sqlce = new SqlceDal(@"Data Source=" + sqlcePath); //sqlce.ExecSQL("delete from localpara"); //IDataReader dr = mssql.ExecReader("select * from localpara"); //或者 // IDataReader dr = mssql.ExecReader("select lparaid, lparacode, lparaname, lparavalue from localpara"); //或者 IDataReader dr = mssql.ExecReader("select dcid lparaid,dccode lparacode,dcname lparaname,dcvalues lparavalue from dbconfig"); try { DataTable scheamTable = dr.GetSchemaTable(); //取得表信息 // 生成 Sqlce 数据插入 SQL 语句 StringBuilder sbFields = new StringBuilder(); StringBuilder sbParams = new StringBuilder(); string field, param; DataRow schemaRow; for (int i = 0; i < scheamTable.Rows.Count; i++) { if (i != 0) { sbFields.Append(", "); sbParams.Append(", "); } schemaRow = scheamTable.Rows[i]; field = string.Format("[{0}]", schemaRow["ColumnName"]); //字段名称 param = "@" + ((string)schemaRow["ColumnName"]).Replace(" ", "_"); //参数名称 sbFields.Append(field); sbParams.Append(param); } string insertSql = string.Format("INSERT INTO [{0}]({1}) VALUES({2})", "localpara", sbFields, sbParams); // 执行数据导入 object[] values; while (dr.Read()) { values = new object[dr.FieldCount]; dr.GetValues(values); sqlce.ExecSQL(insertSql, values); } } catch (Exception ex) { MessageBox.Show("导入失败" + "/r/n" + "错误信息：" + ex.ToString() + "/r/n"); } finally { if (dr != null && dr.IsClosed == false) { dr.Close(); dr.Dispose(); } } }

 

后记：

1.这里提供个思路，功能和效率还可以再改进

2.有个疑问：是否每执行一条SQL语句就断开数据库的连接？

欢迎提示改进意见