在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
using System; using System.Collections.Generic; using System.Collections.Specialized; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Net; using System.ServiceModel.Web; using System.Text; namespace Common { public class DBHelper { private string m_dbs; /// <summary> /// 构造函数 /// </summary> public DBHelper() { } /// <summary> /// 构造函数 /// </summary> /// <param name="connectString">数据库连接字符串</param> public DBHelper (string connectString) { m_dbs = connectString; } public string ConnectString { get { return m_dbs; } set { m_dbs = value; } } /// <summary> /// 插入并获取ID /// </summary> /// <param name="connectString">数据库连接字符串</param> /// <param name="commandStr">SQL语句 包含获取ID的命令</param> /// <returns>新记录的ID</returns> public int ExecuteScalarInsert(string connectString,string commandStr) { string err = ""; int ret = 0; if(string.IsNullOrEmpty (connectString )) { return -1; } using (SqlConnection dbc = new SqlConnection(connectString)) { SqlCommand insert = new SqlCommand(commandStr, dbc); try { dbc.Open(); ret = Convert.ToInt32(insert.ExecuteScalar()); } catch(Exception ex) { err = ex.Message; } } if (err.Length > 0) { throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError); } else { return ret; } } /// <summary> /// 插入并获取ID /// </summary> /// <param name="commandStr">SQL语句 包含获取ID的命令</param> /// <returns>新记录的ID</returns> public int ExecuteScalarInsert(string commandStr) { string err = ""; int ret = 0; if (string.IsNullOrEmpty(m_dbs )) { return -1; } using (SqlConnection dbc = new SqlConnection(m_dbs)) { SqlCommand insert = new SqlCommand(commandStr, dbc); try { dbc.Open(); ret = Convert.ToInt32(insert.ExecuteScalar()); } catch(Exception ex) { err = ex.Message; } } if (err.Length > 0) { throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError); } else { return ret; } } /// <summary> /// 添加、删除、更新操作 /// </summary> /// <param name="connectString">数据库连接字符串</param> /// <param name="commandstr">SQL语句</param> /// <returns>受影响的行数</returns> public int CommandExecuteNonQuery(string connectString, string commandstr) { if(string .IsNullOrEmpty (connectString)||string .IsNullOrEmpty (commandstr )) { return -1; } string err = ""; int result = 0; using (SqlConnection dbc = new SqlConnection(connectString)) { SqlCommand command = new SqlCommand(commandstr, dbc); try { dbc.Open(); result = command.ExecuteNonQuery(); } catch(Exception ex) { err = ex.Message; } } if (err.Length > 0) { throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError); } else { return result; } } /// <summary> /// 添加、删除、更新操作 /// </summary> /// <param name="commandstr">SQL语句</param> /// <returns>受影响的行数</returns> public int CommandExecuteNonQuery(string commandstr) { if (string.IsNullOrEmpty(m_dbs )||string .IsNullOrEmpty (commandstr )) { return -1; } string err = ""; int result = 0; using (SqlConnection dbc = new SqlConnection(m_dbs)) { SqlCommand command = new SqlCommand(commandstr, dbc); try { dbc.Open(); result = command.ExecuteNonQuery(); } catch (Exception ex) { err = ex.Message; } } if (err.Length > 0) { throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError); } else { return result; } } /// <summary> /// 执行查询 /// </summary> /// <param name="connectString">数据库连接字符串</param> /// <param name="selectstr">SQL语句</param> /// <returns>数据表</returns> public DataTable GetCommand(string connectString, string selectstr) { if(string .IsNullOrEmpty (connectString )||string .IsNullOrEmpty (selectstr )) { return null; } DataTable table = new DataTable(); string err = ""; using (SqlConnection dbc = new SqlConnection(connectString)) { try { SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand(selectstr, dbc); adapter.Fill(table); } catch(Exception ex) { err = ex.Message; } } if (err.Length > 0) { throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError); } else { return table; } } /// <summary> /// 执行查询 /// </summary> /// <param name="selectstr">SQL语句</param> /// <returns>数据表</returns> public DataTable GetCommand(string selectstr) { if (string.IsNullOrEmpty(m_dbs)) { return null; } DataTable table = new DataTable(); string err = ""; using (SqlConnection dbc = new SqlConnection(m_dbs)) { try { SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand(selectstr, dbc); adapter.Fill(table); } catch(Exception ex) { err = ex.Message; } } if (err.Length > 0) { throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError); } else { return table; } } /// <summary> /// 执行一个事务 /// </summary> /// <param name="commands">事务中要执行的所有语句</param> /// <returns>事务是否成功执行</returns> public bool ExecuteTransaction(List <string >commands) { if(string .IsNullOrEmpty (m_dbs)||commands ==null ) { return false; } string err = ""; bool ret = false; using (SqlConnection dbc = new SqlConnection(m_dbs)) { dbc.Open(); using (SqlTransaction transaction = dbc.BeginTransaction()) { try { foreach (string commandstr in commands) { SqlCommand command = new SqlCommand(commandstr, dbc); command.Transaction = transaction; command.ExecuteNonQuery(); } transaction.Commit(); ret = true; } catch (Exception ex) { transaction.Rollback(); err = ex.Message; } } } if (err.Length > 0) { throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError); } else { return ret; } } /// <summary> /// 执行一个事务 /// </summary> /// <param name="connectString">数据库连接字符串</param> /// <param name="commands">事务中要执行的所有语句</param> /// <returns>事务是否成功执行</returns> public bool ExecuteTransaction(string connectString,List<string> commands) { if (string.IsNullOrEmpty(connectString) || commands == null) { return false; } string err = ""; bool ret = false; using (SqlConnection dbc = new SqlConnection(connectString)) { dbc.Open(); using (SqlTransaction transaction = dbc.BeginTransaction()) { try { foreach (string commandstr in commands) { SqlCommand command = new SqlCommand(commandstr, dbc); command.Transaction = transaction; command.ExecuteNonQuery(); } transaction.Commit(); ret = true; } catch (Exception ex) { transaction.Rollback(); err = ex.Message; } } } if (err.Length > 0) { throw new WebFaultException<SimpleException>(new SimpleException() { Message = err }, HttpStatusCode.InternalServerError); } else { return ret; } } } } 这种类写了又写,故作记录。 |
请发表评论