webconfig连接数据库配置

</connectionStrings>

APP_CODE;

using System; using System.Data; using System.Configuration;

using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient; using System.Text; using System.Collections;

/// <summary> /// Summary description for SqlDataAccess /// </summary> public class SqlDataAccess { //sqlConnection private string pvtStrSqlCon = ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString; //private SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon);

private string pvtStrTableName = string.Empty; private SqlCommand pvtSqlCom = null;

//with table name as parameter public SqlDataAccess(string strTableName) { ////set pvtStrSqlCon //if (pvtStrSqlCon == string.Empty) //{ // pvtStrSqlCon = ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString; //}

//set pvtStrTableName if (pvtStrTableName == string.Empty) { pvtStrTableName = strTableName; }

//set pvtSqlCom pvtSqlCom = new SqlCommand(); //pvtSqlCom.Connection = pvtSqlCon;

}

//without parameter public SqlDataAccess() { //set pvtStrSqlCon //if (pvtStrSqlCon == string.Empty) //{ // pvtStrSqlCon = ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString; //}

//set pvtSqlCom pvtSqlCom = new SqlCommand(); //pvtSqlCom.Connection = pvtSqlCon; }

// ~SqlDataAccess() { }

//when table name set, get table data according to sqlParamCol public DataSet GetTableData(SqlParameterCollection sqlParamCol) { using (SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon)) { pvtSqlCon.Open(); pvtSqlCom.Connection = pvtSqlCon;

StringBuilder strSql = new StringBuilder("", 200); strSql.Append("select * from "); strSql.Append(pvtStrTableName); if (sqlParamCol.Count > 0) { strSql.Append(" where 1=1 "); for (int i = 0; i < sqlParamCol.Count; i++) { strSql.Append(" and "); strSql.Append(sqlParamCol[i].SourceColumn); strSql.Append(" = @"); strSql.Append(sqlParamCol[i].SourceColumn);

pvtSqlCom.Parameters.Add(sqlParamCol[i].ParameterName, sqlParamCol[i].SqlDbType, sqlParamCol[i].Size, sqlParamCol[i].SourceColumn); pvtSqlCom.Parameters[i].Value = sqlParamCol[i].Value; } } pvtSqlCom.CommandText = strSql.ToString();

SqlDataAdapter daResult = new SqlDataAdapter(pvtSqlCom); DataSet dsResult = new DataSet(); daResult.Fill(dsResult);

pvtSqlCon.Close(); return dsResult; } }

public int GetDataCount(SqlParameterCollection sqlParamCol) { using (SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon)) { pvtSqlCon.Open(); pvtSqlCom.Connection = pvtSqlCon; StringBuilder strSql = new StringBuilder("", 200);

strSql.Append("select COUNT(1) from "); strSql.Append(pvtStrTableName); if (sqlParamCol.Count > 0) { strSql.Append(" where 1=1 "); for (int i = 0; i < sqlParamCol.Count; i++) { strSql.Append(" and "); strSql.Append(sqlParamCol[i].SourceColumn); strSql.Append(" = @"); strSql.Append(sqlParamCol[i].SourceColumn);

SqlDataAdapter daResult = new SqlDataAdapter(pvtSqlCom); DataSet dsResult = new DataSet(); daResult.Fill(dsResult); pvtSqlCon.Close(); return Convert.ToInt32(dsResult.Tables[0].Rows[0][0]); } }

//when update or insert, call this method public void ExecProcNoneQuery(string strProcName, SqlParameterCollection sqlParamCol) { using (SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon)) { pvtSqlCon.Open(); pvtSqlCom.Connection = pvtSqlCon;

pvtSqlCom.CommandType = CommandType.StoredProcedure; pvtSqlCom.CommandText = strProcName; for (int i = 0; i < sqlParamCol.Count; i++) { pvtSqlCom.Parameters.Add(sqlParamCol[i].ParameterName, sqlParamCol[i].SqlDbType, sqlParamCol[i].Size, sqlParamCol[i].SourceColumn); pvtSqlCom.Parameters[i].Value = sqlParamCol[i].Value; pvtSqlCom.Parameters[i].Direction = sqlParamCol[i].Direction; } pvtSqlCom.ExecuteNonQuery(); for (int i = 0; i < sqlParamCol.Count; i++) { sqlParamCol[i].Value = pvtSqlCom.Parameters[i].Value; } pvtSqlCon.Close(); } }

//when wanna to get a data result, call this method public DataSet ExecProcReader(string strProcName, SqlParameterCollection sqlParamCol) {

using (SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon)) { pvtSqlCon.Open(); pvtSqlCom.Connection = pvtSqlCon;

pvtSqlCom.CommandType = CommandType.StoredProcedure; pvtSqlCom.CommandText = strProcName; for (int i = 0; i < sqlParamCol.Count; i++) { pvtSqlCom.Parameters.Add(sqlParamCol[i].ParameterName, sqlParamCol[i].SqlDbType, sqlParamCol[i].Size, sqlParamCol[i].SourceColumn); pvtSqlCom.Parameters[i].Value = sqlParamCol[i].Value; pvtSqlCom.Parameters[i].Direction = sqlParamCol[i].Direction; } SqlDataAdapter daResult = new SqlDataAdapter(pvtSqlCom); DataSet dsResult = new DataSet(); daResult.Fill(dsResult); for (int i = 0; i < sqlParamCol.Count; i++) { sqlParamCol[i].Value = pvtSqlCom.Parameters[i].Value; } pvtSqlCon.Close(); return dsResult; } }

//when wanna to use sql to get a data result, call this method public DataSet ExecSqlReader(string strSql, SqlParameterCollection sqlParamCol) { using (SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon)) { pvtSqlCon.Open(); pvtSqlCom.Connection = pvtSqlCon;

pvtSqlCom.CommandType = CommandType.Text; pvtSqlCom.CommandText = strSql; for (int i = 0; i < sqlParamCol.Count; i++) { pvtSqlCom.Parameters.Add(sqlParamCol[i].ParameterName, sqlParamCol[i].SqlDbType, sqlParamCol[i].Size, sqlParamCol[i].SourceColumn); pvtSqlCom.Parameters[i].Value = sqlParamCol[i].Value; pvtSqlCom.Parameters[i].Direction = sqlParamCol[i].Direction; } SqlDataAdapter daResult = new SqlDataAdapter(pvtSqlCom); DataSet dsResult = new DataSet(); daResult.Fill(dsResult); for (int i = 0; i < sqlParamCol.Count; i++) { sqlParamCol[i].Value = pvtSqlCom.Parameters[i].Value; } pvtSqlCom.Parameters.Clear(); pvtSqlCon.Close(); return dsResult; } } //============================================================ /// <summary> /// 查询SQL语句时用该方法，返回DataSet /// </summary> /// <param name="strSql"></param> /// <returns></returns> public DataSet ExecSqlQuery(string strSql) { using (SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon)) { pvtSqlCon.Open(); pvtSqlCom.Connection = pvtSqlCon;

pvtSqlCom.CommandType = CommandType.Text; pvtSqlCom.CommandText = strSql; SqlDataAdapter daResult = new SqlDataAdapter(pvtSqlCom); DataSet dsResult = new DataSet(); daResult.Fill(dsResult); pvtSqlCon.Close(); return dsResult; } } //============================================================ /// <summary> /// 处理SQL语句时，用该方法，返回处理结果，大于0：成功；小于0：失败； /// </summary> /// <param name="strSql"></param> /// <returns></returns> public int ExecSqlHandel(string strSql) { strSql = strSql + ";SELECT SCOPE_IDENTITY()"; using (SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon)) { pvtSqlCon.Open(); pvtSqlCom.Connection = pvtSqlCon;

pvtSqlCom.CommandType = CommandType.Text; pvtSqlCom.CommandText = strSql; string sResult = pvtSqlCom.ExecuteScalar().ToString(); pvtSqlCon.Close(); int nResult = 0; if (!sResult.Equals("")) { nResult = int.Parse(sResult); } return nResult; } } public int ExecSqlHandel(string strSql, SqlParameterCollection sqlParamCol) { strSql = strSql + ";SELECT SCOPE_IDENTITY()"; using (SqlConnection pvtSqlCon = new SqlConnection(pvtStrSqlCon)) { pvtSqlCon.Open(); pvtSqlCom.Connection = pvtSqlCon; pvtSqlCom.CommandType = CommandType.Text; pvtSqlCom.CommandText = strSql; for (int i = 0; i < sqlParamCol.Count; i++) { pvtSqlCom.Parameters.Add(sqlParamCol[i].ParameterName, sqlParamCol[i].SqlDbType, sqlParamCol[i].Size, sqlParamCol[i].SourceColumn); pvtSqlCom.Parameters[i].Value = sqlParamCol[i].Value; pvtSqlCom.Parameters[i].Direction = sqlParamCol[i].Direction; } string sResult = pvtSqlCom.ExecuteScalar().ToString(); pvtSqlCom.Parameters.Clear(); pvtSqlCon.Close(); int nResult = string.IsNullOrWhiteSpace(sResult) ? 0 : Convert.ToInt32(sResult); return nResult; } }

internal DataSet ExecSqlQuery(StringBuilder sbSql) { throw new NotImplementedException(); } }

巴特西

webconfig连接数据库配置

最新文章

热门文章