asp.net 连接SQL数据库的封装类，及其调用方法

1.web.config 中

<add key="ConnectionString" value="server=(local)uid=sapwd=123456database=News"/>

调用的时候

string strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString()

SqlConnection Conn = new SqlConnection(strConn)

2.或者不用web.config直接在文件中写

SqlConnection conn = new SqlConnection("server=.\\SQLEXPRESSuid=sapwd=123456database=login")

如何是Express版的数据库，一定要在服务器名的后面加上 \\SSQLEXPRESS

1.基本类型只能按值传递，而每个基本类型对应的封装类是按引用传递的。

2.从性能上说java中的基本类型是在堆栈上创建的，而所有的对象类型都是在堆上创建的，（对象的引用在堆栈上创建）。比如

Integer i=new Integer(10) 其中new Integer()是在堆上创建的，而他的引用Integer i是在堆栈上。 封装类的出现，是为了更方便的使用一些基本类型不具备的方法，比如valueOf（），toString（）等等。还有你如果想传递一个int对象的引用，而不是值，那只能用封装类。

using System

using System.Collections.Generic

using System.Text

using System.Data.SqlClient

using System.Data

using System.Configuration

public static class DBHelper

{

private static SqlConnection connection

public static SqlConnection Connection

{

get

{

//string connectionString = "User ID=saPassword=sqlpassInitial Catalog=RC_DataBasePooling=true"

string connectionString = ConfigurationManager.ConnectionStrings["dbconnectstring"].ConnectionString //获取配置文件中的数据库连接字符串

if (connection == null)

{

connection = new SqlConnection(connectionString)

connection.Open()

}

else if (connection.State == System.Data.ConnectionState.Closed)

{

connection.Open()

}

else if (connection.State == System.Data.ConnectionState.Broken)

{

connection.Close()

connection.Open()

}

return connection

}

}

/// <summary>

/// 简单防sql注入程序

/// </summary>

/// <param name="str"></param>

/// <returns></returns>

public static string Sqlstring(string str)

{

str = str.Replace("&", "&")

str = str.Replace("<", "<")

str = str.Replace(">", "&gt")

str = str.Replace("'", "''")

str = str.Replace("*", "")

str = str.Replace("\n", "<br/>")

str = str.Replace("\r\n", "<br/>")

//str = str.Replace("?","")

str = str.Replace("select", "")

str = str.Replace("insert", "")

str = str.Replace("update", "")

str = str.Replace("delete", "")

str = str.Replace("create", "")

str = str.Replace("drop", "")

str = str.Replace("delcare", "")

str = str.Replace("--", "")

str = str.Replace("@", "")

if (str.Trim().ToString() == "") { str = "null"}

return str

}

/***********************************存储过程****************************************/

/// <summary>

/// 执行存储过程（返回结果集）

/// </summary>

/// <param name="procName">存储过程名</param>

/// <param name="values">参数值</param>

/// <returns></returns>

public static DataTable ExecuteQueryProc(string procName, params SqlParameter[] values)

{

DataSet ds = new DataSet()

SqlCommand cmd = new SqlCommand()

cmd.Connection = Connection

cmd.CommandText = procName

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddRange(values)

SqlDataAdapter da = new SqlDataAdapter(cmd)

da.Fill(ds)

return ds.Tables[0]

}

/// <summary>

/// 执行存储过程，并返回一数据对象 Object

/// </summary>

/// <param name="tsql">T-SQL 语句</param>

/// <param name="values">存储过程所需参数</param>

/// <returns>返回数据对象 Object</returns>

public static object ExecuteProc1(string procname,SqlParameter[] values)

{

SqlCommand cmd = new SqlCommand()

cmd.Connection = Connection

cmd.CommandText = procname

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddRange(values)

return cmd.ExecuteScalar()

}

/// <summary>

/// 执行数据库存储过程，并返回影响数据行数（用于对数据库修改，insert、update、delte）

/// </summary>

/// <param name="procName">存储过程名称</param>

/// <param name="values">调用存储过程需要传入所需参数</param>

/// <returns>返回影响数据行数</returns>

public static int ExecuteProc(string procName, params SqlParameter[] values)

{

SqlCommand cmd = new SqlCommand()

cmd.Connection = Connection

cmd.CommandText = procName

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddRange(values)

return cmd.ExecuteNonQuery()

}

/***********************************存储过程****************************************/

/// <summary>

/// 执行 T-SQL 语句

/// </summary>

/// <param name="sql">T-SQL 语句</param>

/// <param name="values">T-SQL 所需参数</param>

/// <returns>返回影响数据行行数</returns>

public static int ExecuteSql(String sql, SqlParameter[] values)

{

try

{

SqlCommand cmd = new SqlCommand(sql, Connection)

cmd.Parameters.AddRange(values)

int count = cmd.ExecuteNonQuery()

return count

}

catch { return -1}

}

/// <summary>

/// 执行 T-SQL 返回 DataTable

/// </summary>

/// <param name="sql">T-SQL 语句</param>

/// <returns>返回 DataTable</returns>

public static DataTable ExecuteSql(string sql)

{

SqlCommand cmd = new SqlCommand(sql, Connection)

SqlDataAdapter da = new SqlDataAdapter(cmd)

DataSet ds = new DataSet()

da.Fill(ds)

return ds.Tables[0]

}

/// <summary>

/// 返回执行影响数据条数

/// </summary>

/// <param name="safeSql">执行SQL语句</param>

/// <returns>影响行数</returns>

public static int ExecuteCommand(string safeSql)

{

SqlCommand cmd = new SqlCommand(safeSql, Connection)

return cmd.ExecuteNonQuery()

}

/// <summary>

/// 返回执行影响数据条数

/// </summary>

/// <param name="sql"></param>

/// <param name="values"></param>

/// <returns></returns>

public static int ExecuteCommand(string sql, params SqlParameter[] values)

{

try

{

SqlCommand cmd = new SqlCommand(sql, Connection)

cmd.Parameters.AddRange(values)

int count = cmd.ExecuteNonQuery()

return count

}

catch { return -1}

}

/// <summary>

/// 获取数据表 identity

/// </summary>

/// <param name="sql">T-SQL 语句</param>

/// <param name="values">T-SQL 语句所需参数</param>

/// <returns>返回表 identity</returns>

public static long GetIdentity(string sql, params SqlParameter[] values)

{

try

{

SqlCommand cmd = new SqlCommand(sql, Connection)

cmd.Parameters.AddRange(values)

long count = long.Parse(cmd.ExecuteScalar().ToString().Trim())

return count

}

catch { return -1}

}

/// <summary>

/// 得到查询结果指定一个数据

/// </summary>

/// <param name="safeSql">查询语句</param>

/// <returns>返回一个值</returns>

public static Object GetScalar(string safeSql)

{

SqlCommand cmd = new SqlCommand(safeSql, Connection)

Object result = cmd.ExecuteScalar()

return result

}

/// <summary>

/// 得到查询结果数目

/// </summary>

/// <param name="sql">查询语句</param>

/// <param name="values">查询参数</param>

/// <returns>返回一个值</returns>

public static Object GetScalar(string sql, params SqlParameter[] values)

{

SqlCommand cmd = new SqlCommand(sql, Connection)

cmd.Parameters.AddRange(values)

Object result = Convert.ToInt32(cmd.ExecuteScalar())

return result

}

/// <summary>

/// 数据读取对象

/// </summary>

/// <param name="safeSql">查询语句</param>

/// <returns>返回一DataReader</returns>

public static SqlDataReader GetReader(string safeSql)

{

SqlCommand cmd = new SqlCommand(safeSql, Connection)

SqlDataReader reader = cmd.ExecuteReader()

return reader

}

/// <summary>

/// 数据读取对象

/// </summary>

/// <param name="sql">查询语句</param>

/// <param name="values">查询参数</param>

/// <returns>返回一DataReader </returns>

public static SqlDataReader GetReader(string sql, params SqlParameter[] values)

{

SqlCommand cmd = null

cmd.CommandText = sql

cmd.Connection = Connection

cmd.Parameters.AddRange(values)

SqlDataReader reader = cmd.ExecuteReader()

return reader

}

/// <summary>

/// 获取数据库的集合

/// </summary>

/// <param name="safeSql">查询语句</param>

/// <returns>返回一DataTable</returns>

public static DataTable GetDataSet(string safeSql)

{

DataSet ds = new DataSet()

SqlCommand cmd = new SqlCommand(safeSql, Connection)

SqlDataAdapter da = new SqlDataAdapter(cmd)

da.Fill(ds)

return ds.Tables[0]

}

/// <summary>

/// 获取数据库的集合

/// </summary>

/// <param name="sql">查询语句</param>

/// <param name="values">查询参数</param>

/// <returns>返回一DataTable</returns>

public static DataTable GetDataSet(string sql, params SqlParameter[] values)

{

DataSet ds = new DataSet()

SqlCommand cmd = new SqlCommand(sql, Connection)

cmd.Parameters.AddRange(values)

SqlDataAdapter da = new SqlDataAdapter(cmd)

da.Fill(ds)

return ds.Tables[0]

}

/// <summary>

/// 查询数据返回一个数据集

/// </summary>

/// <param name="sql">查询语句</param>

/// <returns>返回一DataSet</returns>

public static DataSet Query(string sql)

{

DataSet ds = new DataSet()

SqlCommand cmd = new SqlCommand(sql, Connection)

SqlDataAdapter da = new SqlDataAdapter(cmd)

da.Fill(ds)

return ds

}

/// <summary>

/// 返回一DataSet

/// </summary>

/// <param name="sql">数据查询语句</param>

/// <param name="values">查询条件参数</param>

/// <returns>返回一DataSet</returns>

public static DataSet Query(string sql, params SqlParameter[] values)

{

DataSet ds = new DataSet()

SqlCommand cmd = new SqlCommand(sql, Connection)

cmd.Parameters.AddRange(values)

SqlDataAdapter da = new SqlDataAdapter(cmd)

da.Fill(ds)

return ds

}

/// <summary>

/// 获得数据表中最大编号数据

/// </summary>

/// <param name="fildName">查询字段名称</param>

/// <param name="tableName">查询表名称</param>

/// <returns>返回最大编号值</returns>

public static int GetMaxID(string fildName, string tableName)

{

string sql = "select top 1(" + fildName + ") from " + tableName + " order by " + fildName + " desc"

SqlCommand cmd = new SqlCommand(sql, Connection)

int mid = Convert.ToInt32(cmd.ExecuteScalar())

return mid

}

/// <summary>

/// 获得数据表中最大编号数据

/// </summary>

/// <param name="fildName">查询字段名称</param>

/// <param name="tableName">查询表名称</param>

/// <returns>返回最大编号值</returns>

public static long GetMaxID(string fildName, string tableName, bool isLong)

{

string sql = "select top 1(" + fildName + ") from " + tableName + " order by " + fildName + " desc"

SqlCommand cmd = new SqlCommand(sql, Connection)

long mid = Convert.ToInt32(cmd.ExecuteScalar())

return mid

}

/// <summary>

/// 查询数据存在性

/// </summary>

/// <param name="sql">数据查询语句</param>

/// <returns>bool</returns>

public static bool Exists(string sql)

{

try

{

SqlCommand cmd = new SqlCommand(sql, Connection)

object o = cmd.ExecuteScalar()

if (o != null)

return true

return false

}

catch (Exception ex)

{

throw ex

}

}

/// <summary>

/// 查询数据存在性

/// </summary>

/// <param name="sql">数据查询语句</param>

/// <param name="values">参数数组</param>

/// <returns>bool</returns>

public static bool Exists(string sql, SqlParameter[] values)

{

try

{

SqlCommand cmd = new SqlCommand(sql, Connection)

cmd.Parameters.AddRange(values)

object o = cmd.ExecuteScalar()

if (o != null)

return true

return false

}

catch (Exception ex)

{

throw ex

}

}

/// <summary>

/// 查询返回一个数据

/// </summary>

/// <param name="sql">SQL 语句</param>

/// <param name="parameters">SQL 参数</param>

/// <returns>Object 数据</returns>

internal static object GetSingle(string sql, SqlParameter[] parameters)

{

try

{

SqlCommand cmd = new SqlCommand(sql, Connection)

cmd.Parameters.AddRange(parameters)

object o = cmd.ExecuteScalar()

return o

}

catch (Exception ex)

{

throw ex

}

}

}

---