sqlhelper里面数据库连接要怎么写才最好
我现在才发现ｓｑｌｈｅｌｐｅｒ真的不是那么好写。大家能不能告诉下
[解决办法]
没有最好。
[解决办法]
这个完全要看什么样的程序，只有最适合的，没有最好的。
推荐借鉴socancode生成器生成的代码。
[解决办法]
第一，不能用静态的Connection，要用实例化的（new出来的）；
第二，提供2个重载，一个从应用程序配置文件中取连接字符串；另一个自定义（string型参数）。
[解决办法]
网上都有现成的，下下来自己再改良下就行了嘛
[解决办法]
sql实例：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.OleDb;using System.Configuration;using System.Data.SqlClient;using System.Data.Sql;namespace WorkDAL{ public class SqlHelper { /// 连接字符串 public static readonly string ConnectionString = ConfigurationSettings.AppSettings["StrCon"].ToString(); SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["StrCon"].ToString()); //用于缓存参数的HASH表 //private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <returns>执行命令所影响的行数</returns> public static int ExecuteNonQuery(SqlConnection connection, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 用执行的数据库连接执行一个返回数据集的sql命令 /// </summary> /// <remarks> /// 举例: /// OleDbDataReader r = ExecuteReader(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>包含结果的读取器</returns> /// <summary> /// 返回一个DataSet数据集 /// </summary> /// <param name="connectionString">一个有效的连接字符串</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>包含结果的数据集</returns> /// <summary> /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列 /// </summary> /// <remarks> ///例如: /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> ///<param name="connectionString">一个有效的连接字符串</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> public static object ExecuteScalar(string connectionString, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection(connectionString)) { PrepareCommand(cmd, connection, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 用指定的数据库连接执行一个命令并返回一个数据集的第一列 /// </summary> /// <remarks> /// 例如: /// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24)); /// </remarks> /// <param name="conn">一个存在的数据库连接</param> /// <param name="commandText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> public static object ExecuteScalar(SqlConnection connection, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// <summary> /// 准备执行一个命令 /// </summary> /// <param name="cmd">sql命令</param> /// <param name="conn">Sql连接</param> /// <param name="trans">Sql事务</param> /// <param name="cmdText">命令文本,例如：Select * from Products</param> /// <param name="cmdParms">执行命令的参数</param> private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string cmdText, SqlParameter[] cmdParms) { //判断连接的状态。如果是关闭状态，则打开 if (conn.State != ConnectionState.Open) conn.Open(); //cmd属性赋值 cmd.Connection = conn; cmd.CommandText = cmdText; cmd.CommandType = CommandType.Text; //添加cmd需要的存储过程参数 if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } /// <summary> /// 分页使用 /// </summary> /// <param name="query"></param> /// <param name="passCount"></param> /// <returns></returns> private static string recordID(string query, int passCount) { using (SqlConnection m_Conn = new SqlConnection(ConnectionString)) { m_Conn.Open(); SqlCommand cmd = new SqlCommand(query, m_Conn); string result = string.Empty; using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (passCount < 1) { result += "," + dr.GetInt32(0); } passCount--; } } m_Conn.Close(); m_Conn.Dispose(); return result.Substring(1); } } /// <summary> /// ACCESS高效分页 /// </summary> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">分页容量</param> /// <param name="strKey">主键</param> /// <param name="showString">显示的字段</param> /// <param name="queryString">查询字符串，支持联合查询</param> /// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param> /// <param name="orderString">排序规则</param> /// <param name="pageCount">传出参数：总页数统计</param> /// <param name="recordCount">传出参数：总记录统计</param> /// <returns>装载记录的DataTable</returns> public static DataTable ExecutePager(int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount) { if (pageIndex < 1) pageIndex = 1; if (pageSize < 1) pageSize = 10; if (string.IsNullOrEmpty(showString)) showString = "*"; if (string.IsNullOrEmpty(orderString)) orderString = strKey + " asc "; using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString)) { m_Conn.Open(); string myVw = string.Format(" ( {0} ) tempVw ", queryString); OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), m_Conn); recordCount = Convert.ToInt32(cmdCount.ExecuteScalar()); if ((recordCount % pageSize) > 0) pageCount = recordCount / pageSize + 1; else pageCount = recordCount / pageSize; OleDbCommand cmdRecord; if (pageIndex == 1)//第一页 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), m_Conn); } else if (pageIndex > pageCount)//超出总页数 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn); } else { int pageLowerBound = pageSize * pageIndex; int pageUpperBound = pageLowerBound - pageSize; string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound); cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw, strKey, recordIDs, orderString), m_Conn); } OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord); DataTable dt = new DataTable(); dataAdapter.Fill(dt); m_Conn.Close(); m_Conn.Dispose(); return dt; } } //////////////////////////////////////////////////添加的方法/////////////////////////////// /// <summary> /// 根据数据库连接字符串,创建数据库连接对象 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <returns>数据库连接对象</returns> public static SqlConnection CreateDbConnection(string connectionString) { return new SqlConnection(connectionString); }
[解决办法]
/// <summary> /// 创建命令对象 /// </summary> /// <param name="sqlString">SQL命令字符串</param> /// <param name="connection">数据库连接对象</param> /// <returns>命令对象</returns> public static SqlCommand CreateDbCommand(string sqlString, SqlConnection connection) { return CreateDbCommand(sqlString, connection, null); } /// <summary> /// 创建命令对象 /// </summary> /// <param name="sqlString">SQL命令字符串</param> /// <param name="connection">数据库连接对象</param> /// <param name="transaction">事务对象</param> /// <returns>命令对象</returns> public static SqlCommand CreateDbCommand(string sqlString, SqlConnection connection, SqlTransaction transaction) { if (transaction == null) return new SqlCommand(sqlString, connection); else return new SqlCommand(sqlString, connection, transaction); } /// <summary> /// 创建命令参数 /// </summary> /// <param name="parmName">参数名称</param> /// <param name="parmType">参数类型</param> /// <param name="parmValue">参数值</param> /// <returns>命令参数对象</returns> public static SqlParameter CreateCommandParameter(string parmName, SqlDbType parmType, object parmValue) { SqlParameter parm = new SqlParameter(parmName, parmType); parm.Value = parmValue; return parm; } //判断回复是否为空 public bool Reply(int cmid) { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select count(*) from ReplyMessage where CMid='" + cmid + "'"; cmd.Connection = con; int result = Convert.ToInt32(cmd.ExecuteScalar()); if (result > 0) { return true; } else { return false; } } public static DataTable SqlExecutePager(int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount) { if (pageIndex < 1) pageIndex = 1; if (pageSize < 1) pageSize = 10; if (string.IsNullOrEmpty(showString)) showString = "*"; if (string.IsNullOrEmpty(orderString)) orderString = strKey + " asc "; using (SqlConnection m_Conn = new SqlConnection(ConnectionString)) { m_Conn.Open(); string myVw = string.Format(" ( {0} ) tempVw ", queryString); SqlCommand cmdCount = new SqlCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), m_Conn); recordCount = Convert.ToInt32(cmdCount.ExecuteScalar()); if ((recordCount % pageSize) > 0) pageCount = recordCount / pageSize + 1; else pageCount = recordCount / pageSize; SqlCommand cmdRecord; if (pageIndex == 1)//第一页 { cmdRecord = new SqlCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), m_Conn); } else if (pageIndex > pageCount)//超出总页数 { cmdRecord = new SqlCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn); } else { int pageLowerBound = pageSize * pageIndex; int pageUpperBound = pageLowerBound - pageSize; string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound); cmdRecord = new SqlCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw, strKey, recordIDs, orderString), m_Conn); } SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdRecord); DataTable dt = new DataTable(); dataAdapter.Fill(dt); m_Conn.Close(); m_Conn.Dispose(); return dt; } } /// <summary> /// 执行查询，删除，增加等 /// </summary> /// <param name="cmdtext"></param> public void Execute(string cmdtext,params SqlParameter[] Parameter) { #region SqlCommand cmd = new SqlCommand(); try { con.Open(); cmd.CommandText = cmdtext; cmd.Connection = con; cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw new Exception(ex.Message); } finally { con.Close(); } #endregion } /// <summary> /// 执行结果返回整数 /// </summary> /// <param name="cmdtext"></param> //public int EScalar(string cmdtext) //{ // #region // SqlCommand cmd = new SqlCommand(); // try // { // con.Open(); // cmd.CommandText = cmdtext; // cmd.Connection = con; // int result=cmd.ExecuteScalar(); // return result; // } // catch (SqlException ex) // { // throw new Exception(ex.Message); // } // finally // { // con.Close(); // } // #endregion //} /// <summary> /// 所有查询 /// </summary> /// <returns></returns> public DataSet Select(string cmdtext) { #region SqlDataAdapter sd = new SqlDataAdapter(cmdtext,con); DataSet ds = new DataSet(); try { con.Open(); sd.Fill(ds); return ds; } catch (Exception e) { throw new Exception(e.Message); } finally { con.Close(); } #endregion } }}
[解决办法]
网上百度就出来了 基本都通用，实在不行用动软生成
[解决办法]
download一个不得了。都差不多的东西
[解决办法]
DbHelpSql类
public static string connectionString = PubConstant.ConnectionString; public DbHelperSQL() { }