DIXWeb.git - Gitblit

duan

2024-08-21 f71a02229c1ba00fbecaead19256593ffb052753

提交 \| 用户 \| age
91d475	1	using System;
J	2	using System.Collections.Generic;
	3
	4	using System.Text;
	5	using System.Data.SqlClient;
	6	using System.Data;
	7
	8	namespace DIXPad.DAL
	9	{
	10	public class SQLDataProvider:DataProvider
	11	{
	12	int _ConnectTimeOut { get { return 180; } }
	13	public SQLDataProvider()
	14	{
	15	_DBConnectionString = "";
	16	}
	17
	18	string _DBConnectionString { get; set; }
	19
	20	public SQLDataProvider(string key)
	21	{
	22	string connectionString = "";
	23	try {
	24	connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[key].ConnectionString;
	25	}
	26	catch(Exception er){
	27	throw new Exception("错误C0001：未发现配置" + key);
	28	}
	29	_DBConnectionString = connectionString;
	30	}
	31	void DisposeConnection(SqlConnection con)
	32	{
	33	try
	34	{
	35	if (con != null)
	36	{
	37	con.Close();
	38	con.Dispose();
	39	}
	40	}
	41	catch (Exception er)
	42	{
	43	throw er;
	44	}
	45	}
	46	public override object ExecuteStoreProcedureScalar(Dictionary<string, object> parametersInstance, string storedProcedureName)
	47	{
	48	if (storedProcedureName == null)
	49	throw new Exception("存储过程名称不可为Null");
	50	using (SqlConnection con = new SqlConnection(_DBConnectionString))
	51	{
	52	try
	53	{
	54	//设置Sql
	55	SqlCommand cmd = new SqlCommand(storedProcedureName, con);
	56	cmd.CommandType = CommandType.StoredProcedure;
	57	cmd.CommandTimeout = _ConnectTimeOut;
	58	if (parametersInstance != null)
	59	{
	60	foreach (KeyValuePair<string, object> item in parametersInstance)
	61	{
	62	SqlParameter parm = new SqlParameter(item.Key, item.Value);
	63	cmd.Parameters.Add(parm);
	64	}
	65	}
	66	if (con.State == ConnectionState.Closed)
	67	con.Open();
	68
	69	object r = cmd.ExecuteScalar();
	70
	71	return r;
	72	}
	73	catch (Exception er)
	74	{
	75	DisposeConnection(con);
	76	throw er;
	77	}
	78	finally {
	79	DisposeConnection(con);
	80	}
	81	}
	82	}
	83
	84	public override string GetStoreProcName(int storeprocId)
	85	{
	86	DataTable dt = ExecuteSqlForDataTable("SELECT name FROM sysobjects WHERE type='p' and id=" + storeprocId.ToString());
	87	string x = "";
	88	if (dt.Rows.Count > 0)
	89	x = dt.Rows[0][0].ToString();
	90	return x;
	91	}
	92
	93	public override DataSet ExecuteStoreProcedureDataSetWithTableType(string tableTypeKey, DataTable table, Dictionary<string, object> parametersInstance, string storedProcedureName)
	94	{
	95	DataSet ds = new DataSet(Guid.NewGuid().ToString());
	96	using (SqlConnection con = new SqlConnection(_DBConnectionString))
	97	{
	98	try
	99	{
	100	//设置Sql
	101	SqlCommand cmd = new SqlCommand(storedProcedureName, con);
	102	cmd.CommandType = CommandType.StoredProcedure;
	103	cmd.CommandTimeout = _ConnectTimeOut;
	104	if (parametersInstance != null)
	105	{
	106	foreach (KeyValuePair<string,object> item in parametersInstance)
	107	{
	108	SqlParameter parm = new SqlParameter(item.Key, item.Value);
	109	cmd.Parameters.Add(parm);
	110	}
	111	}
	112	if (tableTypeKey != null && tableTypeKey.Trim() != "" && table != null)
	113	{
	114	SqlParameter parm = new SqlParameter(tableTypeKey,SqlDbType.Structured);
	115	parm.Value = table;
	116	cmd.Parameters.Add(parm);
	117	}
	118	SqlDataAdapter sda = new SqlDataAdapter(cmd);
	119	sda.Fill(ds);
	120	return ds;
	121	}
	122	catch (Exception er)
	123	{
	124	DisposeConnection(con);
	125	throw er;
	126	}
	127	finally
	128	{
	129	DisposeConnection(con);
	130	}
	131	}
	132	}
	133
	134	public override object ExecuteStoreProcedureScalarWithTableType(string tableTypeKey, DataTable table, Dictionary<string, object> parametersInstance, string storedProcedureName)
	135	{
	136	using (SqlConnection con = new SqlConnection(_DBConnectionString))
	137	{
	138	try
	139	{
	140	//设置Sql
	141	SqlCommand cmd = new SqlCommand(storedProcedureName, con);
	142	cmd.CommandType = CommandType.StoredProcedure;
	143	cmd.CommandTimeout = _ConnectTimeOut;
	144	if (parametersInstance != null)
	145	{
	146	foreach (KeyValuePair<string,object> item in parametersInstance)
	147	{
	148	SqlParameter parm = new SqlParameter(item.Key, item.Value);
	149	cmd.Parameters.Add(parm);
	150	}
	151	}
	152	if (tableTypeKey != null && tableTypeKey.Trim() != "" && table != null)
	153	{
	154	SqlParameter parm = new SqlParameter(tableTypeKey,SqlDbType.Structured);
	155	parm.Value = table;
	156	cmd.Parameters.Add(parm);
	157	}
	158	if (con.State == ConnectionState.Closed)
	159	con.Open();
	160	return cmd.ExecuteScalar();
	161	}
	162	catch (Exception er)
	163	{
	164	DisposeConnection(con);
	165	throw er;
	166	}
	167	finally
	168	{
	169	DisposeConnection(con);
	170	}
	171	}
	172	}
	173
	174	public override System.Data.DataSet ExecStoredProcedure(Dictionary<string, object> parametersInstance, string storedProcedureName)
	175	{
	176	using (SqlConnection con = new SqlConnection(_DBConnectionString))
	177	{
	178	try
	179	{
	180	//设置Sql
	181	SqlCommand cmd = new SqlCommand(storedProcedureName, con);
	182	cmd.CommandType = CommandType.StoredProcedure;
	183
	184	if (parametersInstance != null)
	185	{
	186	foreach (KeyValuePair<string,object> item in parametersInstance)
	187	{
	188	SqlParameter parm = new SqlParameter(item.Key, item.Value);
	189	cmd.Parameters.Add(parm);
	190	}
	191	}
	192	DataSet ds = new DataSet();
	193	cmd.CommandTimeout = _ConnectTimeOut;
	194	SqlDataAdapter sdap = new SqlDataAdapter(cmd);
	195
	196	sdap.Fill(ds);
	197	return ds;
	198	}
	199	catch (Exception er)
	200	{
	201	DisposeConnection(con);
	202	throw er;
	203	//return null;
	204	}
	205	finally
	206	{
	207	DisposeConnection(con);
	208	}
	209	}
	210	}
	211	static object locker = new object();
	212	public override bool ExecSqlBulkCopyToDBByTransaction(DataTable copying2dbData, string destinationTableName, Dictionary<string, string> sourceToDestinationFieldsMapping)
	213	{
	214	lock (locker) {
	215	//构建多个数据库连接
	216	SqlConnection connection = null;
	217	SqlBulkCopy bcp = null;
	218	SqlTransaction transaction = null;
	219	bool r = false;
	220	try
	221	{
	222	connection = new SqlConnection(_DBConnectionString);
	223	try
	224	{
	225	connection.Open();
	226	}
	227	catch
	228	{
	229	throw new Exception("连接数据库失败!请检查连接字符串" + _DBConnectionString);
	230	}
	231	transaction = connection.BeginTransaction();
	232	bcp = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);
b347b1	233	bcp.BatchSize = 10000;
J	234	bcp.BulkCopyTimeout = 60;
91d475	235	//依次使用数据库连接导入数据
J	236	foreach (KeyValuePair<string, string> kv in sourceToDestinationFieldsMapping)
	237	{
	238	//列映射
	239	bcp.ColumnMappings.Add(kv.Key, kv.Value);
	240	}
	241	bcp.DestinationTableName = destinationTableName;
	242	bcp.WriteToServer(copying2dbData);
	243	transaction.Commit();
	244	r= true;
	245	}
	246	catch (Exception er)
	247	{
	248	string error = er.Message;
	249	try { } catch (Exception ex) { error += "--" + ex.Message; }
	250	if (transaction != null)
	251	transaction.Rollback();
	252	DisposeConnection(connection);
	253	throw new Exception(error) ;
	254	}
	255	finally
	256	{
	257	DisposeConnection(connection);
	258
	259	}
	260	return r;
	261	}
	262	}
	263
	264	public override System.Data.DataTable ExecStoreProcedureForGettingTable(Dictionary<string, object> parametersInstance, string storedProcedureName)
	265	{
	266	using (SqlConnection con = new SqlConnection(_DBConnectionString))
	267	{
	268	try
	269	{
	270	//设置Sql
	271	SqlCommand cmd = new SqlCommand(storedProcedureName, con);
	272	cmd.CommandType = CommandType.StoredProcedure;
	273	cmd.CommandTimeout = _ConnectTimeOut;
	274	if (parametersInstance != null)
	275	{
	276	foreach (KeyValuePair<string, object> item in parametersInstance)
	277	{
	278	SqlParameter parm = new SqlParameter(item.Key, item.Value);
	279	cmd.Parameters.Add(parm);
	280	}
	281	}
	282	DataTable dt = new DataTable(Guid.NewGuid().ToString());
	283	SqlDataAdapter sdap = new SqlDataAdapter(cmd);
	284	sdap.Fill(dt);
	285	return dt;
	286	}
	287	catch (Exception er)
	288	{
	289	DisposeConnection(con);
	290	throw er;
	291	}
	292	finally
	293	{
	294	DisposeConnection(con);
	295	}
	296	}
	297	}
	298
	299	public override DataTable ExecuteSqlForDataTable(string sql)
	300	{
	301	using (SqlConnection con = new SqlConnection(_DBConnectionString))
	302	{
	303	try
	304	{
	305	//设置Sql
	306	SqlCommand cmd = new SqlCommand(sql, con);
	307	cmd.CommandType = CommandType.Text;
	308	cmd.CommandTimeout = _ConnectTimeOut;
	309	DataTable dt = new DataTable(Guid.NewGuid().ToString());
	310	SqlDataAdapter sdap = new SqlDataAdapter(cmd);
	311	sdap.Fill(dt);
	312	return dt;
	313	}
	314	catch (Exception er)
	315	{
	316	DisposeConnection(con);
	317	throw er;
	318	}
	319	finally
	320	{
	321	DisposeConnection(con);
	322	}
	323	}
	324	}
	325	public override DataSet ExecuteSqlForDataSet(string sql)
	326	{
	327	using (SqlConnection con = new SqlConnection(_DBConnectionString))
	328	{
	329	try
	330	{
	331	//设置Sql
	332	SqlCommand cmd = new SqlCommand(sql, con);
	333	cmd.CommandType = CommandType.Text;
	334	cmd.CommandTimeout = _ConnectTimeOut;
	335	DataSet ds = new DataSet(Guid.NewGuid().ToString());
	336	SqlDataAdapter sdap = new SqlDataAdapter(cmd);
	337	sdap.Fill(ds);
	338	return ds;
	339	}
	340	catch (Exception er)
	341	{
	342	DisposeConnection(con);
	343	throw er;
	344	}
	345	finally
	346	{
	347	DisposeConnection(con);
	348	}
	349	}
	350	}
	351	public override DataTable ExecStoreProcedureWithTableType(string tableTypeKey,DataTable table, Dictionary<string, object> parametersInstance, string storedProcedureName)
	352	{
	353	using (SqlConnection con = new SqlConnection(_DBConnectionString))
	354	{
	355	try
	356	{
	357	//设置Sql
	358	SqlCommand cmd = new SqlCommand(storedProcedureName, con);
	359	cmd.CommandType = CommandType.StoredProcedure;
	360	cmd.CommandTimeout = _ConnectTimeOut;
	361	if (parametersInstance != null)
	362	{
	363	foreach (KeyValuePair<string,object> item in parametersInstance)
	364	{
	365	SqlParameter parm = new SqlParameter(item.Key, item.Value);
	366	cmd.Parameters.Add(parm);
	367	}
	368	}
	369	if (table != null)
	370	{
	371	SqlParameter parm = new SqlParameter(tableTypeKey, table);
	372	cmd.Parameters.Add(parm);
	373	}
	374	DataTable dt = new DataTable(Guid.NewGuid().ToString());
	375	SqlDataAdapter sdap = new SqlDataAdapter(cmd);
	376	sdap.Fill(dt);
	377	return dt;
	378	}
	379	catch (Exception er)
	380	{
	381	DisposeConnection(con);
	382	throw er;
	383	}
	384	finally
	385	{
	386	DisposeConnection(con);
	387	}
	388	}
	389	}
	390
	391	}
	392	}