一般为了方便我们在程序中做数据操作,都会封装一个SQLHelper的类,在这里分享一下我写的SQLHelper,并分析一下里面的代码。- public class SQLHelper
- {
- //用于获取连接字符串的属性
- public string ConnectionString
- {
- get {
- return ConfigurationManager.ConnectionStrings["myConnString"].ConnectionString;
- }
- }
- /// <summary>
- /// 执行sql返回指定名称的datatable对象
- /// </summary>
- /// <param name="sql">"select * from T_..."</param>
- /// <param name="paras">sql或存储过程的参数</param>
- /// <param name="tbName">返回的datatable名称</param>
- /// <param name="type">命令类型</param>
- /// <returns></returns>
- public DataTable ExecuteSqlReturnTable(string sql, SqlParameter[] paras,string tbName, CommandType type=CommandType.Text )
- {
- DataTable table;
- SqlConnection conn = new SqlConnection(ConnectionString);
- SqlCommand cmd = conn.CreateCommand();
- cmd.CommandText = sql;
- if (paras != null && paras.Length > 0)
- {
- foreach (SqlParameter p in paras)
- {
- cmd.Parameters.Add(p);
- }
- }
- cmd.CommandType = type;
- SqlDataAdapter sda = new SqlDataAdapter(cmd);
- if (tbName != null)
- {
- table = new DataTable(tbName);
- }
- else {
- table = new DataTable();
- }
- sda.Fill(table);
- return table;
- }
- /// <summary>
- /// 执行非查询的sql或存储过程
- /// </summary>
- /// <param name="sql">sql语句或存储过程</param>
- /// <param name="paras">执行命令所需的参数</param>
- /// <param name="type">命令类型,默认是text</param>
- /// <returns>表示执行是否成功</returns>
- public bool ExecuteNonQuery(string sql,SqlParameter[] paras,CommandType type=CommandType.Text)
- {
- bool result = false;
- SqlConnection conn = new SqlConnection(ConnectionString);
- SqlCommand cmd = new SqlCommand(sql, conn);
- cmd.CommandType = type;
- if (paras != null && paras.Length > 0)
- {
- foreach (SqlParameter p in paras)
- {
- cmd.Parameters.Add(p);
- }
- }
- conn.Open();
- if (cmd.ExecuteNonQuery() > 0)
- {
- result = true;
- }
- return result;
- }
- }
复制代码 1.ConfigurationManager类,这是一个static类,提供对客户端应用程序配置文件的访问,其Static属性ConnectionStrings用来获取当前应用程序默认配置的连接字符串;
2.ExecuteSqlReturnTable函数,封装的第一个函数,用来执行存储过程或sql语句并返回一个Datatable对象;
3.ExecuteNonQuery函数,用来执行非查询的数据操作,如插入、修改和删除,返回一个bool的值类型表示操作是否成功。
|