学步园

下面是我总结出来的一个数据库访问公共类，基于ADO.NET，C#的，其中，以重载的方式实现使用存属过程的接口和不用存储过程的接口，如有不妥请大家指正，谢谢~

作者：shinehoo

using System;<br /> using System.Collections.Generic;<br /> using System.Text;<br /> using System.Data;<br /> using System.Data.SqlClient;<br /> namespace shinehoo.db<br /> {<br /> /// <summary><br /> /// 对数据库的一些公共操作<br /> /// </summary><br /> public class DBOperate<br /> {<br /> /// <summary><br /> /// 建立数据库连接<br /> /// </summary><br /> /// <returns>返回SqlConnection对象</returns><br /> public static SqlConnection getConnection()<br /> {<br /> SqlConnection myCon;<br /> try<br /> {<br /> string strSqlCon = "Data Source = SHINEHOO-PC//SQLEXPRESS; Initial Catalog = ShineHoo_DB; Integrated Security = True";<br /> //SqlConnection类用来连接数据库<br /> myCon = new SqlConnection(strSqlCon);<br /> }<br /> catch (Exception e)<br /> {<br /> throw e;<br /> }<br /> return myCon;<br /> }<br /> /// <summary><br /> /// 执行SqlCommand命令<br /> /// </summary><br /> /// <param name="strSqlCommand">SQL语句</param><br /> public static void getCommand(string strSqlCommand)<br /> {<br /> SqlConnection sqlcon = getConnection();<br /> try<br /> {<br /> //SqlConnection类的Open()方法用来打开数据库连接<br /> sqlcon.Open();<br /> //声明将对数据库执行一个SQL语句或存储过程<br /> SqlCommand sqlcom = new SqlCommand(strSqlCommand, sqlcon);<br /> //执行SqlCommand命令<br /> sqlcom.ExecuteNonQuery();<br /> }<br /> catch (Exception e)<br /> {<br /> throw e;<br /> }<br /> finally<br /> {<br /> //关闭数据库连接<br /> sqlcon.Close();<br /> //sqlcon.Dispose();<br /> }<br /> }<br /> /// <summary><br /> /// 创建一个DataSet对象<br /> /// </summary><br /> /// <param name="strSqlCommand">SQL语句</param><br /> /// <param name="strTable">表名</param><br /> /// <returns>返回DataSet对象</returns><br /> public static DataSet getDataSet(string strSqlCommand, string strTable)<br /> {<br /> SqlConnection sqlcon = getConnection();<br /> DataSet myds;<br /> try<br /> {<br /> sqlcon.Open();<br /> //用于填充DataSet和更新数据库的一组数据命令和一个数据库连接<br /> SqlDataAdapter sqlda = new SqlDataAdapter(strSqlCommand, sqlcon);<br /> //DataSet是数据的内存驻留表示形式，他表示一个数据集<br /> myds = new DataSet();<br /> //填充DataSet数据集<br /> sqlda.Fill(myds, strTable);<br /> }<br /> catch (Exception e)<br /> {<br /> throw e;<br /> }<br /> finally<br /> {<br /> sqlcon.Close();<br /> //sqlcon.Dispose();<br /> }<br /> return myds;<br /> }</p> <p> /// <summary><br /> /// 创建一个SqlDataReader对象<br /> /// </summary><br /> /// <param name="strSqlCommand">SQL语句</param><br /> /// <returns>返回SqlDataReader对象</returns><br /> public static SqlDataReader getRead(string strSqlCommand)<br /> {<br /> SqlConnection sqlcon = getConnection();<br /> SqlDataReader sqlread;<br /> try<br /> {<br /> sqlcon.Open();<br /> //实例化一个SqlCommand对象<br /> SqlCommand sqlcom = new SqlCommand(strSqlCommand, sqlcon);<br /> //ExecuteReader创建SqlDataReader类对象，CommandBehavior .<br /> //CloseConnection说明在关闭SqlDataReader对象的时候，关闭其关联的SqlConnection对象<br /> sqlread = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);<br /> }<br /> catch (Exception e)<br /> {<br /> throw e;<br /> }<br /> return sqlread;<br /> }<br /> //**************************************以下是调用存储过程的方法************************************</p> <p> /// <summary><br /> /// 执行存储过程，返回SqlDataReader对象<br /> /// 在SqlDataReader对象关闭的同时，数据库连接自动关闭<br /> /// </summary><br /> /// <param name="proName">存储过程名</param><br /> /// <param name="paraValues">要传递给给存储过程的参数值类表</param><br /> /// <returns>SqlDataReader对象</returns><br /> public static SqlDataReader getRead(string proName, SqlParameter[] paraValues)<br /> {<br /> SqlConnection sqlcon = getConnection();<br /> SqlDataReader dr;<br /> SqlCommand comm = new SqlCommand(proName, sqlcon);<br /> comm.CommandType = CommandType.StoredProcedure;<br /> // 添加所有参数<br /> if (paraValues != null)<br /> {<br /> foreach (SqlParameter para in paraValues)<br /> {<br /> comm.Parameters.Add(para);<br /> }<br /> }<br /> try<br /> {<br /> sqlcon.Open();<br /> dr = comm.ExecuteReader(CommandBehavior.CloseConnection);<br /> }<br /> catch (Exception e)<br /> {<br /> throw e;<br /> }<br /> finally<br /> {<br /> comm.Parameters.Clear();<br /> }<br /> return dr;<br /> }<br /> /// <summary><br /> /// 执行存储过程，不返回值。<br /> /// </summary><br /> /// <param name="proName">存储过程名</param><br /> /// <param name="paraValues">参数值列表。</param><br /> public static void getCommand(string proName, SqlParameter[] paraValues)<br /> {<br /> SqlConnection sqlcon = getConnection();<br /> SqlCommand comm = new SqlCommand(proName, sqlcon);<br /> comm.CommandType = CommandType.StoredProcedure;<br /> // 添加所有参数<br /> if (paraValues != null)<br /> {<br /> foreach (SqlParameter para in paraValues)<br /> {<br /> comm.Parameters.Add(para);<br /> }<br /> }<br /> try<br /> {<br /> sqlcon.Open();<br /> comm.ExecuteNonQuery();<br /> }<br /> catch (Exception e)<br /> {<br /> throw e;<br /> }<br /> finally<br /> {<br /> comm.Parameters.Clear();<br /> sqlcon.Close();<br /> //sqlcon.Dispose();<br /> }<br /> }<br /> /// <summary><br /> /// 执行存储过程返回一个表。<br /> /// </summary><br /> /// <param name="proName">存储过程名</param><br /> /// <param name="paraValues">参数值列表</param><br /> /// <returns>DataTable对象</returns><br /> public static DataTable getDataTable(string proName, SqlParameter[] paraValues)<br /> {<br /> SqlConnection sqlcon = getConnection();<br /> DataTable dt;<br /> SqlCommand comm = new SqlCommand(proName, sqlcon);<br /> comm.CommandType = CommandType.StoredProcedure;<br /> // 添加所有参数<br /> if(paraValues != null)<br /> {<br /> foreach (SqlParameter para in paraValues)<br /> {<br /> comm.Parameters.Add(para);<br /> }<br /> }<br /> try<br /> {<br /> sqlcon.Open();<br /> SqlDataAdapter sqlda = new SqlDataAdapter(comm);<br /> dt = new DataTable();<br /> sqlda.Fill(dt);<br /> }<br /> catch (Exception e)<br /> {<br /> throw e;<br /> }<br /> finally<br /> {<br /> comm.Parameters.Clear();<br /> sqlcon.Close();<br /> //sqlcon.Dispose();<br /> }<br /> return dt;<br /> }<br /> /// <summary><br /> /// 返回查询结果的第一行第一列对象<br /> /// </summary><br /> /// <param name="strSqlCommand"></param><br /> /// <returns></returns><br /> public static object getExecuteScale(string proName, SqlParameter[] paraValues)<br /> {<br /> object result;<br /> SqlConnection sqlcon = getConnection();<br /> //实例化一个SqlCommand对象<br /> SqlCommand comm = new SqlCommand(proName, sqlcon);<br /> comm.CommandType = CommandType.StoredProcedure;<br /> // 添加所有参数<br /> if (paraValues != null)<br /> {<br /> foreach (SqlParameter para in paraValues)<br /> {<br /> comm.Parameters.Add(para);<br /> }<br /> }<br /> try<br /> {<br /> sqlcon.Open();<br /> result = comm.ExecuteScalar();<br /> }<br /> catch (Exception e)<br /> {<br /> throw e;<br /> }<br /> finally<br /> {<br /> comm.Parameters.Clear();<br /> sqlcon.Close();<br /> //sqlcon.Dispose();<br /> }<br /> return result;<br /> }<br /> }<br /> } 

转载请注明：http://blog.csdn.net/shineHoo