using System;
|
using System.Collections.Generic;
|
|
using System.Text;
|
using System.Data.SqlClient;
|
using System.Data;
|
|
namespace DIXPad.DAL
|
{
|
public class SQLDataProvider:DataProvider
|
{
|
int _ConnectTimeOut { get { return 180; } }
|
public SQLDataProvider()
|
{
|
_DBConnectionString = "";
|
}
|
|
string _DBConnectionString { get; set; }
|
|
public SQLDataProvider(string key)
|
{
|
string connectionString = "";
|
try {
|
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[key].ConnectionString;
|
}
|
catch(Exception er){
|
throw new Exception("错误C0001：未发现配置" + key);
|
}
|
_DBConnectionString = connectionString;
|
}
|
void DisposeConnection(SqlConnection con)
|
{
|
try
|
{
|
if (con != null)
|
{
|
con.Close();
|
con.Dispose();
|
}
|
}
|
catch (Exception er)
|
{
|
throw er;
|
}
|
}
|
public override object ExecuteStoreProcedureScalar(Dictionary<string, object> parametersInstance, string storedProcedureName)
|
{
|
if (storedProcedureName == null)
|
throw new Exception("存储过程名称不可为Null");
|
using (SqlConnection con = new SqlConnection(_DBConnectionString))
|
{
|
try
|
{
|
//设置Sql
|
SqlCommand cmd = new SqlCommand(storedProcedureName, con);
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.CommandTimeout = _ConnectTimeOut;
|
if (parametersInstance != null)
|
{
|
foreach (KeyValuePair<string, object> item in parametersInstance)
|
{
|
SqlParameter parm = new SqlParameter(item.Key, item.Value);
|
cmd.Parameters.Add(parm);
|
}
|
}
|
if (con.State == ConnectionState.Closed)
|
con.Open();
|
|
object r = cmd.ExecuteScalar();
|
|
return r;
|
}
|
catch (Exception er)
|
{
|
DisposeConnection(con);
|
throw er;
|
}
|
finally {
|
DisposeConnection(con);
|
}
|
}
|
}
|
|
public override string GetStoreProcName(int storeprocId)
|
{
|
DataTable dt = ExecuteSqlForDataTable("SELECT name FROM sysobjects WHERE type='p' and id=" + storeprocId.ToString());
|
string x = "";
|
if (dt.Rows.Count > 0)
|
x = dt.Rows[0][0].ToString();
|
return x;
|
}
|
|
public override DataSet ExecuteStoreProcedureDataSetWithTableType(string tableTypeKey, DataTable table, Dictionary<string, object> parametersInstance, string storedProcedureName)
|
{
|
DataSet ds = new DataSet(Guid.NewGuid().ToString());
|
using (SqlConnection con = new SqlConnection(_DBConnectionString))
|
{
|
try
|
{
|
//设置Sql
|
SqlCommand cmd = new SqlCommand(storedProcedureName, con);
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.CommandTimeout = _ConnectTimeOut;
|
if (parametersInstance != null)
|
{
|
foreach (KeyValuePair<string,object> item in parametersInstance)
|
{
|
SqlParameter parm = new SqlParameter(item.Key, item.Value);
|
cmd.Parameters.Add(parm);
|
}
|
}
|
if (tableTypeKey != null && tableTypeKey.Trim() != "" && table != null)
|
{
|
SqlParameter parm = new SqlParameter(tableTypeKey,SqlDbType.Structured);
|
parm.Value = table;
|
cmd.Parameters.Add(parm);
|
}
|
SqlDataAdapter sda = new SqlDataAdapter(cmd);
|
sda.Fill(ds);
|
return ds;
|
}
|
catch (Exception er)
|
{
|
DisposeConnection(con);
|
throw er;
|
}
|
finally
|
{
|
DisposeConnection(con);
|
}
|
}
|
}
|
|
public override object ExecuteStoreProcedureScalarWithTableType(string tableTypeKey, DataTable table, Dictionary<string, object> parametersInstance, string storedProcedureName)
|
{
|
using (SqlConnection con = new SqlConnection(_DBConnectionString))
|
{
|
try
|
{
|
//设置Sql
|
SqlCommand cmd = new SqlCommand(storedProcedureName, con);
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.CommandTimeout = _ConnectTimeOut;
|
if (parametersInstance != null)
|
{
|
foreach (KeyValuePair<string,object> item in parametersInstance)
|
{
|
SqlParameter parm = new SqlParameter(item.Key, item.Value);
|
cmd.Parameters.Add(parm);
|
}
|
}
|
if (tableTypeKey != null && tableTypeKey.Trim() != "" && table != null)
|
{
|
SqlParameter parm = new SqlParameter(tableTypeKey,SqlDbType.Structured);
|
parm.Value = table;
|
cmd.Parameters.Add(parm);
|
}
|
if (con.State == ConnectionState.Closed)
|
con.Open();
|
return cmd.ExecuteScalar();
|
}
|
catch (Exception er)
|
{
|
DisposeConnection(con);
|
throw er;
|
}
|
finally
|
{
|
DisposeConnection(con);
|
}
|
}
|
}
|
|
public override System.Data.DataSet ExecStoredProcedure(Dictionary<string, object> parametersInstance, string storedProcedureName)
|
{
|
using (SqlConnection con = new SqlConnection(_DBConnectionString))
|
{
|
try
|
{
|
//设置Sql
|
SqlCommand cmd = new SqlCommand(storedProcedureName, con);
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
if (parametersInstance != null)
|
{
|
foreach (KeyValuePair<string,object> item in parametersInstance)
|
{
|
SqlParameter parm = new SqlParameter(item.Key, item.Value);
|
cmd.Parameters.Add(parm);
|
}
|
}
|
DataSet ds = new DataSet();
|
cmd.CommandTimeout = _ConnectTimeOut;
|
SqlDataAdapter sdap = new SqlDataAdapter(cmd);
|
|
sdap.Fill(ds);
|
return ds;
|
}
|
catch (Exception er)
|
{
|
DisposeConnection(con);
|
throw er;
|
//return null;
|
}
|
finally
|
{
|
DisposeConnection(con);
|
}
|
}
|
}
|
static object locker = new object();
|
public override bool ExecSqlBulkCopyToDBByTransaction(DataTable copying2dbData, string destinationTableName, Dictionary<string, string> sourceToDestinationFieldsMapping)
|
{
|
lock (locker) {
|
//构建多个数据库连接
|
SqlConnection connection = null;
|
SqlBulkCopy bcp = null;
|
SqlTransaction transaction = null;
|
bool r = false;
|
try
|
{
|
connection = new SqlConnection(_DBConnectionString);
|
try
|
{
|
connection.Open();
|
}
|
catch
|
{
|
throw new Exception("连接数据库失败!请检查连接字符串" + _DBConnectionString);
|
}
|
transaction = connection.BeginTransaction();
|
bcp = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);
|
bcp.BatchSize = 10000;
|
bcp.BulkCopyTimeout = 60;
|
//依次使用数据库连接导入数据
|
foreach (KeyValuePair<string, string> kv in sourceToDestinationFieldsMapping)
|
{
|
//列映射
|
bcp.ColumnMappings.Add(kv.Key, kv.Value);
|
}
|
bcp.DestinationTableName = destinationTableName;
|
bcp.WriteToServer(copying2dbData);
|
transaction.Commit();
|
r= true;
|
}
|
catch (Exception er)
|
{
|
string error = er.Message;
|
try { } catch (Exception ex) { error += "--" + ex.Message; }
|
if (transaction != null)
|
transaction.Rollback();
|
DisposeConnection(connection);
|
throw new Exception(error) ;
|
}
|
finally
|
{
|
DisposeConnection(connection);
|
|
}
|
return r;
|
}
|
}
|
|
public override System.Data.DataTable ExecStoreProcedureForGettingTable(Dictionary<string, object> parametersInstance, string storedProcedureName)
|
{
|
using (SqlConnection con = new SqlConnection(_DBConnectionString))
|
{
|
try
|
{
|
//设置Sql
|
SqlCommand cmd = new SqlCommand(storedProcedureName, con);
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.CommandTimeout = _ConnectTimeOut;
|
if (parametersInstance != null)
|
{
|
foreach (KeyValuePair<string, object> item in parametersInstance)
|
{
|
SqlParameter parm = new SqlParameter(item.Key, item.Value);
|
cmd.Parameters.Add(parm);
|
}
|
}
|
DataTable dt = new DataTable(Guid.NewGuid().ToString());
|
SqlDataAdapter sdap = new SqlDataAdapter(cmd);
|
sdap.Fill(dt);
|
return dt;
|
}
|
catch (Exception er)
|
{
|
DisposeConnection(con);
|
throw er;
|
}
|
finally
|
{
|
DisposeConnection(con);
|
}
|
}
|
}
|
|
public override DataTable ExecuteSqlForDataTable(string sql)
|
{
|
using (SqlConnection con = new SqlConnection(_DBConnectionString))
|
{
|
try
|
{
|
//设置Sql
|
SqlCommand cmd = new SqlCommand(sql, con);
|
cmd.CommandType = CommandType.Text;
|
cmd.CommandTimeout = _ConnectTimeOut;
|
DataTable dt = new DataTable(Guid.NewGuid().ToString());
|
SqlDataAdapter sdap = new SqlDataAdapter(cmd);
|
sdap.Fill(dt);
|
return dt;
|
}
|
catch (Exception er)
|
{
|
DisposeConnection(con);
|
throw er;
|
}
|
finally
|
{
|
DisposeConnection(con);
|
}
|
}
|
}
|
public override DataSet ExecuteSqlForDataSet(string sql)
|
{
|
using (SqlConnection con = new SqlConnection(_DBConnectionString))
|
{
|
try
|
{
|
//设置Sql
|
SqlCommand cmd = new SqlCommand(sql, con);
|
cmd.CommandType = CommandType.Text;
|
cmd.CommandTimeout = _ConnectTimeOut;
|
DataSet ds = new DataSet(Guid.NewGuid().ToString());
|
SqlDataAdapter sdap = new SqlDataAdapter(cmd);
|
sdap.Fill(ds);
|
return ds;
|
}
|
catch (Exception er)
|
{
|
DisposeConnection(con);
|
throw er;
|
}
|
finally
|
{
|
DisposeConnection(con);
|
}
|
}
|
}
|
public override DataTable ExecStoreProcedureWithTableType(string tableTypeKey,DataTable table, Dictionary<string, object> parametersInstance, string storedProcedureName)
|
{
|
using (SqlConnection con = new SqlConnection(_DBConnectionString))
|
{
|
try
|
{
|
//设置Sql
|
SqlCommand cmd = new SqlCommand(storedProcedureName, con);
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.CommandTimeout = _ConnectTimeOut;
|
if (parametersInstance != null)
|
{
|
foreach (KeyValuePair<string,object> item in parametersInstance)
|
{
|
SqlParameter parm = new SqlParameter(item.Key, item.Value);
|
cmd.Parameters.Add(parm);
|
}
|
}
|
if (table != null)
|
{
|
SqlParameter parm = new SqlParameter(tableTypeKey, table);
|
cmd.Parameters.Add(parm);
|
}
|
DataTable dt = new DataTable(Guid.NewGuid().ToString());
|
SqlDataAdapter sdap = new SqlDataAdapter(cmd);
|
sdap.Fill(dt);
|
return dt;
|
}
|
catch (Exception er)
|
{
|
DisposeConnection(con);
|
throw er;
|
}
|
finally
|
{
|
DisposeConnection(con);
|
}
|
}
|
}
|
|
}
|
}
|
