烂菜博
烂菜开源Blog--ASP.NET数据库操作基础类库
烂菜 发表于 2008-06-21 10:00:04
我的Blog重建工作今天开始,我将一步一步的贴出我Blog的所有制作工作,希望有经验的朋友来提提建议。
新建的一个Common类
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;
/// <summary>
/// ADO.NET数据库操作基础类。
/// </summary>
public class Common
{
//数据库连接字符串
protected static string connectionString = ConfigurationManager.ConnectionStrings["BlogStrings"].ConnectionString;
public Common()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 执行两条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLString1"></param>
/// <param name="SQLString2"></param>
public static void ExecuteSqlTran(string SQLString1, string SQLString2)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
cmd.CommandText = SQLString1;
cmd.ExecuteNonQuery();
cmd.CommandText = SQLString2;
cmd.ExecuteNonQuery();
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务,每条语句以“;”分割。
/// </summary>
/// <param name="SQLStringList"></param>
public static void ExecuteSqlTran(string SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
string[] split = SQLStringList.Split(new Char[] { ';' });
foreach (string strsql in split)
{
if (strsql.Trim() != "")
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString"></param>
/// <param name="content"></param>
/// <returns></returns>
public static int ExecuteSql(string SQLString, string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段
/// </summary>
/// <param name="strSQL"></param>
/// <param name="fs"></param>
/// <returns></returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(整数)。
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int GetCount(string strSQL)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader result = cmd.ExecuteReader();
int i = 0;
while (result.Read())
{
i = result.GetInt32(0);
}
result.Close();
return i;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行查询语句,返回SqlDataReader
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
SqlDataReader myReader;
try
{
connection.Open();
myReader = cmd.ExecuteReader();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
#region 存储过程操作
/// <summary>
/// 运行存储过程
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
return returnReader;
}
}
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
SqlConnection connection = new SqlConnection(connectionString);
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
#endregion
}
风※花※雪※月
烂菜 发表于 2008-03-20 23:14:23
风
秋风飘絮归无期,执手蓦然爱无意。
夜阑心火灭余生,温热红唇泪凄凄。
苦短人生系两地,白首空梦难相忆。
素色衣裙几番影,风流荡荡总相离。
花
凝眸梅花窜新愁,细听燕语夜不休。
谁复锦书过关山,怅驾兰舟觅江头。
晚风岁末橘子洲,红叶香草拂挽袖。
阑干百里海棠心,遥岑新梦登南楼。
雪
江山北望雪漫漫,潇潇落叶易水寒。
孤影白昼负西风,目送飘蓬夕阳断。
红颜易老此恨难,云雨相逢诉平安。
醉里纵横芳草径,长宿青楼几时还。
月
轩窗小景斗回廊,邻家有女夜梳妆。
悠悠岁月盘绕指,半世旷逸半世荒。
幽林古道花怒放,平沙西岸月如霜。
憨月不识人满面,犹照玉楼掩埋香。
---烂菜--1--14--
GRAY
烂菜 发表于 2008-03-11 15:00:42
为什么衣服是灰色的才能让人有回忆?
为什么脸是灰色的才能让人有抚摸的悸动?
为什么天是灰色的才能有下雨的冲动?
为什么灰色的才是寂寞的?
为什么灰色的才是无聊的?
为什么灰色的才是缺陷的?
为什么灰色的才是松弛的?
灰色的空气;
灰色的大地;
灰色的眼睛;
灰色的窗户;
我喜欢灰色,就像喜欢等待一样……
等待灰色的朦胧慢慢拭去后明亮的明天……
