#region
|
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Data.SqlClient;
|
using System.Data.SqlTypes;
|
using System.Text;
|
using CSFramework.DB.Common;
|
using CSFramework.DB.Models;
|
|
#endregion
|
|
namespace CSFramework.DB
|
{
|
public class DatabaseMSSQL : IDatabase
|
{
|
private string _ConnectionString;
|
|
public DatabaseMSSQL(string connectionString)
|
{
|
_ConnectionString = connectionString;
|
}
|
|
public string BuildConnectionString(string server, int port,
|
string dbName, string uid, string pwd,
|
int timeout = 15)
|
{
|
const string DEF_SQL_CONNECTION =
|
@"Data Source={0};Initial Catalog={1};User ID={2};Password ={3};Persist Security Info=True;Connect Timeout={4};";
|
var connstr = "";
|
if (port == DefaultPort)
|
connstr = string.Format(DEF_SQL_CONNECTION, server, dbName, uid,
|
pwd, timeout);
|
else
|
connstr = string.Format(DEF_SQL_CONNECTION, server + "," + port,
|
dbName, uid, pwd, timeout);
|
|
return connstr;
|
}
|
|
public void Close(DbConnection connection)
|
{
|
if (connection != null)
|
{
|
connection.Close();
|
connection.Dispose();
|
}
|
}
|
|
public int CommandTimeout
|
{
|
get
|
{
|
var defaultTimeOut = 30;
|
return DatabaseFactory.CommandTimeOut > 0
|
? DatabaseFactory.CommandTimeOut
|
: defaultTimeOut;
|
}
|
}
|
|
public string ConnectionString
|
{
|
get => _ConnectionString;
|
set => _ConnectionString = value;
|
}
|
|
public int ConnectionTimeout
|
{
|
get
|
{
|
var defaultTimeOut = 15;
|
return DatabaseFactory.ConnectionTimeOut > 0
|
? DatabaseFactory.ConnectionTimeOut
|
: defaultTimeOut;
|
}
|
}
|
|
public CommandHelper CreateCommand(string commandText)
|
{
|
return new CommandHelper(this, commandText, CommandType.Text);
|
}
|
|
public DbCommand CreateCommand(string commandText,
|
CommandType commandType)
|
{
|
return new SqlCommand(commandText)
|
{
|
CommandText = commandText, CommandType = commandType,
|
CommandTimeout = CommandTimeout
|
};
|
}
|
|
public DbCommandBuilder CreateCommandBuilder()
|
{
|
return new SqlCommandBuilder();
|
}
|
|
public DbConnection CreateConnection()
|
{
|
return CreateConnection(_ConnectionString);
|
}
|
|
public DbConnection CreateConnection(string connectionString)
|
{
|
DbConnection DBConn = new SqlConnection(connectionString);
|
DBConn.Open(); //打开连接
|
return DBConn;
|
}
|
|
public DbDataAdapter CreateDataAdapter()
|
{
|
return new SqlDataAdapter();
|
}
|
|
public DbParameter CreateParameter(string parameterName,
|
DbType parameterType, int size, string sourceColumn,
|
object parameterValue)
|
{
|
parameterName = ParseParamName(parameterName);
|
return new SqlParameter
|
{
|
ParameterName = parameterName, DbType = parameterType,
|
Size = size, SourceColumn = sourceColumn,
|
Value = parameterValue, IsNullable = true
|
};
|
}
|
|
public DbParameter CreateParameter(string parameterName,
|
object parameterValue)
|
{
|
parameterName = ParseParamName(parameterName);
|
return new SqlParameter
|
{
|
ParameterName = parameterName, Value = parameterValue,
|
IsNullable = true
|
};
|
}
|
|
public CommandHelper CreateSqlProc(string spName)
|
{
|
return new CommandHelper(this, spName, CommandType.StoredProcedure);
|
}
|
|
/// <summary>
|
/// 创建时间戳参数
|
/// </summary>
|
/// <param name="parameterName">参数名</param>
|
/// <param name="fieldName">字段名</param>
|
/// <returns></returns>
|
public DbParameter CreateTimestampParameter(string parameterName,
|
string fieldName)
|
{
|
var p = new SqlParameter();
|
p.ParameterName = parameterName;
|
p.SqlDbType = SqlDbType.Timestamp;
|
p.Size = 8;
|
p.SourceColumn = fieldName;
|
return p;
|
}
|
|
public DatabaseType DatabaseType => DatabaseType.SqlServer;
|
|
public Type DateTimeType => typeof(SqlDateTime);
|
|
public int DefaultPort => 1433;
|
|
public int ExecuteCommand(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null && cmd.Transaction == null)
|
cmd.Connection =
|
connection = CreateConnection(_ConnectionString);
|
|
return cmd.ExecuteNonQuery();
|
}
|
finally
|
{
|
if (connection != null) Close(connection);
|
}
|
}
|
|
public DbDataReader ExecuteReader(string SQL)
|
{
|
DbCommand cmd = null;
|
DbDataReader o = null;
|
DbConnection conn = null;
|
try
|
{
|
cmd = new SqlCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn = CreateConnection(_ConnectionString);
|
o = cmd.ExecuteReader();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
|
Close(conn);
|
}
|
|
return o;
|
}
|
|
public DbDataReader ExecuteReader(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
return cmd.ExecuteReader();
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public T ExecuteReader<T>(DbCommand cmd) where T : new()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var obj = default(T);
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
if (dataReader.Read())
|
obj = DbTools.Convert2Object<T>(dataReader);
|
}
|
|
return obj;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public T ExecuteReader<T>(string SQL) where T : new()
|
{
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var obj = default(T);
|
using (dataReader = command.ExecuteReader())
|
{
|
if (dataReader.Read())
|
obj = DbTools.Convert2Object<T>(dataReader);
|
}
|
|
return obj;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
|
if (command != null) command.Dispose();
|
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<T> ExecuteReader<T>(string SQL,
|
Func<DbDataReader, T> action) where T : new()
|
{
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var list = new List<T>();
|
using (dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(action.Invoke(dataReader));
|
}
|
|
return list;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
|
if (command != null) command.Dispose();
|
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<T> ExecuteReader<T>(DbCommand cmd,
|
Func<DbDataReader, T> action) where T : new()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var list = new List<T>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(action.Invoke(dataReader));
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public List<T> ExecuteReaderList<T>(string SQL) where T : new()
|
{
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var list = new List<T>();
|
using (dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(DbTools.Convert2Object<T>(dataReader));
|
}
|
|
return list;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
|
if (command != null) command.Dispose();
|
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<T> ExecuteReaderList<T>(DbCommand cmd) where T : new()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var list = new List<T>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(DbTools.Convert2Object<T>(dataReader));
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public object ExecuteScalar(string SQL)
|
{
|
DbConnection connection = null;
|
DbCommand cmd = null;
|
try
|
{
|
cmd = CreateCommand(SQL, CommandType.Text);
|
cmd.Connection = connection = CreateConnection();
|
return cmd.ExecuteScalar();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
|
Close(connection);
|
}
|
}
|
|
public object ExecuteScalar(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
return cmd.ExecuteScalar();
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public int ExecuteSQL(string SQL)
|
{
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection();
|
return ExecuteSQL(SQL, connection);
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
|
/// <summary>
|
/// 在事务内提交数据
|
/// </summary>
|
/// <param name="trans">事务</param>
|
/// <param name="SQL">SQL语句</param>
|
/// <returns></returns>
|
public int ExecuteTrans(DbTransaction trans, string SQL)
|
{
|
DbCommand cmd = null;
|
var i = -1;
|
try
|
{
|
cmd = new SqlCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = trans.Connection;
|
cmd.Transaction = trans;
|
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
public int ExecuteTrans(DbTransaction trans, DbCommand cmd)
|
{
|
var i = -1;
|
try
|
{
|
cmd.Connection = trans.Connection;
|
cmd.Transaction = trans;
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
public DataRow GetDataRow(string SQL)
|
{
|
var dt = GetDataSet(SQL).Tables[0];
|
return dt.Rows.Count > 0 ? dt.Rows[0] : null;
|
}
|
|
public DataSet GetDataSet(DbCommand cmd)
|
{
|
DbDataAdapter adapter = null;
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection =
|
connection = CreateConnection(_ConnectionString);
|
|
var ds = new DataSet();
|
adapter = new SqlDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
|
if (cmd != null) cmd.Dispose();
|
|
Close(connection);
|
}
|
}
|
|
public DataSet GetDataSet(string SQL)
|
{
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection(_ConnectionString);
|
return GetDataSet(SQL, connection);
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
/// <summary>
|
/// 执行带参数的 Sql 语句或存储过程，并返回 DataSet 对象。
|
/// </summary>
|
/// <param name="SQL">要执行的 Sql 语句或存储过程名等。</param>
|
/// <param name="type">CommandType 参数类型，即该命令是 sql 语句，还是存储过程名等。</param>
|
/// <param name="paramlist">参数集合。</param>
|
/// <returns>DataSet 对象。</returns>
|
public virtual DataSet GetDataSet(string SQL, CommandType type,
|
IDataParameter[] paramlist)
|
{
|
DbCommand cmd = null;
|
DbDataAdapter adapter = null;
|
DbConnection connection = null;
|
try
|
{
|
cmd = CreateCommand(SQL, type);
|
cmd.Connection = connection = CreateConnection();
|
|
if (paramlist != null) cmd.Parameters.AddRange(paramlist);
|
|
var ds = new DataSet();
|
adapter = CreateDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
|
if (cmd != null) cmd.Dispose();
|
|
Close(connection);
|
}
|
}
|
|
/// <summary>
|
/// 获取当前表最新(最大)的自增字段值
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <returns></returns>
|
public int GetMaxID(string tableName)
|
{
|
//mssql写法
|
var sql = $"select ident_current('{tableName}');";
|
var o = ExecuteScalar(sql);
|
return o == DBNull.Value || o == null ? 0 : Convert.ToInt32(o);
|
}
|
|
/// <summary>
|
/// 服务器时间
|
/// </summary>
|
public DateTime GetServerTime()
|
{
|
var o = ExecuteScalar("SELECT GETDATE() AS SERVER_DATE");
|
return Convert.ToDateTime(o);
|
}
|
|
public List<string> GetStringList(string SQL)
|
{
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var list = new List<string>();
|
using (dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(dataReader.GetValue(0)
|
.ToString()); //取第1列
|
}
|
|
return list;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
|
if (command != null) command.Dispose();
|
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<string> GetStringList(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var list = new List<string>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(dataReader.GetValue(0).ToString()); //取第1列
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public DataTable GetTable(string SQL, string tableName = "")
|
{
|
var dt = GetDataSet(SQL).Tables[0];
|
if (tableName != "") dt.TableName = tableName;
|
|
return dt;
|
}
|
|
public DataTable GetTable(DbCommand cmd, string tableName = "")
|
{
|
var dt = GetDataSet(cmd).Tables[0];
|
if (tableName != "") dt.TableName = tableName;
|
|
return dt;
|
}
|
|
public DataTable GetTop(int top, string tableName, string fields = "",
|
List<DbParameter> where = null,
|
string orderBy = "")
|
{
|
var cmd = CreateCommand("");
|
|
fields = string.IsNullOrWhiteSpace(fields) ? "*" : fields;
|
orderBy = string.IsNullOrEmpty(orderBy)
|
? ""
|
: " ORDER BY " + orderBy;
|
|
var whereSQL = "";
|
if (where != null && where.Count > 0)
|
{
|
string pName;
|
foreach (var p in where)
|
{
|
pName = p.ParameterName;
|
|
if (pName.IndexOf(ParamSymboName) >= 0) //参数名称带有符号,如：@Code
|
whereSQL = whereSQL +
|
$" AND {pName.Replace(ParamSymboName, "")}={p.ParameterName}";
|
else
|
whereSQL = whereSQL +
|
$" AND {p.ParameterName}={ParamSymboName + p.ParameterName}";
|
|
cmd.Command.Parameters.Add(p);
|
}
|
}
|
|
whereSQL = string.IsNullOrEmpty(whereSQL)
|
? ""
|
: " WHERE 1=1 " + whereSQL;
|
|
var SQL =
|
$"SELECT TOP {top} {fields} FROM {tableName} {whereSQL} {orderBy};";
|
|
cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
return GetTable(cmd.Command, tableName);
|
}
|
|
public DataTable meta_GetDatabase()
|
{
|
var sql =
|
"SELECT Name AS DBName,filename AS FilePath,crdate AS CreationTime,Version FROM Master..SysDatabases";
|
return GetTable(sql, "SysDatabases");
|
}
|
|
public List<MetaDBNames> meta_GetDatabaseList()
|
{
|
var sql =
|
"SELECT Name AS DBName,filename AS FilePath,crdate AS CreationTime,Version FROM Master..SysDatabases";
|
return ExecuteReaderList<MetaDBNames>(sql);
|
}
|
|
public List<MetaStoreProcedure> meta_GetStoreProcList(
|
string dbName = "")
|
{
|
var sql =
|
"SELECT type,name FROM sys.sql_modules inner join sysobjects on sys.sql_modules.object_id = sysobjects.id and type in ('p','fn') and category=0";
|
|
CodeSafeHelper.IsCheckSQM = false;
|
var result = new List<MetaStoreProcedure>();
|
var sp = CreateCommand(sql);
|
var dt = GetTable(sp.Command);
|
foreach (DataRow R in dt.Rows)
|
{
|
if (R["type"].ToString().Trim().ToUpper() == "P")
|
result.Add(new MetaStoreProcedure
|
{
|
Type = MetaStoreProcedureType.StoreProcedure,
|
Name = R["name"].ToString()
|
});
|
|
if (R["type"].ToString().Trim().ToUpper() == "FN")
|
result.Add(new MetaStoreProcedure
|
{
|
Type = MetaStoreProcedureType.Function,
|
Name = R["name"].ToString()
|
});
|
}
|
|
return result;
|
}
|
|
public DataTable meta_GetTableNames(string dbName = "")
|
{
|
return GetTable(meta_GetTablesNamesSQL());
|
}
|
|
public List<MetaTableNames> meta_GetTableNamesList(string dbName = "")
|
{
|
return ExecuteReaderList<MetaTableNames>(meta_GetTablesNamesSQL());
|
}
|
|
public DataTable meta_GetTableStru(string tableName)
|
{
|
var b = CodeSafeHelper.IsCheckSQM;
|
try
|
{
|
CodeSafeHelper.IsCheckSQM = false;
|
var sp = CreateCommand(meta_GetTableStruSQL());
|
sp.AddParam("TableName", tableName);
|
return GetTable(sp.Command, tableName);
|
}
|
finally
|
{
|
CodeSafeHelper.IsCheckSQM = b;
|
}
|
}
|
|
public List<MetaTableStructure> meta_GetTableStruList(string tableName)
|
{
|
var b = CodeSafeHelper.IsCheckSQM;
|
try
|
{
|
CodeSafeHelper.IsCheckSQM = false;
|
var sp = CreateCommand(meta_GetTableStruSQL());
|
sp.AddParam("TableName", tableName);
|
return ExecuteReaderList<MetaTableStructure>(sp.Command);
|
}
|
finally
|
{
|
CodeSafeHelper.IsCheckSQM = b;
|
}
|
}
|
|
/// <summary>
|
/// 参数符号.如: WHERE Code=@Code
|
/// </summary>
|
public string ParamSymboName { get; set; } = "@";
|
|
/// <summary>
|
/// 返回包含参数符号的参数名称,比如：@Code, :Code, ?p_Code
|
/// </summary>
|
/// <param name="paramName"></param>
|
/// <returns></returns>
|
public string ParseParamName(string paramName)
|
{
|
if (string.IsNullOrEmpty(ParamSymboName) ||
|
string.IsNullOrEmpty(paramName)) return paramName;
|
|
if (paramName.IndexOf(ParamSymboName) == 0) //首字母包含
|
return paramName;
|
|
return ParamSymboName + paramName; //添加参数符号
|
}
|
|
public DbType ToDbType(string sourceType)
|
{
|
return DBDataTypes.GetDbTypeByMsSqlType(sourceType);
|
}
|
|
public Type ToNetType(string sourceType)
|
{
|
return DBDataTypes.GetNetTypeByMsSql(sourceType);
|
}
|
|
public DbTransaction TransBegin()
|
{
|
return CreateConnection().BeginTransaction();
|
}
|
|
public void TransCommit(DbTransaction trans,
|
bool closeConnection = false)
|
{
|
trans.Commit();
|
if (closeConnection) Close(trans.Connection);
|
}
|
|
public void TransRollback(DbTransaction trans,
|
bool closeConnection = false)
|
{
|
trans.Rollback();
|
if (closeConnection) Close(trans.Connection);
|
}
|
|
public bool UpdateDataSet(DataTable ds, string tableName, string KEY)
|
{
|
//先删除原表数据
|
var sql = "";
|
var ErrorMsg = "";
|
foreach (DataRow dr in ds.Rows)
|
{
|
sql = string.Format(@"delete from {0} where {1} in ('{2}')",
|
tableName, KEY, dr[KEY]);
|
break;
|
}
|
|
var a = ExecuteSQL(sql);
|
var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
|
for (var i = 0; i < ds.Columns.Count; i++)
|
colMapping[i] =
|
new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName,
|
ds.Columns[i].ColumnName);
|
|
DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping,
|
ref ErrorMsg);
|
return true;
|
}
|
|
|
public bool InstDataSet(DataTable ds, string tableName)
|
{
|
//先删除原表数据
|
var ErrorMsg = "";
|
var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
|
for (var i = 0; i < ds.Columns.Count; i++)
|
colMapping[i] =
|
new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName,
|
ds.Columns[i].ColumnName);
|
|
DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping,
|
ref ErrorMsg);
|
return true;
|
}
|
|
public int ExecuteSQLT(string conn, string sql, object FingerDataTwo,
|
object Report)
|
{
|
DbConnection connection = null;
|
try
|
{
|
var ConnectString = conn;
|
|
//string sql = "update SYS_Reports set Data = @FingerDataTwo WHERE ReportTitle= @UserId";
|
var con = new SqlConnection(ConnectString);
|
var cmd = new SqlCommand(sql, con);
|
cmd.Parameters.Add("@FingerDataTwo", SqlDbType.VarBinary)
|
.Value = FingerDataTwo;
|
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = Report;
|
con.Open();
|
var i = cmd.ExecuteNonQuery();
|
con.Close();
|
|
return 1;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
//2022-05-01 liu
|
public bool AddTable(DataTable dt, string tableName)
|
{
|
using (var sqlCon = new SqlConnection(_ConnectionString))
|
{
|
try
|
{
|
sqlCon.Open();
|
using (var bulkCopy = new SqlBulkCopy(sqlCon))
|
{
|
bulkCopy.DestinationTableName = tableName;
|
for (var i = 0; i < dt.Columns.Count; i++)
|
bulkCopy.ColumnMappings.Add(dt.Columns[i].Caption,
|
dt.Columns[i].Caption);
|
|
bulkCopy.WriteToServer(dt);
|
return true;
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
sqlCon.Close();
|
}
|
}
|
}
|
|
#region 封装批量插入数据SQL Server数据的方法
|
|
/// <summary>
|
/// 封装批量插入数据SQL Server数据的方法
|
/// </summary>
|
/// <param name="dt">源数据表</param>
|
/// <param name="connectString">数据库连接字符串</param>
|
/// <param name="tableName">目标表名</param>
|
/// <param name="colMapping">字段映射</param>
|
public bool DataTableToSQLServer(DataTable dt, string connectString,
|
string tableName,
|
SqlBulkCopyColumnMapping[] colMapping, ref string msg)
|
{
|
using (var destinationConnection = new SqlConnection(connectString))
|
{
|
destinationConnection.Open();
|
|
using (var bulkCopy = new SqlBulkCopy(destinationConnection,
|
SqlBulkCopyOptions.FireTriggers, null))
|
{
|
try
|
{
|
bulkCopy.DestinationTableName = tableName; //要插入的表的表名
|
bulkCopy.BatchSize = dt.Rows.Count;
|
foreach (var item in colMapping)
|
bulkCopy.ColumnMappings.Add(item);
|
|
bulkCopy.WriteToServer(dt);
|
return true;
|
}
|
catch (Exception ex)
|
{
|
msg = "[" + tableName + "]" + ex.Message;
|
return false;
|
}
|
}
|
}
|
}
|
|
#endregion
|
|
public bool DeleteDataSet(DataTable ds, string tableName, string KEY)
|
{
|
var sql = "";
|
var ErrorMsg = "";
|
foreach (DataRow dr in ds.Rows)
|
{
|
sql = string.Format(@"delete from {0} where {1} in ('{2}')",
|
tableName, KEY, dr[KEY]);
|
break;
|
}
|
|
try
|
{
|
var a = ExecuteSQL(sql);
|
if (a > 0) return true;
|
}
|
catch (Exception)
|
{
|
return false;
|
}
|
|
return false;
|
}
|
|
public int ExecuteSQL(string SQL, DbConnection conn)
|
{
|
DbCommand cmd = null;
|
var i = -1;
|
try
|
{
|
cmd = new SqlCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
public DataSet GetDataSet(string SQL, DbConnection conn)
|
{
|
DbCommand cmd = null;
|
DbDataAdapter adapter = null;
|
try
|
{
|
cmd = new SqlCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
|
var ds = new DataSet();
|
adapter = new SqlDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
|
if (cmd != null) cmd.Dispose();
|
}
|
}
|
|
private DbCommand meta_GetTablesNamesSQL()
|
{
|
var cmd =
|
CreateCommand(
|
"select id,type,name from sysobjects where type in(@U,@V) order by name");
|
cmd.AddParam("@U", "U");
|
cmd.AddParam("@V", "V");
|
return cmd.Command;
|
}
|
|
private string meta_GetTableStruSQL()
|
{
|
var sql = new StringBuilder();
|
sql.AppendLine("SELECT ");
|
sql.AppendLine(" TableName=d.name, ");
|
sql.AppendLine(" FieldOrder=a.colorder,");
|
sql.AppendLine(" FieldName=a.name,");
|
sql.AppendLine(
|
" IsIdentity=CASE WHEN COLUMNPROPERTY(a.id,a.name, 'IsIdentity')=1 THEN 'Y' ELSE 'N' END,");
|
sql.AppendLine(
|
" PK=CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype= 'PK' AND parent_obj=a.id AND name IN (");
|
sql.AppendLine(
|
" SELECT name FROM sysindexes WHERE indid IN(");
|
sql.AppendLine(
|
" SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) THEN 'Y' ELSE 'N' END,");
|
sql.AppendLine(
|
" FK=CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype= 'F' AND parent_obj=a.id AND name IN (");
|
sql.AppendLine(
|
" SELECT name FROM sysindexes WHERE indid IN(");
|
sql.AppendLine(
|
" SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) THEN 'Y' ELSE 'N' END,");
|
sql.AppendLine(" IDX=CASE WHEN EXISTS( ");
|
sql.AppendLine(
|
" SELECT TOP 1 dd.name FROM sysindexes aa JOIN sysindexkeys bb ON aa.id = bb.id AND aa.indid = bb.indid");
|
sql.AppendLine(" JOIN sysobjects cc ON bb.id = cc.id");
|
sql.AppendLine(
|
" JOIN syscolumns dd ON bb.id = dd.id AND bb.colid = dd.colid");
|
sql.AppendLine(
|
" WHERE dd.name=a.name AND dd.id=a.id AND cc.id=d.id AND aa.indid NOT IN ( 0 , 255 ) ) THEN 'Y' ELSE 'N' END,");
|
sql.AppendLine(" FieldType=b.name,");
|
sql.AppendLine(" FieldLength=a.length,");
|
sql.AppendLine(" Prec=COLUMNPROPERTY(a.id,a.name, 'PRECISION '),");
|
sql.AppendLine(
|
" Scale=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale '),0),");
|
sql.AppendLine(
|
" AllowNull=CASE WHEN a.isnullable=1 THEN 'Y' ELSE 'N' END,");
|
sql.AppendLine(" DefaultValue=isnull(e.text, ' '),");
|
sql.AppendLine(
|
" FieldCaption=CASE WHEN ISNULL(g.[value], '')='' THEN a.name ELSE g.[value] END");
|
sql.AppendLine(" FROM syscolumns a");
|
sql.AppendLine(
|
" LEFT JOIN systypes b on a.xusertype=b.xusertype");
|
sql.AppendLine(
|
" INNER JOIN sysobjects d on a.id=d.id and d.xtype IN ('U','V') and d.name <> 'dtproperties'");
|
sql.AppendLine(
|
" LEFT JOIN syscomments e on a.cdefault=e.id");
|
sql.AppendLine(
|
" LEFT JOIN sys.extended_properties g ON a.id=g.major_id and a.colid=g.minor_id "); // --sql2005 改为 sysproperties表
|
sql.AppendLine(
|
" WHERE d.name= @TableName "); //--如果只查询指定表,加上此条件
|
|
return sql.ToString();
|
}
|
|
|
#region
|
|
protected AdapterRowUpdatingEvent _MyRowUpdatingEvent;
|
|
public DbDataAdapter CreateDataAdapter(
|
AdapterRowUpdatingEvent eventHandler)
|
{
|
_MyRowUpdatingEvent = eventHandler;
|
|
var adp = new SqlDataAdapter();
|
adp.RowUpdating += Adp_RowUpdating;
|
|
return adp;
|
}
|
|
private void Adp_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
|
{
|
if (_MyRowUpdatingEvent != null) _MyRowUpdatingEvent(sender, e);
|
}
|
|
#endregion
|
}
|
}
|
