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 MDK.NT.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;
}
}
}
}
这种类写了又写,故作记录。