C#操作MSSQL类
⽇常⼯作经常操作数据库于是写了这个本⼈经常操作MSSQL数据库的⼀个类，希望有朋友能⽤到，代码纯粹只⾯向⾃⼰的常⽤操作1 如果是ASP.NET ⽤户配置WEB.CONFIG⽂件
[codes=c#] <appSettings>
<!–当前系统MSSQL数据库⽤户–>
<add key="mssql" value="Server=127.0.0.1;UID=sa;PWD=;Database=DB"/>
</appSettings>[/codes]
2 JunvalMSSql的具体实现
[codes=c#]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语句
/**//// <summary>
/// 默认构造函数
/// 默认在fig中配置 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();
}
/**//// <summary>
/// mssql 连接字符串构造
/// </summary>
/// <param name="connStr">sql server连接字符串</param>
public JunvalMSSql(string connStr)
{
connString = connStr;
}
/**//// <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;
}
/**//// <summary>
/// 设置connString属性
/
// </summary>
public string ConnString
{
get
{
return connString;
}
set
{
connString = value;
}
}
/**//// <summary>
/// 设置sqlString属性
/// </summary>
public string SqlString
{
get
{
return sqlString;
}
set
{
sqlString = value;
}
}
/**//// <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();
}
sqltransaction什么意思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;
}
/**//// <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);
}
/**//// <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;
}
/**//// <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();
}
/
**//// <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;
}
/**//// <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) {
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论