1 做个DATABASE类,最好做成个抽象类,里面封装对数据库的公共基本操作,比如 public virtual DataSet ExecuteDataSet(string commandText), public virtual int ExecuteNonQuery(string commandText) 等等2 建几个特定数据库类,比如public class SqlDatabase:Database,这个类从DATABASE类继承,实现SQLSERVER特定的一些操作,类似的可以再做几个,比如OracleDatabase等。3 然后做个DatabaseFactory类 ,这个类根据配制文件来生成特定类型的DATABASE类, 类似 public static Database CreateDatabase(DatabaseConnectInformation info) { ArgumentValidation.CheckForNullReference(info,"DatabaseConnectInformation"); Database db = null; switch(info.DataProvider) { case DataProviderType.SqlClient: { db = new SqlDatabase(); break; } case DataProviderType.OracleClient: { db = new OracleDatabase(); break; } case DataProviderType.OleDb: { db = new MsAccessDatabase(); break; } } if (db != null) db.DatabaseConnection = info; return db; } }
顶一个bitpolar(独自看天) 的确比我考虑的更宽
using System; using System.Data; using System.Data.SqlClient; namespace DBAccess { /// <summary> /// Class1 的摘要说明。 /// </summary> public class DB { SqlConnection conn = new SqlConnection ("server=(local);database=pubs;uid=sa;pwd=sa"); SqlCommand cmd = new SqlCommand (); public DB() { // // TODO: 在此处添加构造函数逻辑 // }
/// <summary> /// 执行存储过程,返回DataSet /// </summary> /// <param name="cmd">存储过程参数数组</param> /// <param name="proc">存储过程名</param> /// <returns>DataSet</returns> public DataSet returnDataSet(string proc,SqlParameter[] paras) { this.cmd.Connection = conn; this.cmd.CommandType = CommandType.StoredProcedure; this.cmd.CommandText = proc; if(paras!=null && paras.Length > 0) { foreach(SqlParameter p in paras) { cmd.Parameters.Add(p); } } SqlDataAdapter da = new SqlDataAdapter (); DataSet ds = new DataSet (); da.Fill(ds); return ds; } /// <summary> /// 执行sql语句 /// </summary> /// <param name="sql">sql查询语句</param> /// <returns></returns> public DataSet returnDataSet(string sql) { SqlDataAdapter da = new SqlDataAdapter(sql,conn); DataSet ds = new DataSet (); da.Fill(ds); return ds; } } }
using System; using System.Data.SqlClient; using System.Text; using System.Data; using System.Collections; using System.Configuration; namespace ClinetPort { /// <summary> /// DB 的摘要说明。 /// </summary> public class DB { private static string strConn = "server=127.0.0.1;database=Info;uid=sa;pwd=sa;"; /// <summary> /// 返回受影响的行数据库操作,如插入、删除等; /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public static int ExecuteSql(string strSQL) { int i = 0; SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(strSQL, conn); try { conn.Open(); //打开数据库 i = cmd.ExecuteNonQuery(); //执行无返回值的数据库操作 return i; } catch (SqlException e) { throw new Exception(e.Message); //抛出异常 } finally { cmd.Dispose(); //释放该组件占用的资源 conn.Close(); //每次操作完毕都要关闭连接 } } /// <summary> /// 返回受影响的行数据库操作,选择; /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public static int ExecuteSelectSql(string strSQL) { int i = 0; SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(strSQL, conn); try { conn.Open(); //打开数据库 i = Convert.ToInt32(cmd.ExecuteScalar()); //执行无返回值的数据库操作 return i; } catch (SqlException e) { throw new Exception(e.Message); //抛出异常 } finally { cmd.Dispose(); //释放该组件占用的资源 conn.Close(); //每次操作完毕都要关闭连接 } } /// <summary> /// 执行查询语句,返回SqlDataReader /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string strSQL) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(strSQL,conn); try { conn.Open(); SqlDataReader myReader = cmd.ExecuteReader(); return myReader; } catch(System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); } } /// <summary> /// 返回值为DataReader类型的操作 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public static int ExecuteSqlReader(string strSQL) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(strSQL, conn); try { conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { return 0; //执行成功,返回 0 } else { throw new Exception("Value Unavailable!"); } } catch (SqlException e) { throw new Exception(e.Message); } finally { cmd.Dispose(); conn.Close(); } } /// <summary> /// 返回值为DataSet类型的操作 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public static DataSet ExecuteSqlDS(string strSQL,string tablename) { SqlConnection conn = new SqlConnection(strConn); try { conn.Open(); SqlDataAdapter sda = new SqlDataAdapter(strSQL, conn); DataSet ds = new DataSet("ds"); sda.Fill(ds,tablename); //调用SqlDataAdapter的Fill方法,为DataSet填充数据 return ds; //返回得到的DataSet对象,保存了从数据库查询到的数据 } catch (SqlException e) { throw new Exception(e.Message); } finally { conn.Close(); } } /// <summary> /// 返回值为int类型的操作 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public static int ExecuteSqlValue(string strSQL) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(strSQL, conn); try { conn.Open(); object o = cmd.ExecuteScalar(); if (Object.Equals(o, null)) //判断对象是否为空 { throw new Exception("Value Unavailable!"); } else { return (int)o; } } catch (SqlException e) { throw new Exception(e.Message); } finally { cmd.Dispose(); conn.Close(); } } /// <summary> /// 直接返回object /// </summary> /// <param name="strSQL"></param> /// <returns></returns> protected static object ExecuteSqlObj(string strSQL) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(strSQL, conn); try { conn.Open(); object o = cmd.ExecuteScalar(); if (Object.Equals(o, null)) { throw new Exception("Object Unavailable!"); } else { return o; } } catch (SqlException e) { throw new Exception(e.Message); } finally { cmd.Dispose(); conn.Close(); } } /// <summary> /// 执行多条无返回值的数据库操作 /// </summary> /// <param name="strSQLs"></param> /// <returns></returns> public static int ExecuteSqls(string[] strSQLArr) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(); int i = strSQLArr.Length; //获取字符串数组中字符串的个数 try { conn.Open(); } catch (SqlException e) { throw new Exception(e.Message); } SqlTransaction tran = conn.BeginTransaction(); try { cmd.Connection = conn; cmd.Transaction = tran; foreach (string str in strSQLArr) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } tran.Commit(); //提交事务 return 0; } catch (SqlException e) { tran.Rollback(); throw new Exception(e.Message); } finally { cmd.Dispose(); conn.Close(); } } /// <summary> /// 执行存储过程的,返回值为object类型 /// </summary> /// <param name="ProcName">存储过程名称</param> /// <param name="parm">参数</param> /// <returns>object</returns> public static object ExecuteProcGetObject(string ProcName, SqlParameter[] parm) { SqlConnection conn = new SqlConnection(strConn); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; if (parm != null) { foreach (SqlParameter p in parm) { if (p != null) { if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null)) { p.Value = DBNull.Value; } cmd.Parameters.Add(p); } } } cmd.CommandText = ProcName; cmd.Connection = conn; cmd.CommandTimeout = 0; object obj = null; try { conn.Open(); obj = cmd.ExecuteScalar(); } catch (SqlException e) { throw new Exception(e.Message); } finally { cmd.Dispose(); conn.Close(); } return obj; } /// <summary> /// 执行存储过程,返回值为DataSet类型 /// </summary> /// <param name="ProcName"></param> /// <param name="parm"></param> /// <returns></returns>
因为微软的工程师对于数据库操作封装的已经够好了,
除非你自信比微软的工程师封装的还要好.
不过如果是一个开发团队共同开发的话,这么做可以减少数据库操作所出现的问题,还是值得的.再有就是返回类型最好不要使用DataReader,DataTable,DataSet类型返回
这样的返回类型也就是在.net里才认.可以返回xml格式字符串(不是xmlDocument对象)
譬如在WebService里调用返回xml字符串的方法
用java写的代码接收都没问题
public virtual DataSet ExecuteDataSet(string commandText),
public virtual int ExecuteNonQuery(string commandText) 等等2 建几个特定数据库类,比如public class SqlDatabase:Database,这个类从DATABASE类继承,实现SQLSERVER特定的一些操作,类似的可以再做几个,比如OracleDatabase等。3 然后做个DatabaseFactory类 ,这个类根据配制文件来生成特定类型的DATABASE类,
类似
public static Database CreateDatabase(DatabaseConnectInformation info)
{
ArgumentValidation.CheckForNullReference(info,"DatabaseConnectInformation"); Database db = null;
switch(info.DataProvider)
{
case DataProviderType.SqlClient:
{
db = new SqlDatabase();
break;
}
case DataProviderType.OracleClient:
{
db = new OracleDatabase();
break;
}
case DataProviderType.OleDb:
{
db = new MsAccessDatabase();
break;
}
} if (db != null)
db.DatabaseConnection = info;
return db;
}
}
using System.Data;
using System.Data.SqlClient;
namespace DBAccess
{
/// <summary>
/// Class1 的摘要说明。
/// </summary>
public class DB
{
SqlConnection conn = new SqlConnection ("server=(local);database=pubs;uid=sa;pwd=sa");
SqlCommand cmd = new SqlCommand ();
public DB()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 执行存储过程,返回DataSet
/// </summary>
/// <param name="cmd">存储过程参数数组</param>
/// <param name="proc">存储过程名</param>
/// <returns>DataSet</returns>
public DataSet returnDataSet(string proc,SqlParameter[] paras)
{
this.cmd.Connection = conn;
this.cmd.CommandType = CommandType.StoredProcedure;
this.cmd.CommandText = proc;
if(paras!=null && paras.Length > 0)
{
foreach(SqlParameter p in paras)
{
cmd.Parameters.Add(p);
}
}
SqlDataAdapter da = new SqlDataAdapter ();
DataSet ds = new DataSet ();
da.Fill(ds); return ds;
} /// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sql">sql查询语句</param>
/// <returns></returns>
public DataSet returnDataSet(string sql)
{
SqlDataAdapter da = new SqlDataAdapter(sql,conn);
DataSet ds = new DataSet ();
da.Fill(ds); return ds;
} }
}
using System.Data.SqlClient;
using System.Text;
using System.Data;
using System.Collections;
using System.Configuration;
namespace ClinetPort
{
/// <summary>
/// DB 的摘要说明。
/// </summary>
public class DB
{
private static string strConn = "server=127.0.0.1;database=Info;uid=sa;pwd=sa;";
/// <summary>
/// 返回受影响的行数据库操作,如插入、删除等;
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteSql(string strSQL)
{
int i = 0;
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open(); //打开数据库
i = cmd.ExecuteNonQuery(); //执行无返回值的数据库操作
return i;
}
catch (SqlException e)
{
throw new Exception(e.Message); //抛出异常
}
finally
{
cmd.Dispose(); //释放该组件占用的资源
conn.Close(); //每次操作完毕都要关闭连接
}
} /// <summary>
/// 返回受影响的行数据库操作,选择;
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteSelectSql(string strSQL)
{
int i = 0;
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open(); //打开数据库
i = Convert.ToInt32(cmd.ExecuteScalar()); //执行无返回值的数据库操作
return i;
}
catch (SqlException e)
{
throw new Exception(e.Message); //抛出异常
}
finally
{
cmd.Dispose(); //释放该组件占用的资源
conn.Close(); //每次操作完毕都要关闭连接
}
} /// <summary>
/// 执行查询语句,返回SqlDataReader
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL,conn);
try
{
conn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
} } /// <summary>
/// 返回值为DataReader类型的操作
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteSqlReader(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read())
{
return 0; //执行成功,返回 0
}
else
{
throw new Exception("Value Unavailable!");
}
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 返回值为DataSet类型的操作
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static DataSet ExecuteSqlDS(string strSQL,string tablename)
{
SqlConnection conn = new SqlConnection(strConn);
try
{
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(strSQL, conn);
DataSet ds = new DataSet("ds");
sda.Fill(ds,tablename); //调用SqlDataAdapter的Fill方法,为DataSet填充数据
return ds; //返回得到的DataSet对象,保存了从数据库查询到的数据
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回值为int类型的操作
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteSqlValue(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open();
object o = cmd.ExecuteScalar();
if (Object.Equals(o, null)) //判断对象是否为空
{
throw new Exception("Value Unavailable!");
}
else
{
return (int)o;
}
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 直接返回object
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
protected static object ExecuteSqlObj(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open();
object o = cmd.ExecuteScalar();
if (Object.Equals(o, null))
{
throw new Exception("Object Unavailable!");
}
else
{
return o;
}
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 执行多条无返回值的数据库操作
/// </summary>
/// <param name="strSQLs"></param>
/// <returns></returns>
public static int ExecuteSqls(string[] strSQLArr)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
int i = strSQLArr.Length; //获取字符串数组中字符串的个数
try
{
conn.Open();
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
SqlTransaction tran = conn.BeginTransaction();
try
{
cmd.Connection = conn;
cmd.Transaction = tran;
foreach (string str in strSQLArr)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
tran.Commit(); //提交事务
return 0;
}
catch (SqlException e)
{
tran.Rollback();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 执行存储过程的,返回值为object类型
/// </summary>
/// <param name="ProcName">存储过程名称</param>
/// <param name="parm">参数</param>
/// <returns>object</returns>
public static object ExecuteProcGetObject(string ProcName, SqlParameter[] parm)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
if (parm != null)
{
foreach (SqlParameter p in parm)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
}
}
cmd.CommandText = ProcName;
cmd.Connection = conn;
cmd.CommandTimeout = 0;
object obj = null;
try
{
conn.Open();
obj = cmd.ExecuteScalar();
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
return obj;
}
/// <summary>
/// 执行存储过程,返回值为DataSet类型
/// </summary>
/// <param name="ProcName"></param>
/// <param name="parm"></param>
/// <returns></returns>
{
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
if (parm != null)
{
foreach (SqlParameter p in parm)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
}
}
cmd.CommandText = ProcName;
cmd.Connection = conn;
cmd.CommandTimeout = 0;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
try
{
conn.Open();
sda.Fill(ds);
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
return ds;
} /// <summary>
/// 执行存储过程,返回值为DataTable类型
/// </summary>
/// <param name="ProcName"></param>
/// <param name="parm"></param>
/// <returns></returns>
public static DataTable ExecuteProcGetDataTable(string ProcName, SqlParameter[] parm)
{
DataSet ds = ExecuteProcGetDataSet(ProcName, parm);
if (ds == null) return null;
if (ds.Tables.Count >= 1)
{
return ds.Tables[0];
}
else
{
return null;
}
} /// <summary>
/// 执行查询语句,返回值为object类型
/// </summary>
/// <param name="ProcName"></param>
/// <param name="parm">参数</param>
/// <returns>object</returns>
public static object GetSingle(string SQLString)
{
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlCommand cmd = new SqlCommand(SQLString,conn);
try
{
conn.Open();
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 new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
} }
}