//查询操作,判断是否存在 public static bool IsPriExist(string str) { SqlConnection conn = ConnectionManger.GetConnection(); conn.Open(); SqlCommand cmd = new SqlCommand(str, conn); SqlDataReader reader; try { reader = cmd.ExecuteReader(); if (reader.Read()) { reader.Dispose(); return(true); } else { return(false); } } catch { return(false); } finally { cmd.Dispose(); } }
/// <summary> /// 带参数绑定数据 /// </summary> /// <param name="str">存储过程</param> /// <returns></returns> public static DataSet GetDataSet(string str, CommandType cmdtype, SqlParameter[] cmdParm) { SqlConnection conn = ConnectionManger.GetConnection(); DataSet ds = new DataSet(); try { SqlCommand cmd = new SqlCommand(str, conn); cmd.CommandText = str; cmd.CommandType = cmdtype; //PrepareCommand(cmd, conn, null, cmdtype, str, parm); SqlDataAdapter da = new SqlDataAdapter(cmd); foreach (SqlParameter parm in cmdParm) { cmd.Parameters.Add(parm); } da.Fill(ds); return(ds); } catch { conn.Close(); throw; } }
/// <summary> /// 通过分页存储过程绑定数据 /// </summary> /// <param name="tablename">表、视图名</param> /// <param name="fieldkey">关键字</param> /// <param name="currentpage">当前页数</param> /// <param name="pagesize">每页的数量</param> /// <param name="fieldshow">显示的字段</param> /// <param name="fieldorder">排序方式</param> /// <param name="where">条件</param> /// <param name="recordcount">总计页数</param> /// <returns></returns> public static DataSet GetGriview(string tablename, string fieldkey, int currentpage, int pagesize, string fieldshow, string fieldorder, string where, ref int recordcount) { SqlConnection conn = ConnectionManger.GetConnection(); conn.Open(); SqlCommand cmd = new SqlCommand("pageination", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@tbname", SqlDbType.NVarChar, 50).Value = tablename; cmd.Parameters.Add("@FieldKey", SqlDbType.NVarChar, 50).Value = fieldkey; cmd.Parameters.Add("@PageCurrent", SqlDbType.Int).Value = currentpage; cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize; cmd.Parameters.Add("@FieldShow", SqlDbType.NVarChar, 1000).Value = fieldshow; cmd.Parameters.Add("@FieldOrder", SqlDbType.NVarChar, 50).Value = fieldorder; cmd.Parameters.Add("@Where", SqlDbType.NVarChar, 1000).Value = where; cmd.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output; cmd.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output; DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); recordcount = (int)cmd.Parameters["@RecordCount"].Value; //pagecount = cmd.Parameters["@PageCount"].Value.ToString(); conn.Close(); return(ds); }
/// <summary> /// 执行一条返回第一条记录第一列的SqlCommand命令,通过专用的连接字符串。 /// 使用参数数组提供参数 /// </summary> /// <remarks> /// 使用示例: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="commandType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个object类型的数据,可以通过 Convert.To{Type}方法转换类型</returns> public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = ConnectionManger.GetConnection()) { PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return(val); } }
/// <summary> ///执行一个不需要返回值的SqlCommand命令,通过指定专用的连接字符串。 /// 使用参数数组形式提供参数列表 /// </summary> /// <remarks> /// 使用示例: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="commandType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = ConnectionManger.GetConnection()) { //通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); //清空SqlCommand中的参数列表 cmd.Parameters.Clear(); return(val); } }
public static DataSet DataSetBind(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = ConnectionManger.GetConnection()) { DataSet ds = new DataSet(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); //object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return(ds); } }
/// <summary> /// 通过数据集来绑定数据 /// </summary> /// <param name="str">SQL语句/存储过程CommandType.Text/CommandType.StoredProcedure </param> /// <returns></returns> public static DataSet GetDataSet(string str, CommandType cmdtype) { SqlConnection conn = ConnectionManger.GetConnection(); DataSet ds = new DataSet(); try { SqlCommand cmd = new SqlCommand(str, conn); cmd.CommandType = cmdtype; SqlDataAdapter da = new SqlDataAdapter(str, conn); da.Fill(ds); return(ds); } catch { conn.Close(); throw; } }
/// <summary> /// 执行一条返回结果集的SqlCommand命令,通过专用的连接字符串。 /// 使用参数数组提供参数 /// </summary> /// <remarks> /// 使用示例: /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="commandType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个包含结果的SqlDataReader</returns> public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = ConnectionManger.GetConnection(); // 在这里使用try/catch处理是因为如果方法出现异常,则SqlDataReader就不存在, //CommandBehavior.CloseConnection的语句就不会执行,触发的异常由catch捕获。 //关闭数据库连接,并通过throw再次引发捕捉到的异常。 try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return(rdr); } catch { conn.Close(); conn.Dispose(); throw; } }