第一部分普通sql的用法:
System.Data.DataSet dss = new System.Data.DataSet(); dss = WebApplication1.SQLHelp.ExecuteDataSet(System.Data.CommandType.Text, "select AccountId from message where ServerType ='" + item + "' ", null); DataTable dtt = dss.Tables[0];
第二部分存储过程的用法:SqlParameter[] ps ={ new SqlParameter("@role",SqlDbType.Int,4), //输入参数 new SqlParameter("@accountid",SqlDbType.Int,4), //输出参数 new SqlParameter("rval", SqlDbType.Int,4) //返回结果 }; ps[0].Direction=ParameterDirection.Output; ps[1].Value=1; ps[2].Direction=ParameterDirection.ReturnValue; GridView1.DataSource = SQLHelp.ExecuteDataSet(CommandType.StoredProcedure, "sunke", ps); GridView1.DataBind();
简单的sqlhelper 转载网络
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace WebApplication1 { public static class SQLHelp { private static string constr = ConfigurationManager.ConnectionStrings["sunketestConnectionString"].ToString(); /// <summary> /// 用于提交Insert Update Delete 返回受影响的行数 /// </summary> /// <param name="cmdType">操作类型StoreProcdeure 或者是 sql语句</param> /// <param name="sql">sql语句或者存储过程的名称</param> /// <param name="sps">参数的数组,没有参数传递为Null值</param> /// <returns></returns> public static int ExecuteNonQuery(CommandType cmdType, string sql, params SqlParameter[] sps) { try { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = cmdType; // if (sps != null) // { foreach (SqlParameter var in sps) { cmd.Parameters.Add(var); } } con.Open(); int count = cmd.ExecuteNonQuery(); con.Close(); return count; } } } catch (Exception ex) { throw ex; } } /// <summary> /// 用于提交select 返回 SqlDataReader ,读取完成后需要关闭SqlDataReader /// </summary> /// <param name="cmdType">操作类型StoreProcdeure 或者是 sql语句</param> /// <param name="sql">sql语句或者存储过程的名称</param> /// <param name="sps">参数的数组,没有参数传递为Null值</param> /// <returns></returns> public static SqlDataReader ExecuteReader(CommandType cmdType, string sql, params SqlParameter[] sps) { try { SqlConnection con = new SqlConnection(constr); using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = cmdType; // if (sps != null) // { foreach (SqlParameter var in sps) { cmd.Parameters.Add(var); } } con.Open(); //关闭读取器,将自动关闭连接对象 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return dr; } } catch (Exception ex) { throw ex; } } /// <summary> /// 用于提交select中的聚合函数,返回第一行,第一列的值 /// </summary> /// <param name="cmdType">操作类型StoreProcdeure 或者是 sql语句</param> /// <param name="sql">sql语句或者存储过程的名称</param> /// <param name="sps">参数的数组,没有参数传递为Null值</param> /// <returns></returns> public static object ExecuteScalar(CommandType cmdType, string sql, params SqlParameter[] sps) { try { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = cmdType; // if (sps != null) // { foreach (SqlParameter var in sps) { cmd.Parameters.Add(var); } } con.Open(); object o = cmd.ExecuteScalar(); con.Close(); return o; } } } catch (Exception ex) { throw ex; } } /// <summary> /// 用于提交select 返回 DataSet ,数据集中默认只有一张表格 /// </summary> /// <param name="cmdType">操作类型StoreProcdeure 或者是 sql语句</param> /// <param name="sql">sql语句或者存储过程的名称</param> /// <param name="sps">参数的数组,没有参数传递为Null值</param> /// <returns></returns> public static DataSet ExecuteDataSet(CommandType cmdType, string sql, params SqlParameter[] sps) { try { using (SqlConnection con = new SqlConnection(constr)) { using (SqlDataAdapter da = new SqlDataAdapter()) { SqlCommand cmd = new SqlCommand(sql, con); cmd.CommandType = cmdType; // if (sps != null) // { foreach (SqlParameter var in sps) { cmd.Parameters.Add(var); } } da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } catch (Exception ex) { throw ex; } } } }
Sql Sqlhelper调用存储过程
最新推荐文章于 2020-12-04 15:59:25 发布