日常工作经常操作数据库 于是写了这个本人经常操作MSSQL数据库的一个类，希望有朋友能用到，代码纯粹只面向自己的常用操作
1 如果是ASP.NET 用户 配置WEB.CONFIG文件
<appSettings>
<!--当前系统MSSQL数据库用户-->
<add key="mssql" value="Server=127.0.0.1;UID=sa;PWD=;Database=DB"/>
</appSettings>
2 JunvalMSSql的具体实现
using System;
using System.Data;
using System.Data.SqlClient;
namespace Junval
{
//JunvalMSSql类 专用操作 SQL SERVER数据用户常用方法#region JunvalMSSql类 专用操作 SQL SERVER数据用户常用方法
/**//// <summary>
/// JunvalMSSql 的摘要说明。
/// JunvalMSSql类 专用操作 SQL SERVER数据用户常用方法
/// 开发者: 徐忠威 Junval
/// 建类时间: 2007年2月25日
/// 开发平台: .NET 1.1 C#
/// </summary>
public class JunvalMSSql
{
private string connString;//连接sql server 字符串
private string sqlString; //执行的sql语句</p> <p> /**//// <summary>
/// 默认构造函数
/// 默认在Web.config中配置 add key="mssql" value="Server=127.0.0.1;UID=sa;PWD=;Database="
/// </summary>
public JunvalMSSql()
{
//
// TODO: 在此处添加构造函数逻辑
//默认连接字符串以由webconfig决定
connString = System.Configuration.ConfigurationSettings.AppSettings["mssql"].ToString();
}</p> <p> /**//// <summary>
/// mssql 连接字符串构造
/// </summary>
/// <param name="connStr">sql server连接字符串</param>
public JunvalMSSql(string connStr)
{
connString = connStr;
}</p> <p> /**//// <summary>
/// 由需要执行的sql语句 与 sql server连接字符串构造成
/// </summary>
/// <param name="executeSql">需要执行的sql语句</param>
/// <param name="sqlConnStr">连接sql server的字符串</param>
public JunvalMSSql(string executeSql, string sqlConnStr)
{
connString = sqlConnStr;
sqlString = executeSql;
}</p> <p> /**//// <summary>
/// 设置connString属性
/// </summary>
public string ConnString
{
get
{
return connString;
}
set
{
connString = value;
}
}</p> <p> /**//// <summary>
/// 设置sqlString属性
/// </summary>
public string SqlString
{
get
{
return sqlString;
}
set
{
sqlString = value;
}
}</p> <p> /**//// <summary>
/// 执行SQL语句 由JunvalMSSql.SqlString属性设置
/// </summary>
/// <returns>执行成功返回"1" 失败返回错误信息</returns>
public string ExecuteSql()
{
string ls_ret ="";
SqlConnection Conn = new SqlConnection(connString);
try
{
Conn.Open();
}
catch(SqlException ex)
{
ls_ret = "(0)["+ex.Errors.ToString()+"]"+ex.Message;
goto
ErrEnd;
}
SqlTransaction myTrans = Conn.BeginTransaction();
try
{
SqlCommand Cmd = new SqlCommand(sqlString,Conn,myTrans);
Cmd.ExecuteNonQuery();
myTrans.Commit();
ls_ret = "1";
}
catch(SqlException ex)
{
ls_ret = "(-1)["+ex.Errors.ToString()+"]" + ex.Message.ToString();
myTrans.Rollback();</p> <p> }
finally
{
Conn.Close();
}
ErrEnd:
return ls_ret;
}
/**//// <summary>
/// 执行带参数sql语句
/// </summary>
/// <param name="excuteSql">需要执行的sql语句</param>
/// <returns>执行成功返回"1" 失败返回错误信息</returns>
public string ExecuteSql(string excuteSql)
{
this.sqlString = excuteSql;
return ExecuteSql();
}
/**//// <summary>
/// 执行带参数的sql语句
/// </summary>
/// <param name="Parms">sqlString中的参数</param>
/// <returns>执行成功返回"1" 失败返回错误信息</returns>
public string ExecuteSqlParms(SqlParameter[] Parms)
{
string ls_ret = "";
SqlConnection Conn = new SqlConnection(connString);
try
{
Conn.Open();
}
catch(SqlException ex)
{
ls_ret = "(0)["+ex.Errors.ToString()+"]"+ex.Message;
goto
ErrEnd;
}
SqlTransaction myTrans = Conn.BeginTransaction();
try
{
SqlCommand Cmd = new SqlCommand(sqlString,Conn,myTrans);
if(Parms != null)
{
foreach(SqlParameter Paramter in Parms)
{
Cmd.Parameters.Add(Paramter);
}
}
Cmd.ExecuteNonQuery();
myTrans.Commit();
ls_ret = "1";
}
catch(SqlException ex)
{
ls_ret = "(-1)["+ex.Errors.ToString()+"]"+ex.Message;
myTrans.Rollback();
}
finally
{
Conn.Close();
}
ErrEnd:
return ls_ret;
}</p> <p>
/**//// <summary>
/// 执行带参数的sql语句
/// </summary>
/// <param name="executeSql">执行的语句</param>
/// <param name="Parms">执行语句中的参数</param>
/// <returns>执行成功返回"1",失败返回错误信息</returns>
public string ExecuteSqlParms(string executeSql, SqlParameter[] Parms)
{
this.sqlString = executeSql;
return ExecuteSqlParms(Parms);
}</p> <p> /**//// <summary>
/// 执行多条sql语句
/// </summary>
/// <param name="Sqls">string[] sql语句数组</param>
/// <returns>执行成功返回"1" 失败返回错误信息</returns>
public string ExecuteSqls(string[] Sqls)
{
string ls_ret ="";
SqlConnection Conn = new SqlConnection(connString);
try
{
Conn.Open();
}
catch(SqlException ex)
{
ls_ret = "(0)["+ex.Errors.ToString()+"]"+ex.Message;
goto
ErrEnd;
}
SqlTransaction myTrans = Conn.BeginTransaction();
try
{
SqlCommand Cmd = Conn.CreateCommand();
Cmd.Transaction = myTrans;
if(Sqls != null)
{
foreach(string sql in Sqls)
{
Cmd.CommandText = sql;
Cmd.ExecuteNonQuery();
}
}
myTrans.Commit();
ls_ret = "1";
}
catch(SqlException ex)
{
ls_ret = "(-1)["+ex.Errors.ToString()+"]"+ex.Message;
myTrans.Rollback();
}
finally
{
Conn.Close();
}
ErrEnd:
return ls_ret;
}</p> <p> /**//// <summary>
/// 执行无参数的过程
/// </summary>
/// <returns>执行成功返回"1",失败返回错误信息</returns>
public string ExecuteProcedure()
{
string ls_ret ="";
SqlConnection Conn = new SqlConnection(connString);
try
{
Conn.Open();
}
catch(SqlException ex)
{
ls_ret ="(0)["+ex.Errors.ToString()+"]"+ ex.Message;
goto
ErrEnd;
}
SqlTransaction myTrans = Conn.BeginTransaction();
try
{
SqlCommand Cmd = new SqlCommand(sqlString,Conn,myTrans);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.ExecuteNonQuery();
myTrans.Commit();
ls_ret = "1";
}
catch(SqlException ex)
{
ls_ret = "(-1)["+ex.Errors.ToString()+"]"+ex.Message;
myTrans.Rollback();
}
finally
{
Conn.Close();
}
ErrEnd:
return ls_ret;
}
/**//// <summary>
/// 执行无参数过程
/// </summary>
/// <param name="ProcedureName">过程名字</param>
/// <returns>执行成功返回"1",失败返回错误信息</returns>
public string ExecuteProcedure(string ProcedureName)
{
this.sqlString = ProcedureName;
return ExecuteProcedure();
}</p> <p> /**//// <summary>
/// 返回一个DataSet对象,默认返回Table[0]名字为"MyData"
/// </summary>
/// <returns>执行成功返回一个dataset对象,失败返回null</returns>
public DataSet GetDataSet()
{
DataSet RetDs = new DataSet();
SqlConnection Conn = new SqlConnection(connString);
SqlDataAdapter Adapter = new SqlDataAdapter(sqlString,Conn);
try
{
Conn.Open();
Adapter.Fill(RetDs,"MyData");
}
catch
{
RetDs = null;
}
finally
{
Conn.Close();
}
return RetDs;
}</p> <p> /**//// <summary>
/// 返回一个DataSet对象,默认返回Table[0]名字为"MyData"
/// </summary>
/// <param name="executeSql">查询dataSet的sql语句</param>
/// <returns>执行成功返回dataset对象 失败返回null</returns>
public DataSet GetDataSet(string executeSql)
{
sqlString = executeSql;
return GetDataSet();
}
/**//// <summary>
/// 返回一个DataSet对象,table[0] 为 "MyData"
/// </summary>
/// <param name="executeSql">执行查询的sql语句</param>
/// <param name="sqlConnString">连接sql server字符串</param>
/// <returns>返回DataSet对象</returns>
public DataSet GetDataSet(string executeSql,string sqlConnString)
{
sqlString = executeSql;
connString = sqlConnString;
return GetDataSet();
}</p> <p> /**//// <summary>
/// 返回一个SqlDataReader对象
/// </summary>
/// <returns></returns>
public SqlDataReader GetDataReader()
{
SqlDataReader RetDataReader;
SqlConnection Conn = new SqlConnection(connString);
try
{
Conn.Open();
SqlCommand Cmd = new SqlCommand(sqlString,Conn);
RetDataReader = Cmd.ExecuteReader();
}
catch
{
RetDataReader = null;
}
finally
{
Conn.Close();
}
return RetDataReader;
}</p> <p> /**//// <summary>
/// 返回一个SqlDataReader对象
/// </summary>
/// <param name="selectSql">执行查询的sql语句</param>
/// <returns></returns>
public SqlDataReader GetDataReader(string selectSql)
{
sqlString = selectSql;
return GetDataReader();
}
/**//// <summary>
/// 返回一个SqlDataReader对象
/// </summary>
/// <param name="selectSql">执行查询的sql语句</param>
/// <param name="sqlConnString">sql server连接字符串</param>
/// <returns></returns>
public SqlDataReader GetDataReader(string selectSql,string sqlConnString)
{
sqlString = selectSql;
connString = sqlConnString;
return GetDataReader();
}
/**//// <summary>
/// 返回一个SqlConnection对象
/// </summary>
/// <returns></returns>
public SqlConnection Connection()
{
SqlConnection Conn = new SqlConnection(connString);
return Conn;
}
}</p> <p>}
