关于ASP.NET DBHelper的实用写法！
using System;using System.Collections.Generic;
using System.Text;
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;
namespace Dal
{
public static class DBC
{
private static SqlConnection con;
/// <summary>
/// 连接数据库？
/// </summary>
public static SqlConnection Con
{
get
{
string conString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString.ToString();
if (con == null)
{
con = new SqlConnection(conString);
con.Open();
}
else if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
else if (con.State == System.Data.ConnectionState.Broken)
{
con.Close();
con.Open();
}
return DBC.con;
}
}
/// <summary>
/// 检测是否含有危险字符（防止Sql注入）？
/// </summary>
/// <param name="contents">预检测的内容</param>
/// <returns>返回True或false</returns>
public static bool HasDangerousContents(string contents)
{
bool bReturnValue = false;
if (contents.Length > 0)
{
//convert to lower
string sLowerStr = contents.ToLower();
//RegularExpressions
string sRxStr = @"(\sand\s)|(\sand\s)|(\slike\s)|(select\s)|(insert\s)|(delete\s)|(update\s[\s\S].*\sset)|(create\s)|(\stable)|(<[iframe|/iframe|script|/script])|(')|(\易做图ec)|(\sdeclare)|(\struncate)|(\smaster)|(\sbackup)|(\smid)|(\scount)";
//Match
bool bIsMatch = false;
System.Text.RegularExpressions.Regex sRx = new System.Text.RegularExpressions.Regex(sRxStr);
bIsMatch = sRx.IsMatch(sLowerStr, 0);
if (bIsMatch)
{
bReturnValue = true;
}
}
return bReturnValue;
}
/// <summary>
/// 密码加密？
/// </summary>
/// <param name="password"></param>
/// <returns></returns>
public static string Encrypt(string password)
{
System.Security.Cryptography.MD5 md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
string password_md5 = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(password, "MD5");
return password_md5;
}
/// <summary>
/// 执行sql语句/存储过程，返回影响的行数？
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static int ExecuteCommand(string sqlStr)
{
int result = 0;
SqlCommand cmd = new SqlCommand(sqlStr, Con);
try
{
result = cmd.ExecuteNonQuery();
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return result;
}
/// <summary>
/// 执行sql语句，返回影响的行数？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int ExecuteCommandByParam(string sqlStr, params SqlParameter[] values)
{
int result = 0;
SqlCommand cmd = new SqlCommand(sqlStr, Con);
try
{
if (values != null)
{ //判断是否有参数
foreach (SqlParameter para in values)//循环添加参数
{
cmd.Parameters.Add(para);
}
}
//cmd.Parameters.AddRange(values);
result = cmd.ExecuteNonQuery();
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return result;
}
/// <summary>
/// 执行cmd,返回一个结果？
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public static int ExecuteNonQueryByCmd(SqlCommand cmd)
{
int Result = 0;
try
{
cmd.Connection = Con;
Result = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
return Result;
}
/// <summary>
/// 执行sql语句，返回影响的行数？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sqlStr, string[] paramName, string[] paramValue)
{
int result = 0;
SqlCommand cmd = new SqlCommand(sqlStr, Con);
try
{
for (int i = 0; i < paramName.Length; i++)
{
cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
}
result = cmd.ExecuteNonQuery();
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return result;
}
======================================
请“专业人员”点评/指点！ --------------------编程问答-------------------- /// <summary>
/// 执行sql语句/存储过程，返回第一行的第一列，比如插入新记录，返回自增的id？
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static int GetScalar(string sqlStr)
{
int result = 0;
SqlCommand cmd = new SqlCommand(sqlStr, Con);
try
{
result = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return result;
}
/// <summary>
/// 执行sql语句，返回影响的行数？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int GetScalarByParamArray(string sqlStr, params SqlParameter[] values)
{
int result = 0;
SqlCommand cmd = new SqlCommand(sqlStr, Con);
try
{
cmd.Parameters.AddRange(values);
result = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return result;
}
/// <summary>
/// 执行sql语句，返回一个Object？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static object ExecuteScalarByParam(string sqlStr, string[] paramName, string[] paramValue)
{
object obj = null;
SqlCommand cmd = new SqlCommand(sqlStr, Con);
try
{
for (int i = 0; i < paramName.Length; i++)
{
cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
}
obj = cmd.ExecuteScalar();
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return obj;
}
/// <summary>
/// 返回一个Object？
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetObject(string sql)
{
object obj = null;
try
{
SqlCommand cmd = new SqlCommand(sql, Con);
obj = cmd.ExecuteScalar();
}
catch (SqlException se)
{
throw se;
}
return obj;
}
/// <summary>
/// 返回一个Object？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="values"></param>
/// <returns></returns>
public static object GetObjectByParam(string sqlStr, params SqlParameter[] values)
{
object obj = null;
try
{
SqlCommand cmd = new SqlCommand(sqlStr, Con);
cmd.Parameters.AddRange(values);
obj = cmd.ExecuteScalar();
}
catch (SqlException se)
{
throw se;
}
return obj;
}
--------------------编程问答-------------------- /// <summary>
/// 执行sql语句/存储过程，返回一个DataReader？
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sqlStr)
{
SqlDataReader reader = null;
SqlCommand cmd = new SqlCommand(sqlStr, Con);
try
{
reader = cmd.ExecuteReader();
//reader.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
//con.Close();
}
return reader;
}
/// <summary>
/// 返回一个DataReader？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="values"></param>
/// <returns></returns>
public static SqlDataReader GetReaderByParam(string sqlStr, params SqlParameter[] values)
{
SqlDataReader reader = null;
SqlCommand cmd = new SqlCommand(sqlStr, Con);
try
{
cmd.Parameters.AddRange(values);
reader = cmd.ExecuteReader();
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return reader;
}
/// <summary>
/// 执行sql语句/存储过程，返回一个DataTable？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="values"></param>
/// <returns></returns>
public static DataTable GetDataSet(string sqlStr)
{
SqlCommand cmd = new SqlCommand(sqlStr, Con);
DataSet ds = new DataSet();
SqlDataAdapter da = null;
try
{
da = new SqlDataAdapter(cmd);
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 返回一个DataTable？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="values"></param>
/// <returns></returns>
public static DataTable GetDataSetByParam(string sqlStr, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sqlStr, Con);
DataSet ds = new DataSet();
SqlDataAdapter da =null;
try
{
cmd.Parameters.AddRange(values);
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
//da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 执行带参数SQL语句，返回一张表？
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static DataTable GetDataTableByParam(string sqlStr, string[] paramName, string[] paramValue)
{
DataTable dt = new DataTable();
SqlCommand cmd = null; ;
SqlDataAdapter da = null;
try
{
cmd = new SqlCommand(sqlStr, Con);
for (int i = 0; i < paramName.Length; i++)
{
cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
}
da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return dt;
}
/// <summary>
/// 执行带参数的存储过程，返回一张表？
/// </summary>
/// <param name="produre"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static DataTable GetDataTableByProdure(string produre, string[] paramName, string[] paramValue)
{
DataTable dt = new DataTable();
SqlCommand cmd = null;
SqlDataAdapter da = null;
try
{
cmd = new SqlCommand(produre, Con);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < paramName.Length; i++)
{
cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
}
da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
catch (SqlException se)
{
throw se;
}
finally
{
cmd.Dispose();
con.Close();
}
return dt;
}
/// <summary>
/// 执行SqlCommand命令，并返回一张表?
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public static DataTable GetDataTableByCmd(SqlCommand cmd)
{
DataTable dt = new DataTable();
cmd.Connection = con;
SqlDataAdapter da = null;
try
{
da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
catch (SqlException se)
{
throw se;
}
finally
{
da.Dispose();
}
return dt;
}
/// <summary>
/// 执行带参数的存储过程，并返回受影响的行数？
/// </summary>
/// <param name="procedureName"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static int ExecuteProcedure(string procedureName, string[] paramName, string[] paramValue)
{
SqlCommand cmd = new SqlCommand(procedureName, Con);
for (int i = 0; i < paramName.Length; i++)
{
cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
}
cmd.CommandType = CommandType.StoredProcedure;
int result = cmd.ExecuteNonQuery();
return result;
}
/// <summary>
/// 返回影响行数？
/// </summary>
/// <param name="procedureName"></param>
/// <returns></returns>
public static int ExecuteProcedureByName(string procedureName)
{
SqlCommand cmd = new SqlCommand(procedureName, Con);
cmd.CommandType = CommandType.StoredProcedure;
int result = cmd.ExecuteNonQuery();
con.Close();
return result;
}
}
}
--------------------编程问答-------------------- --------------------编程问答-------------------- 楼主辛苦 --------------------编程问答-------------------- --------------------编程问答-------------------- 你不知道代码怎么粘帖吗
看起来真够乱的 --------------------编程问答-------------------- 还在用ado.net,.... --------------------编程问答-------------------- 你想问什么?贴了这么一堆... --------------------编程问答--------------------
ado.net好像还能用会儿吧。这位大侠莫非用linq？ --------------------编程问答-------------------- 很好很复杂 我们的DBhelper 都很短很实用的 不过没有你功能多
补充：.NET技术 , ASP.NET
