You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1540 lines
66 KiB
C#
1540 lines
66 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Data.OleDb;
|
|
using System.Data.SqlClient;
|
|
using System.Diagnostics;
|
|
using System.IO;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using NetLibrary.Common.Configuration;
|
|
|
|
namespace NetLibrary.Data
|
|
{
|
|
public class Database
|
|
{
|
|
private string m_ConnectionString;
|
|
|
|
public string ConnectionString
|
|
{
|
|
get { return m_ConnectionString; }
|
|
set { m_ConnectionString = value; }
|
|
}
|
|
private DbProviderFactory m_Factory;
|
|
|
|
public DbProviderFactory Factory
|
|
{
|
|
get { return m_Factory; }
|
|
set { m_Factory = value; }
|
|
}
|
|
public Database(string ConnectionString, DbProviderFactory factory)
|
|
{
|
|
this.Factory = factory;
|
|
this.ConnectionString = ConnectionString;
|
|
}
|
|
private string m_DataBaseName = "";
|
|
|
|
private bool m_IsLastingConnect = false;
|
|
/// <summary>
|
|
/// 是否保持连接
|
|
/// </summary>
|
|
public bool IsLastingConnect
|
|
{
|
|
get { return m_IsLastingConnect; }
|
|
set { m_IsLastingConnect = value; }
|
|
}
|
|
|
|
private DbConnection cn = null;
|
|
|
|
public string DataBaseName
|
|
{
|
|
get { return m_DataBaseName; }
|
|
set { m_DataBaseName = value; }
|
|
}
|
|
|
|
#region 释放内存
|
|
public void Dispose(DbCommand command)
|
|
{
|
|
command.Parameters.Clear();
|
|
command.Dispose();
|
|
command = null;
|
|
Factory = null;
|
|
cn = null;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 返回条件参数化连接语句
|
|
public string GetWhere(RefParameterCollection Param, CommandType CommandType)
|
|
{
|
|
string tsql = "";
|
|
if (Param != null && Param.Count != 0)
|
|
{
|
|
if (CommandType == CommandType.Text)
|
|
{
|
|
foreach (RefParameter pam in Param)
|
|
{
|
|
if (pam.Direction != ParameterDirection.Input) continue;
|
|
if (pam.IsDeclare == false) continue; //不生成条件
|
|
if (tsql.IndexOf("where") == -1)
|
|
{
|
|
switch (pam.Condition)
|
|
{
|
|
case "in":
|
|
tsql = tsql + " where " + pam.Name + " " + pam.Condition + " (@" + pam.Caption + "0";
|
|
string[] chars1 = pam.Value.ToString().Split(new char[] { ',' });
|
|
for (int i = 1; i < chars1.Length; i++)
|
|
{
|
|
tsql = tsql + " ,@" + pam.Caption + i.ToString();
|
|
}
|
|
tsql = tsql + ")" + pam.EndString;
|
|
break;
|
|
case "not in":
|
|
tsql = tsql + " where " + pam.Name + " " + pam.Condition + " (@" + pam.Caption + "0";
|
|
string[] chars2 = pam.Value.ToString().Split(new char[] { ',' });
|
|
for (int i = 1; i < chars2.Length; i++)
|
|
{
|
|
tsql = tsql + " ,@" + pam.Caption + i.ToString();
|
|
}
|
|
tsql = tsql + ")" + pam.EndString;
|
|
break;
|
|
case "like":
|
|
tsql = tsql + " where " + pam.Name + " like '%'+" + "@" + pam.Caption + "+'%'" + pam.EndString;
|
|
break;
|
|
case "like left":
|
|
tsql = tsql + " where " + pam.Name + " like '%'+" + "@" + pam.Caption + pam.EndString;
|
|
break;
|
|
case "like right":
|
|
tsql = tsql + " where " + pam.Name + " like " + "@" + pam.Caption + "+'%'" + pam.EndString;
|
|
break;
|
|
case "is":
|
|
tsql = tsql + " where " + pam.Name + " is null" + pam.EndString;
|
|
break;
|
|
case "is not":
|
|
tsql = tsql + " where " + pam.Name + " is not null" + pam.EndString;
|
|
break;
|
|
default:
|
|
if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
|
|
{
|
|
tsql = tsql + " where " + pam.Name + "=''" + pam.EndString;
|
|
}
|
|
else if (pam.Value == null || pam.Value == DBNull.Value)
|
|
{
|
|
tsql = tsql + " where " + pam.Name + " is null" + pam.EndString;
|
|
}
|
|
else
|
|
{
|
|
tsql = tsql + " where " + pam.Name + pam.Condition + "@" + pam.Caption + pam.EndString;
|
|
}
|
|
break;
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
switch (pam.Condition)
|
|
{
|
|
case "in":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " " + pam.Condition + " (@" + pam.Caption + "0";
|
|
string[] chars3 = pam.Value.ToString().Split(new char[] { ',' });
|
|
for (int i = 1; i < chars3.Length; i++)
|
|
{
|
|
tsql = tsql + " ,@" + pam.Caption + i.ToString();
|
|
}
|
|
tsql = tsql + ")" + pam.EndString;
|
|
break;
|
|
case "not in":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " " + pam.Condition + " (@" + pam.Caption + "0";
|
|
string[] chars4 = pam.Value.ToString().Split(new char[] { ',' });
|
|
for (int i = 1; i < chars4.Length; i++)
|
|
{
|
|
tsql = tsql + " ,@" + pam.Caption + i.ToString();
|
|
}
|
|
tsql = tsql + ")" + pam.EndString;
|
|
break;
|
|
case "like":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%'+" + "@" + pam.Caption + "+'%'" + pam.EndString;
|
|
break;
|
|
case "like left":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%'+" + "@" + pam.Caption + pam.EndString;
|
|
break;
|
|
case "like right":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like " + "@" + pam.Caption + "+'%'" + pam.EndString;
|
|
break;
|
|
case "is":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
|
|
break;
|
|
case "is not":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is not null" + pam.EndString;
|
|
break;
|
|
default:
|
|
if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + "=''" + pam.EndString;
|
|
}
|
|
else if (pam.Value == null || pam.Value == DBNull.Value)
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
|
|
}
|
|
else
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + "@" + pam.Caption + pam.EndString;
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
|
|
foreach (RefParameter pam in Param)
|
|
{
|
|
if (pam.Direction != ParameterDirection.Input) continue;
|
|
if (pam.IsDeclare == false) continue; //不生成条件
|
|
if (tsql.IndexOf("where") == -1)
|
|
{
|
|
switch (pam.Condition)
|
|
{
|
|
case "in":
|
|
if (pam.Type != DbType.String)
|
|
{
|
|
tsql = tsql + " where " + pam.Name + " in (" + pam.Value.ToString() + ")" + pam.EndString;
|
|
}
|
|
else
|
|
{
|
|
string[] chars1 = pam.Value.ToString().Split(new char[] { ',' });
|
|
string ss = "";
|
|
foreach (string s in chars1)
|
|
{
|
|
if (ss == "") { ss = "'" + s + "'"; }
|
|
else { ss = ss + ",'" + s + "'"; }
|
|
}
|
|
|
|
tsql = tsql + " where " + pam.Name + " in (" + ss + ")" + pam.EndString;
|
|
}
|
|
break;
|
|
case "not in":
|
|
if (pam.Type != DbType.String)
|
|
{
|
|
tsql = tsql + " where " + pam.Name + " not in (" + pam.Value.ToString() + ")" + pam.EndString;
|
|
}
|
|
else
|
|
{
|
|
string[] chars2 = pam.Value.ToString().Split(new char[] { ',' });
|
|
string ss = "";
|
|
foreach (string s in chars2)
|
|
{
|
|
if (ss == "") { ss = "'" + s + "'"; }
|
|
else { ss = ss + ",'" + s + "'"; }
|
|
}
|
|
|
|
tsql = tsql + " where " + pam.Name + " not in (" + ss + ")" + pam.EndString;
|
|
}
|
|
break;
|
|
case "like":
|
|
tsql = tsql + " where " + pam.Name + " like '%" + pam.Value.ToString() + "%'" + pam.EndString;
|
|
break;
|
|
case "like left":
|
|
tsql = tsql + " where " + pam.Name + " like '%" + pam.Value.ToString() + "'" + pam.EndString;
|
|
break;
|
|
case "like right":
|
|
tsql = tsql + " where " + pam.Name + " like '" + pam.Value.ToString() + "%'" + pam.EndString;
|
|
break;
|
|
case "is":
|
|
tsql = tsql + " where " + pam.Name + " is null" + pam.EndString;
|
|
break;
|
|
case "is not":
|
|
tsql = tsql + " where " + pam.Name + " is not null" + pam.EndString;
|
|
break;
|
|
default:
|
|
|
|
if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
|
|
{
|
|
tsql = " where " + pam.Name + "=''" + pam.EndString;
|
|
}
|
|
else if (pam.Value == null || pam.Value == DBNull.Value)
|
|
{
|
|
tsql = " where " + pam.Name + " is null" + pam.EndString;
|
|
}
|
|
else if (pam.Type == DbType.String || pam.Type == DbType.DateTime)
|
|
{
|
|
tsql = " where " + pam.Name + pam.Condition + "'" + pam.Value.ToString() + "'" + pam.EndString;
|
|
}
|
|
else
|
|
{
|
|
tsql = " where " + pam.Name + pam.Condition + pam.Value.ToString() + pam.EndString;
|
|
}
|
|
break;
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
switch (pam.Condition)
|
|
{
|
|
case "in":
|
|
if (pam.Type != DbType.String)
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " in (" + pam.Value.ToString() + ")" + pam.EndString;
|
|
}
|
|
else
|
|
{
|
|
string[] chars3 = pam.Value.ToString().Split(new char[] { ',' });
|
|
string ss = "";
|
|
foreach (string s in chars3)
|
|
{
|
|
if (ss == "") { ss = "'" + s + "'"; }
|
|
else { ss = ss + ",'" + s + "'"; }
|
|
}
|
|
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " in (" + ss + ")" + pam.EndString;
|
|
}
|
|
break;
|
|
case "not in":
|
|
if (pam.Type != DbType.String)
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " not in (" + pam.Value.ToString() + ")" + pam.EndString;
|
|
}
|
|
else
|
|
{
|
|
string[] chars4 = pam.Value.ToString().Split(new char[] { ',' });
|
|
string ss = "";
|
|
foreach (string s in chars4)
|
|
{
|
|
if (ss == "") { ss = "'" + s + "'"; }
|
|
else { ss = ss + ",'" + s + "'"; }
|
|
}
|
|
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " not in (" + ss + ")" + pam.EndString;
|
|
}
|
|
break;
|
|
case "like":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%" + pam.Value.ToString() + "%'" + pam.EndString;
|
|
break;
|
|
case "like left":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%" + pam.Value.ToString() + "'" + pam.EndString;
|
|
break;
|
|
case "like right":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '" + pam.Value.ToString() + "%'" + pam.EndString;
|
|
break;
|
|
case "is":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
|
|
break;
|
|
case "is not":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is not null" + pam.EndString;
|
|
break;
|
|
default:
|
|
if (pam.Type == DbType.String && Convert.ToString(pam.Value) == "")
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + "=''" + pam.EndString;
|
|
}
|
|
else if (pam.Value == null || pam.Value == DBNull.Value)
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null" + pam.EndString;
|
|
}
|
|
else if (pam.Type == DbType.String || pam.Type == DbType.DateTime)
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + "'" + pam.Value.ToString() + "'" + pam.EndString;
|
|
}
|
|
else
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + pam.Value.ToString() + pam.EndString;
|
|
}
|
|
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return tsql;
|
|
}
|
|
#endregion
|
|
|
|
#region 创建Connection对象
|
|
public DbConnection CreateConnection()
|
|
{
|
|
if (cn != null) return cn;
|
|
cn = Factory.CreateConnection();
|
|
cn.ConnectionString = this.ConnectionString;
|
|
if (this.DataBaseName != "") System.Text.RegularExpressions.Regex.Replace(cn.ConnectionString, @"", DataBaseName);
|
|
return cn;
|
|
}
|
|
#endregion
|
|
#region 读取数据库名称
|
|
public string GetDataBaseName()
|
|
{
|
|
if (cn != null) return cn.Database;
|
|
cn = Factory.CreateConnection();
|
|
cn.ConnectionString = this.ConnectionString;
|
|
return cn.Database;
|
|
}
|
|
#endregion
|
|
|
|
#region 测试连接
|
|
public bool TestConnection()
|
|
{
|
|
DbConnection cn = Factory.CreateConnection();
|
|
cn.ConnectionString = this.ConnectionString;
|
|
try
|
|
{
|
|
cn.Open();
|
|
}
|
|
catch
|
|
{
|
|
return false;
|
|
}
|
|
cn.Close();
|
|
return true;
|
|
}
|
|
#endregion
|
|
|
|
#region 返回向DataTable查询条件
|
|
public static string GetDataTableWhere(RefParameterCollection Param)
|
|
{
|
|
string tsql = "";
|
|
if (Param != null && Param.Count != 0)
|
|
{
|
|
foreach (RefParameter pam in Param)
|
|
{
|
|
if (Param.IndexOf(pam) == 0)
|
|
{
|
|
switch (pam.Condition)
|
|
{
|
|
case "in":
|
|
if (pam.Type != DbType.String)
|
|
{
|
|
tsql = pam.Name + " in (" + pam.Value.ToString() + ")";
|
|
}
|
|
else
|
|
{
|
|
string[] chars1 = pam.Value.ToString().Split(new char[] { ',' });
|
|
string ss = "";
|
|
foreach (string s in chars1)
|
|
{
|
|
if (ss == "") { ss = "'" + s + "'"; }
|
|
else { ss = ss + ",'" + s + "'"; }
|
|
}
|
|
tsql = pam.Name + " in (" + ss + ")";
|
|
}
|
|
break;
|
|
case "not in":
|
|
if (pam.Type != DbType.String)
|
|
{
|
|
tsql = pam.Name + " not in (" + pam.Value.ToString() + ")";
|
|
}
|
|
else
|
|
{
|
|
string[] chars2 = pam.Value.ToString().Split(new char[] { ',' });
|
|
string ss = "";
|
|
foreach (string s in chars2)
|
|
{
|
|
if (ss == "") { ss = "'" + s + "'"; }
|
|
else { ss = ss + ",'" + s + "'"; }
|
|
}
|
|
|
|
tsql = pam.Name + " not in (" + ss + ")";
|
|
}
|
|
break;
|
|
case "like":
|
|
tsql = pam.Name + " like '%" + pam.Value.ToString() + "%'";
|
|
break;
|
|
case "like left":
|
|
tsql = pam.Name + " like '%" + pam.Value.ToString() + "'";
|
|
break;
|
|
case "like right":
|
|
tsql = pam.Name + " like '" + pam.Value.ToString() + "%'";
|
|
break;
|
|
case "is":
|
|
tsql = pam.Name + " is null";
|
|
break;
|
|
case "is not":
|
|
tsql = pam.Name + " is not null";
|
|
break;
|
|
default:
|
|
|
|
if (pam.Type == DbType.String || pam.Type == DbType.DateTime)
|
|
{
|
|
tsql = pam.Name + pam.Condition + "'" + pam.Value.ToString() + "'";
|
|
}
|
|
else
|
|
{
|
|
tsql = pam.Name + pam.Condition + pam.Value.ToString();
|
|
}
|
|
break;
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
switch (pam.Condition)
|
|
{
|
|
case "in":
|
|
if (pam.Type != DbType.String)
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " in (" + pam.Value.ToString() + ")";
|
|
}
|
|
else
|
|
{
|
|
string[] chars3 = pam.Value.ToString().Split(new char[] { ',' });
|
|
string ss = "";
|
|
foreach (string s in chars3)
|
|
{
|
|
if (ss == "") { ss = "'" + s + "'"; }
|
|
else { ss = ss + ",'" + s + "'"; }
|
|
}
|
|
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " in (" + ss + ")";
|
|
}
|
|
break;
|
|
case "not in":
|
|
if (pam.Type != DbType.String)
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " not in (" + pam.Value.ToString() + ")";
|
|
}
|
|
else
|
|
{
|
|
string[] chars4 = pam.Value.ToString().Split(new char[] { ',' });
|
|
string ss = "";
|
|
foreach (string s in chars4)
|
|
{
|
|
if (ss == "") { ss = "'" + s + "'"; }
|
|
else { ss = ss + ",'" + s + "'"; }
|
|
}
|
|
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " not in (" + ss + ")";
|
|
}
|
|
break;
|
|
case "like":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%" + pam.Value.ToString() + "%'";
|
|
break;
|
|
case "like left":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '%" + pam.Value.ToString() + "'";
|
|
break;
|
|
case "like right":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " like '" + pam.Value.ToString() + "%'";
|
|
break;
|
|
case "is":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is null";
|
|
break;
|
|
case "is not":
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + " is not null";
|
|
break;
|
|
default:
|
|
if (pam.Type == DbType.String || pam.Type == DbType.DateTime)
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + "'" + pam.Value.ToString() + "'";
|
|
}
|
|
else
|
|
{
|
|
tsql = tsql + " " + pam.RelateTo + " " + pam.Name + pam.Condition + pam.Value.ToString();
|
|
}
|
|
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return tsql;
|
|
}
|
|
#endregion
|
|
|
|
#region Command对象
|
|
/// <summary>
|
|
/// 执行Sql语句的Command
|
|
/// </summary>
|
|
public DbCommand GetSqlStringCommand(string query)
|
|
{
|
|
return CreateCommandByCommandType(CommandType.Text, query);
|
|
}
|
|
|
|
public virtual DbCommand GetStoredProcCommand(string storedProcedureName)
|
|
{
|
|
|
|
return CreateCommandByCommandType(CommandType.StoredProcedure, storedProcedureName);
|
|
}
|
|
|
|
private DbCommand CreateCommandByCommandType(CommandType commandType, string commandText)
|
|
{
|
|
DbCommand command = Factory.CreateCommand();
|
|
command.CommandType = commandType;
|
|
command.CommandText = commandText;
|
|
command.CommandTimeout = 300;
|
|
return command;
|
|
}
|
|
#endregion
|
|
|
|
#region 参数
|
|
public void AddInParameter(DbCommand cmd, RefParameterCollection Param)
|
|
{
|
|
if (Param != null && Param.Count != 0)
|
|
{
|
|
int i = 0;
|
|
foreach (RefParameter pam in Param)
|
|
{
|
|
if (pam.Direction == ParameterDirection.Input)
|
|
{
|
|
if (pam.IsDeclare == true)
|
|
{
|
|
if (pam.Condition == "in" || pam.Condition == "not in")
|
|
{
|
|
string[] chars = pam.Value.ToString().Split(new char[] { ',' });
|
|
i = 0;
|
|
foreach (string s in chars)
|
|
{
|
|
this.AddInParameter(cmd, "@" + pam.Caption + i.ToString(), pam.Type, s);
|
|
i++;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (pam.Value != null)
|
|
{
|
|
this.AddInParameter(cmd, "@" + pam.Caption, pam.Type, pam.Value);
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
|
|
this.AddInParameter(cmd, "@" + pam.Name, pam.Type, pam.Value);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
this.AddOutParameter(cmd, "@" + pam.Name, pam.Type, 50);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
public void AddInParameter(DbCommand command, string name, DbType dbType, string sourceColumn, bool nullable, DataRowVersion version)
|
|
{
|
|
DbParameter parameter = CreateParameter(name, dbType, nullable, sourceColumn, version);
|
|
command.Parameters.Add(parameter); ;
|
|
}
|
|
|
|
public void AddInParameter(DbCommand command, string name, DbType dbType, object value)
|
|
{
|
|
AddParameter(command, name, dbType, ParameterDirection.Input, true, value, null);
|
|
}
|
|
|
|
public void AddOutParameter(DbCommand command, string name, DbType dbType, int Size)
|
|
{
|
|
AddParameter(command, name, dbType, ParameterDirection.Output, true, DBNull.Value, Size);
|
|
}
|
|
|
|
public virtual void AddParameter(DbCommand command, string name, DbType dbType, ParameterDirection direction, bool nullable, object value, int? Size)
|
|
{
|
|
DbParameter parameter = CreateParameter(name, dbType, direction, nullable, value, Size);
|
|
command.Parameters.Add(parameter);
|
|
}
|
|
|
|
|
|
protected virtual DbParameter CreateParameter(string name, DbType dbType, ParameterDirection direction, bool nullable, object value, int? Size)
|
|
{
|
|
DbParameter param = Factory.CreateParameter();
|
|
param.ParameterName = name;
|
|
param.DbType = dbType;
|
|
param.IsNullable = nullable;
|
|
param.Value = (value == null) ? DBNull.Value : value;
|
|
param.Direction = direction;
|
|
if (Size != null) param.Size = Size.Value;
|
|
return param;
|
|
}
|
|
|
|
protected virtual DbParameter CreateParameter(string name, DbType dbType, bool nullable, string sourceColumn, DataRowVersion version)
|
|
{
|
|
DbParameter param = Factory.CreateParameter();
|
|
param.ParameterName = name;
|
|
param.Value = DBNull.Value;
|
|
param.IsNullable = nullable;
|
|
param.DbType = dbType;
|
|
param.Direction = ParameterDirection.Input;
|
|
param.SourceColumnNullMapping = nullable;
|
|
param.SourceColumn = sourceColumn;
|
|
param.SourceVersion = version;
|
|
return param;
|
|
}
|
|
#endregion
|
|
|
|
#region 打开连接
|
|
private void ConnectOpen(DbConnection cn)
|
|
{
|
|
if (this.IsLastingConnect == false || cn.State == ConnectionState.Closed) cn.Open();
|
|
}
|
|
#endregion
|
|
|
|
#region 关闭连接
|
|
private void ConnectClose(DbConnection cn)
|
|
{
|
|
if (this.IsLastingConnect == false) cn.Close();
|
|
}
|
|
public void CloseConnect()
|
|
{
|
|
cn.Close();
|
|
}
|
|
#endregion
|
|
|
|
#region 杀死锁进程
|
|
public bool KillProcess()
|
|
{
|
|
//bConnection cn = CreateConnection();
|
|
// try
|
|
// {
|
|
// DbCommand command = this.GetStoredProcCommand("p_lockinfo");
|
|
// AddInParameter(command, "@kill_lock_spid", DbType.Boolean, true); //自动杀掉死锁进程
|
|
// AddInParameter(command, "@show_spid_if_nolock", DbType.Int32, false); //不显示进程信息
|
|
// command.Connection = cn;
|
|
// ConnectOpen(cn);
|
|
// cn.ChangeDatabase("master");
|
|
// int a = command.ExecuteNonQuery();
|
|
// ConnectClose(cn);
|
|
// NetLibrary.Follow.ErrorFollow.TraceWrite("杀进程死锁成功:", "", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
|
|
// NetLibrary.Follow.ErrorFollow.TraceWrite("杀进程死锁出错:", "", ex.Message);
|
|
// return false;
|
|
// }
|
|
NetLibrary.Log.ErrorFollow.TraceWrite("进程死锁:", "", "");
|
|
return true;
|
|
}
|
|
#endregion
|
|
|
|
#region 执行Command
|
|
public virtual DataTable ExecuteDataTable(DbCommand command)
|
|
{
|
|
DataTable tb = new DataTable();
|
|
try
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
command.Connection = cn;
|
|
using (DbDataAdapter adapter = Factory.CreateDataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
adapter.Fill(tb);
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return tb;
|
|
}
|
|
|
|
public virtual DataTable ExecuteDataTable(DbCommand command, int StartRowIndex, int RowCount)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
command.Connection = cn;
|
|
using (DbDataAdapter adapter = Factory.CreateDataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
adapter.Fill(ds, StartRowIndex, RowCount, "Table1");
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return ds.Tables[0];
|
|
}
|
|
|
|
|
|
public virtual DataSet ExecuteDataSet(DbCommand command)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
|
|
try
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
command.Connection = cn;
|
|
using (DbDataAdapter adapter = Factory.CreateDataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
adapter.Fill(ds);
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return ds;
|
|
}
|
|
|
|
public virtual DataSet ExecuteDataSet(DbCommand command, int StartRowIndex, int RowCount)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
|
|
try
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
command.Connection = cn;
|
|
using (DbDataAdapter adapter = Factory.CreateDataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
adapter.Fill(ds, StartRowIndex, RowCount, "Table1");
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return ds;
|
|
}
|
|
|
|
public virtual DataTable ExecuteDataTable(DbCommand command, DbConnection cn, DbTransaction trans)
|
|
{
|
|
DataTable tb = new DataTable();
|
|
try
|
|
{
|
|
command.Connection = cn;
|
|
command.Transaction = trans;
|
|
using (DbDataAdapter adapter = Factory.CreateDataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(tb);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return tb;
|
|
}
|
|
|
|
public virtual int ExecuteNonQuery(DbCommand command)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
int a = 0;
|
|
try
|
|
{
|
|
command.Connection = cn;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
a = command.ExecuteNonQuery();
|
|
ConnectClose(cn);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return a;
|
|
}
|
|
|
|
public virtual void ExecuteNonQuery(List<DbCommand> ListCommand)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
DbTransaction trans = cn.BeginTransaction();
|
|
foreach (DbCommand cmd in ListCommand)
|
|
{
|
|
cmd.Connection = cn;
|
|
cmd.Transaction = trans;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
try
|
|
{
|
|
trans.Commit();
|
|
ConnectClose(cn);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
trans.Rollback();
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public virtual int ExecuteNonQuery(DbCommand command, DbConnection cn, DbTransaction trans)
|
|
{
|
|
int a = 0;
|
|
try
|
|
{
|
|
command.Connection = cn;
|
|
command.Transaction = trans;
|
|
a = command.ExecuteNonQuery();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return a;
|
|
}
|
|
|
|
public virtual object ExecuteScalar(DbCommand command)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
object a = null;
|
|
try
|
|
{
|
|
command.Connection = cn;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
a = command.ExecuteScalar();
|
|
ConnectClose(cn);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return a;
|
|
}
|
|
|
|
public virtual object ExecuteScalar(DbCommand command, DbConnection cn, DbTransaction trans)
|
|
{
|
|
object a = null;
|
|
try
|
|
{
|
|
command.Connection = cn;
|
|
command.Transaction = trans;
|
|
a = command.ExecuteScalar();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return a;
|
|
}
|
|
|
|
public virtual IDataReader ExecuteReader(DbCommand command)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
IDataReader a = null;
|
|
try
|
|
{
|
|
command.Connection = cn;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
a = command.ExecuteReader(CommandBehavior.CloseConnection);
|
|
//ConnectClose(cn);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return a;
|
|
}
|
|
|
|
public virtual IDataReader ExecuteReader(DbCommand command, DbConnection cn, DbTransaction trans)
|
|
{
|
|
|
|
IDataReader a = null;
|
|
try
|
|
{
|
|
command.Connection = cn;
|
|
command.Transaction = trans;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return a;
|
|
}
|
|
|
|
//public void UpdateDataTable(DataTable tb, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
|
|
//{
|
|
// DbConnection cn = CreateConnection();
|
|
// try
|
|
// {
|
|
// ConnectOpen(cn);
|
|
// if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
// //DbTransaction tran = cn.BeginTransaction();
|
|
// DbDataAdapter da = Factory.CreateDataAdapter();
|
|
// if (insertCommand != null)
|
|
// {
|
|
// //insertCommand.Transaction=tran;
|
|
// insertCommand.Connection = cn;
|
|
// da.InsertCommand = insertCommand;
|
|
// //da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
// }
|
|
// if (updateCommand != null)
|
|
// {
|
|
// //updateCommand.Transaction = tran;
|
|
// updateCommand.Connection = cn;
|
|
// da.UpdateCommand = updateCommand;
|
|
// //da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
// }
|
|
// if (deleteCommand != null)
|
|
// {
|
|
// //deleteCommand.Transaction = tran;
|
|
// deleteCommand.Connection = cn;
|
|
// da.DeleteCommand = deleteCommand;
|
|
// //da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
// }
|
|
// da.Update(tb);
|
|
// //tran.Commit();
|
|
// ConnectClose(cn);
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// ConnectClose(cn);
|
|
// if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
// throw new Exception(ex.Message);
|
|
// }
|
|
|
|
|
|
//}
|
|
|
|
public void UpdateDataTable(DataTable tb, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, int UpdateBatchSize)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
try
|
|
{
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
//DbTransaction tran = cn.BeginTransaction();
|
|
DbDataAdapter da = Factory.CreateDataAdapter();
|
|
if (UpdateBatchSize != 1) da.UpdateBatchSize = UpdateBatchSize;
|
|
if (insertCommand != null)
|
|
{
|
|
//insertCommand.Transaction=tran;
|
|
insertCommand.Connection = cn;
|
|
da.InsertCommand = insertCommand;
|
|
if (UpdateBatchSize != 1) da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
if (updateCommand != null)
|
|
{
|
|
//updateCommand.Transaction = tran;
|
|
updateCommand.Connection = cn;
|
|
da.UpdateCommand = updateCommand;
|
|
if (UpdateBatchSize != 1) da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
if (deleteCommand != null)
|
|
{
|
|
//deleteCommand.Transaction = tran;
|
|
deleteCommand.Connection = cn;
|
|
da.DeleteCommand = deleteCommand;
|
|
if (UpdateBatchSize != 1) da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
da.Update(tb);
|
|
|
|
//tran.Commit();
|
|
ConnectClose(cn);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public void UpdateDataSet(DataSet ds, string TableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
try
|
|
{
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
//DbTransaction tran = cn.BeginTransaction();
|
|
DbDataAdapter da = Factory.CreateDataAdapter();
|
|
if (insertCommand != null)
|
|
{
|
|
//insertCommand.Transaction = tran;
|
|
insertCommand.Connection = cn;
|
|
da.InsertCommand = insertCommand;
|
|
//da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
if (updateCommand != null)
|
|
{
|
|
//updateCommand.Transaction = tran;
|
|
updateCommand.Connection = cn;
|
|
da.UpdateCommand = updateCommand;
|
|
//da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
if (deleteCommand != null)
|
|
{
|
|
//deleteCommand.Transaction = tran;
|
|
deleteCommand.Connection = cn;
|
|
da.DeleteCommand = deleteCommand;
|
|
//da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
da.Update(ds, TableName);
|
|
|
|
//tran.Commit();
|
|
ConnectClose(cn);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public void UpdateDataSet(DataSet ds, string TableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, int UpdateBatchSize)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
try
|
|
{
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
//DbTransaction tran = cn.BeginTransaction();
|
|
DbDataAdapter da = Factory.CreateDataAdapter();
|
|
if (UpdateBatchSize != 1) da.UpdateBatchSize = UpdateBatchSize;
|
|
if (insertCommand != null)
|
|
{
|
|
//insertCommand.Transaction = tran;
|
|
insertCommand.Connection = cn;
|
|
da.InsertCommand = insertCommand;
|
|
if (UpdateBatchSize != 1) da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
if (updateCommand != null)
|
|
{
|
|
//updateCommand.Transaction = tran;
|
|
updateCommand.Connection = cn;
|
|
da.UpdateCommand = updateCommand;
|
|
if (UpdateBatchSize != 1) da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
if (deleteCommand != null)
|
|
{
|
|
//deleteCommand.Transaction = tran;
|
|
deleteCommand.Connection = cn;
|
|
da.DeleteCommand = deleteCommand;
|
|
if (UpdateBatchSize != 1) da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
da.Update(ds, TableName);
|
|
ConnectClose(cn);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public void UpdateBatchDataRow(DataRow[] drow, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
try
|
|
{
|
|
ConnectOpen(cn);
|
|
DbDataAdapter da = Factory.CreateDataAdapter();
|
|
da.UpdateBatchSize = 0;
|
|
if (insertCommand != null)
|
|
{
|
|
//insertCommand.Transaction = transaction;
|
|
insertCommand.Connection = cn;
|
|
da.InsertCommand = insertCommand;
|
|
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
if (updateCommand != null)
|
|
{
|
|
//updateCommand.Transaction = transaction;
|
|
updateCommand.Connection = cn;
|
|
da.UpdateCommand = updateCommand;
|
|
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
if (deleteCommand != null)
|
|
{
|
|
//deleteCommand.Transaction = transaction;
|
|
deleteCommand.Connection = cn;
|
|
da.DeleteCommand = deleteCommand;
|
|
da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
|
|
}
|
|
da.Update(drow);
|
|
ConnectClose(cn);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public void UpdateTranDataTable(DataTable tb, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
|
|
{
|
|
DataTable table1 = tb.GetChanges(DataRowState.Deleted);
|
|
if (table1 != null) table1.RejectChanges();
|
|
DataTable table2 = tb.GetChanges(DataRowState.Added);
|
|
DataTable table3 = tb.GetChanges(DataRowState.Modified);
|
|
int len = 0;
|
|
if (table1 != null) len += table1.Rows.Count;
|
|
if (table2 != null) len += table2.Rows.Count;
|
|
if (table3 != null) len += table3.Rows.Count;
|
|
if (len == 0) return;
|
|
DbConnection cn = CreateConnection();
|
|
ConnectOpen(cn);
|
|
DbTransaction trans = cn.BeginTransaction();
|
|
try
|
|
{
|
|
DbCommand[] ListCommand = new DbCommand[len];
|
|
int index = 0;
|
|
if (deleteCommand != null && table1 != null)
|
|
{
|
|
foreach (DataRow row in table1.Rows)
|
|
{
|
|
ListCommand[index] = this.GetSqlStringCommand(deleteCommand.CommandText);
|
|
ListCommand[index].CommandType = deleteCommand.CommandType;
|
|
ListCommand[index].CommandTimeout = 300;
|
|
foreach (DbParameter pam in deleteCommand.Parameters)
|
|
{
|
|
object value = pam.Value;
|
|
if (value == DBNull.Value) value = row[pam.SourceColumn];
|
|
this.AddInParameter(ListCommand[index], pam.ParameterName, pam.DbType, value);
|
|
}
|
|
ListCommand[index].Connection = cn;
|
|
ListCommand[index].Transaction = trans;
|
|
ListCommand[index].ExecuteNonQuery();
|
|
index++;
|
|
}
|
|
}
|
|
if (insertCommand != null && table2 != null)
|
|
{
|
|
foreach (DataRow row in table2.Rows)
|
|
{
|
|
ListCommand[index] = this.GetSqlStringCommand(insertCommand.CommandText);
|
|
ListCommand[index].CommandType = insertCommand.CommandType;
|
|
ListCommand[index].CommandTimeout = 300;
|
|
foreach (DbParameter pam in insertCommand.Parameters)
|
|
{
|
|
object value = pam.Value;
|
|
if (value == DBNull.Value) value = row[pam.SourceColumn];
|
|
this.AddInParameter(ListCommand[index], pam.ParameterName, pam.DbType, value);
|
|
}
|
|
ListCommand[index].Connection = cn;
|
|
ListCommand[index].Transaction = trans;
|
|
ListCommand[index].ExecuteNonQuery();
|
|
index++;
|
|
}
|
|
}
|
|
if (updateCommand != null && table3 != null)
|
|
{
|
|
foreach (DataRow row in table3.Rows)
|
|
{
|
|
ListCommand[index] = this.GetSqlStringCommand(updateCommand.CommandText);
|
|
ListCommand[index].CommandType = updateCommand.CommandType;
|
|
ListCommand[index].CommandTimeout = 300;
|
|
foreach (DbParameter pam in updateCommand.Parameters)
|
|
{
|
|
object value = pam.Value;
|
|
if (value == DBNull.Value) value = row[pam.SourceColumn];
|
|
this.AddInParameter(ListCommand[index], pam.ParameterName, pam.DbType, value);
|
|
}
|
|
ListCommand[index].Connection = cn;
|
|
ListCommand[index].Transaction = trans;
|
|
ListCommand[index].ExecuteNonQuery();
|
|
index++;
|
|
}
|
|
}
|
|
trans.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
trans.Rollback();
|
|
if (ex.Message.Contains("进程已被死锁") == true)
|
|
{
|
|
ConnectClose(cn);
|
|
KillProcess();
|
|
}
|
|
throw new Exception(ex.Message);
|
|
}
|
|
finally
|
|
{
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
|
|
public void UpdateDataTable(DataTable tb, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
|
|
{
|
|
DataTable table1 = tb.GetChanges(DataRowState.Deleted);
|
|
if (table1 != null) table1.RejectChanges();
|
|
DataTable table2 = tb.GetChanges(DataRowState.Added);
|
|
DataTable table3 = tb.GetChanges(DataRowState.Modified);
|
|
int len = 0;
|
|
if (table1 != null) len += table1.Rows.Count;
|
|
if (table2 != null) len += table2.Rows.Count;
|
|
if (table3 != null) len += table3.Rows.Count;
|
|
if (len == 0) return;
|
|
DbConnection cn = CreateConnection();
|
|
ConnectOpen(cn);
|
|
//DbTransaction trans = cn.BeginTransaction();
|
|
try
|
|
{
|
|
DbCommand[] ListCommand = new DbCommand[len];
|
|
int index = 0;
|
|
if (deleteCommand != null && table1 != null)
|
|
{
|
|
foreach (DataRow row in table1.Rows)
|
|
{
|
|
ListCommand[index] = this.GetSqlStringCommand(deleteCommand.CommandText);
|
|
ListCommand[index].CommandType = deleteCommand.CommandType;
|
|
ListCommand[index].CommandTimeout = 300;
|
|
foreach (DbParameter pam in deleteCommand.Parameters)
|
|
{
|
|
object value = pam.Value;
|
|
if (value == DBNull.Value) value = row[pam.SourceColumn];
|
|
this.AddInParameter(ListCommand[index], pam.ParameterName, pam.DbType, value);
|
|
}
|
|
ListCommand[index].Connection = cn;
|
|
//ListCommand[index].Transaction = trans;
|
|
ListCommand[index].ExecuteNonQuery();
|
|
index++;
|
|
}
|
|
}
|
|
if (insertCommand != null && table2 != null)
|
|
{
|
|
foreach (DataRow row in table2.Rows)
|
|
{
|
|
ListCommand[index] = this.GetSqlStringCommand(insertCommand.CommandText);
|
|
ListCommand[index].CommandType = insertCommand.CommandType;
|
|
ListCommand[index].CommandTimeout = 300;
|
|
foreach (DbParameter pam in insertCommand.Parameters)
|
|
{
|
|
object value = pam.Value;
|
|
if (value == DBNull.Value) value = row[pam.SourceColumn];
|
|
this.AddInParameter(ListCommand[index], pam.ParameterName, pam.DbType, value);
|
|
}
|
|
ListCommand[index].Connection = cn;
|
|
//ListCommand[index].Transaction = trans;
|
|
ListCommand[index].ExecuteNonQuery();
|
|
index++;
|
|
}
|
|
}
|
|
if (updateCommand != null && table3 != null)
|
|
{
|
|
foreach (DataRow row in table3.Rows)
|
|
{
|
|
ListCommand[index] = this.GetSqlStringCommand(updateCommand.CommandText);
|
|
ListCommand[index].CommandType = updateCommand.CommandType;
|
|
ListCommand[index].CommandTimeout = 300;
|
|
foreach (DbParameter pam in updateCommand.Parameters)
|
|
{
|
|
object value = pam.Value;
|
|
if (value == DBNull.Value) value = row[pam.SourceColumn];
|
|
this.AddInParameter(ListCommand[index], pam.ParameterName, pam.DbType, value);
|
|
}
|
|
ListCommand[index].Connection = cn;
|
|
//ListCommand[index].Transaction = trans;
|
|
ListCommand[index].ExecuteNonQuery();
|
|
index++;
|
|
}
|
|
}
|
|
//trans.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//trans.Rollback();
|
|
if (ex.Message.Contains("进程已被死锁") == true)
|
|
{
|
|
ConnectClose(cn);
|
|
KillProcess();
|
|
}
|
|
throw new Exception(ex.Message);
|
|
}
|
|
finally
|
|
{
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 参数格式化
|
|
protected virtual void DeriveParameters(DbCommand discoveryCommand)
|
|
{
|
|
if (this.m_Factory == null)
|
|
{
|
|
throw new ArgumentNullException("DbProviderFactory");
|
|
}
|
|
MethodInfo method = this.m_Factory.CreateCommandBuilder().GetType().GetMethod("DeriveParameters");
|
|
if (method == null)
|
|
{
|
|
throw new ArgumentException("没有可以读取的参数,存储过程错误");
|
|
}
|
|
method.Invoke(null, new object[] { discoveryCommand });
|
|
}
|
|
|
|
public void DiscoverParameters(DbCommand command)
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
ConnectOpen(cn);
|
|
DbCommand discoveryCommand = this.CreateCommandByCommandType(command.CommandType, command.CommandText);
|
|
discoveryCommand.Connection = cn;
|
|
this.DeriveParameters(discoveryCommand);
|
|
foreach (IDataParameter parameter in discoveryCommand.Parameters)
|
|
{
|
|
IDataParameter cloneParameter = (IDataParameter)((ICloneable)parameter).Clone();
|
|
command.Parameters.Add(cloneParameter);
|
|
}
|
|
ConnectClose(cn);
|
|
}
|
|
#endregion
|
|
|
|
#region 创建有自动增加ID列(列名RowIndex)的DataSet
|
|
/// <summary>
|
|
/// 创建有自动增加ID列(列名RowIndex)的DataSet
|
|
/// </summary>
|
|
/// <param name="TableCount">要初始化的DataTable表数据</param>
|
|
/// <returns>返回DataSet</returns>
|
|
public DataSet CreateDataSet(int TableCount)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
DataTable tb = null;
|
|
for (int i = 0; i < TableCount; i++)
|
|
{
|
|
string TableName = "Table";
|
|
if (i > 0) TableName += i.ToString();
|
|
tb = new DataTable(TableName);
|
|
tb.Columns.Add("RowIndex", typeof(int));
|
|
tb.Columns[0].AutoIncrement = true;
|
|
tb.Columns[0].AutoIncrementSeed = 1;
|
|
ds.Tables.Add(tb);
|
|
}
|
|
return ds;
|
|
}
|
|
#endregion
|
|
|
|
#region 创建有自动增加ID列(列名RowIndex)的DataTable
|
|
/// <summary>
|
|
/// 创建有自动增加ID列(列名RowIndex)的DataSet
|
|
/// </summary>
|
|
/// <returns>返回DataTable</returns>
|
|
public DataTable CreateDataTable()
|
|
{
|
|
DataTable tb = null;
|
|
tb = new DataTable("Table");
|
|
tb.Columns.Add("RowIndex", typeof(int));
|
|
tb.Columns[0].AutoIncrement = true;
|
|
tb.Columns[0].AutoIncrementSeed = 1;
|
|
return tb;
|
|
}
|
|
#endregion
|
|
|
|
public void LoadDataSet(DbCommand command, DataSet ds, string TableName)
|
|
{
|
|
try
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
command.Connection = cn;
|
|
using (DbDataAdapter adapter = Factory.CreateDataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
adapter.Fill(ds, TableName);
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public void LoadDataSet(DbCommand command, DataSet ds)
|
|
{
|
|
try
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
command.Connection = cn;
|
|
using (DbDataAdapter adapter = Factory.CreateDataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
adapter.Fill(ds);
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public void LoadDataSet(DbCommand command, DataSet ds, string TableName, int StartRowIndex, int RowCount)
|
|
{
|
|
try
|
|
{
|
|
DbConnection cn = CreateConnection();
|
|
command.Connection = cn;
|
|
using (DbDataAdapter adapter = Factory.CreateDataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
ConnectOpen(cn);
|
|
if (this.DataBaseName != "") cn.ChangeDatabase(DataBaseName);
|
|
adapter.Fill(ds, StartRowIndex, RowCount, TableName);
|
|
ConnectClose(cn);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ConnectClose(cn);
|
|
SqlConnection.ClearAllPools();
|
|
if (ex.Message.Contains("进程已被死锁") == true) KillProcess();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
}
|
|
}
|