using System.Collections.Generic;
|
|
namespace GSProduction.SQLLT
|
{
|
using System;
|
using System.Collections;
|
using System.Collections.Specialized;
|
using System.Data;
|
using System.Data.OleDb;
|
using System.Data.Sql;
|
using System.Data.SqlClient;
|
using System.Text.RegularExpressions;
|
public class SQLHelper : ICloneable, IDisposable
|
{
|
private SqlConnection _connection4Tran;
|
private readonly string _connectionString;
|
private SqlTransaction _transaction;
|
private readonly Queue<SqlTask> _transactionTaskList;
|
|
public SQLHelper(string connectionString)
|
{
|
_transactionTaskList = new Queue<SqlTask>();
|
this._connectionString = connectionString;
|
}
|
|
public SQLHelper(string server, string userID, string password, string database)
|
{
|
_transactionTaskList = new Queue<SqlTask>();
|
if (database == string.Empty)
|
{
|
this._connectionString = "Data Source=" + server + ";User ID=" + userID + ";Password=" + password;
|
}
|
else
|
{
|
this._connectionString = "Data Source=" + server + ";User ID=" + userID + ";Password=" + password + ";Initial Catalog=" + database;
|
}
|
}
|
|
public void BeginTransaction(IsolationLevel isolationLevel)
|
{
|
if ((this._transaction != null) || (this._connection4Tran != null))
|
{
|
throw new Exception("要开始一个新的事务，请先完成当前事务!");
|
}
|
this._connection4Tran = new SqlConnection(this._connectionString);
|
this._connection4Tran.Open();
|
this._transaction = this._connection4Tran.BeginTransaction(isolationLevel);
|
_transactionTaskList.Clear();
|
}
|
|
public void CancelTransaction()
|
{
|
if (this._transaction != null)
|
{
|
this._transaction.Dispose();
|
}
|
if (this._connection4Tran != null)
|
{
|
this._connection4Tran.Close();
|
}
|
if (this._connection4Tran != null)
|
{
|
this._connection4Tran.Dispose();
|
}
|
this._transaction = null;
|
this._connection4Tran = null;
|
}
|
|
public object Clone()
|
{
|
return new SQLHelper(this._connectionString);
|
}
|
|
public void CommitTransaction()
|
{
|
try
|
{
|
if (_transactionTaskList.Count > 0)
|
{
|
foreach (SqlTask sqlTask in _transactionTaskList)
|
{
|
using (var command = new SqlCommand(sqlTask.Text, _connection4Tran))
|
{
|
command.CommandType = sqlTask.CommandType;
|
if (sqlTask.Parameters != null)
|
{
|
foreach (SqlParameter parameter in sqlTask.Parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
command.Transaction = _transaction;
|
command.ExecuteNonQuery();
|
}
|
}
|
}
|
this._transaction.Commit();
|
}
|
catch (Exception)
|
{
|
this._transaction.Rollback();
|
}
|
finally
|
{
|
if (this._transaction != null)
|
{
|
this._transaction.Dispose();
|
}
|
if (this._connection4Tran != null)
|
{
|
this._connection4Tran.Close();
|
}
|
if (this._connection4Tran != null)
|
{
|
this._connection4Tran.Dispose();
|
}
|
this._transaction = null;
|
this._connection4Tran = null;
|
}
|
}
|
|
public DataSet ExecuteDataSet(string sql)
|
{
|
return this.ExecuteDataSet(sql, CommandType.Text, null);
|
}
|
|
|
public DataSet ExecuteDataSet(string sql, CommandType commandType)
|
{
|
return this.ExecuteDataSet(sql, commandType, null);
|
}
|
|
public DataSet ExecuteDataSet(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
DataSet dataSet = new DataSet(Guid.NewGuid().ToString());
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
using (SqlCommand command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
new SqlDataAdapter(command).Fill(dataSet);
|
}
|
}
|
return dataSet;
|
}
|
public DataTable ExecuteDataTable(string sql)
|
{
|
return this.ExecuteDataTable(sql, CommandType.Text, null);
|
}
|
|
public DataTable ExecuteDataTable(string sql, CommandType commandType)
|
{
|
return this.ExecuteDataTable(sql, commandType, null);
|
}
|
|
public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
DataTable dataTable = new DataTable(Guid.NewGuid().ToString());
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
using (SqlCommand command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
new SqlDataAdapter(command).Fill(dataTable);
|
}
|
}
|
return dataTable;
|
}
|
|
public DataTable ExecuteStoredProcedure(string procedureName, SqlParameter[] parameters = null)
|
{
|
try
|
{
|
using (SqlConnection connection = new SqlConnection(_connectionString))
|
{
|
using (SqlCommand command = new SqlCommand(procedureName, connection))
|
{
|
command.CommandType = CommandType.StoredProcedure;
|
|
// 添加参数（如果存在）
|
if (parameters != null && parameters.Length > 0)
|
{
|
command.Parameters.AddRange(parameters);
|
}
|
|
connection.Open();
|
|
// 创建DataTable存储结果
|
DataTable resultTable = new DataTable();
|
|
// 使用SqlDataAdapter填充DataTable
|
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
|
{
|
adapter.Fill(resultTable);
|
}
|
|
return resultTable;
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
return null;
|
}
|
}
|
|
public int ExecuteNonQuery(string sql)
|
{
|
return this.ExecuteNonQuery(sql, CommandType.Text, null);
|
}
|
|
public int ExecuteNonQuery(string sql, CommandType commandType)
|
{
|
return this.ExecuteNonQuery(sql, commandType, null);
|
}
|
|
public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
return this.ExecuteNonQuery(sql, commandType, parameters, false);
|
}
|
//重载方法，返回一个值
|
public string GetSqlAsString(string sqlText)
|
{
|
return this.GetSqlAsString(sqlText, null);
|
}
|
//返回列数
|
public int GetSqlAsInt(string sqlText)
|
{
|
return this.GetSqlAsInt(sqlText, null);
|
}
|
public string GetSqlAsString(string sqlText, SqlParameter[] sqlParameters)
|
{
|
string result = "";
|
SqlDataReader reader;
|
SqlConnection connection = new SqlConnection(this._connectionString);
|
using (connection)
|
{
|
SqlCommand sqlcommand = connection.CreateCommand();
|
sqlcommand.CommandText = sqlText;
|
if (sqlParameters != null)
|
{
|
sqlcommand.Parameters.AddRange(sqlParameters);
|
}
|
connection.Open();
|
reader = sqlcommand.ExecuteReader();
|
if (reader != null)
|
if (reader.Read())
|
{
|
result = reader.GetString(0);
|
}
|
}
|
return result;
|
}
|
public int GetSqlAsInt(string sqlText, SqlParameter[] sqlParameters)
|
{
|
|
SqlConnection connection = new SqlConnection(this._connectionString);
|
using (connection)
|
{
|
SqlCommand sqlcommand = connection.CreateCommand();
|
|
sqlcommand.CommandText = sqlText;
|
if (sqlParameters != null)
|
{
|
sqlcommand.Parameters.AddRange(sqlParameters);
|
}
|
connection.Open();
|
int Count = Convert.ToInt32(sqlcommand.ExecuteScalar());
|
return Count;
|
}
|
}
|
|
//public int ExecuteSql(string SQLString)
|
//{
|
// OleDbConnection connection = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;" + this._connectionString);
|
// connection.Open();
|
|
// OleDbCommand command = new OleDbCommand(SQLString, connection);
|
|
// int strValue = Convert.ToInt32(command.ExecuteScalar());
|
|
|
// return strValue;
|
|
|
//}
|
public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters, bool joinTransaction)
|
{
|
int num;
|
SqlCommand command;
|
if (joinTransaction)
|
{
|
if ((this._transaction == null) || (this._connection4Tran == null))
|
{
|
throw new Exception("事务未初始化！");
|
}
|
|
_transactionTaskList.Enqueue(new SqlTask(sql, commandType, parameters));
|
}
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
using (command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
connection.Open();
|
num = command.ExecuteNonQuery();
|
}
|
}
|
return num;
|
}
|
|
public SqlDataReader ExecuteReader(string sql)
|
{
|
return this.ExecuteReader(sql, CommandType.Text, null);
|
}
|
|
public SqlDataReader ExecuteReader(string sql, CommandType commandType)
|
{
|
return this.ExecuteReader(sql, commandType, null);
|
}
|
|
public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
SqlConnection connection = new SqlConnection(this._connectionString);
|
SqlCommand command = new SqlCommand(sql, connection)
|
{
|
CommandType = commandType
|
};
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
connection.Open();
|
return command.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
|
public object ExecuteScalar(string sql)
|
{
|
return this.ExecuteScalar(sql, CommandType.Text, null);
|
}
|
|
public object ExecuteScalar(string sql, CommandType commandType)
|
{
|
return this.ExecuteScalar(sql, commandType, null);
|
}
|
|
|
public object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
object obj2;
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
using (SqlCommand command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
connection.Open();
|
obj2 = command.ExecuteScalar();
|
}
|
}
|
return obj2;
|
}
|
|
|
|
/// <summary>
|
/// 执行存储过程，返回影响的行数
|
/// </summary>
|
/// <param name="storedProcName">存储过程名</param>
|
/// <param name="parameters">存储过程参数</param>
|
/// <param name="rowsAffected">影响的行数</param>
|
/// <returns></returns>
|
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
|
{
|
using (SqlConnection connection = new SqlConnection(_connectionString))
|
{
|
int result;
|
connection.Open();
|
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
|
rowsAffected = command.ExecuteNonQuery();
|
result = (int)command.Parameters["ReturnValue"].Value;
|
//Connection.Close();
|
return result;
|
}
|
}
|
/// <summary>
|
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
|
/// </summary>
|
/// <param name="storedProcName">存储过程名</param>
|
/// <param name="parameters">存储过程参数</param>
|
/// <returns>SqlCommand 对象实例</returns>
|
private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
|
{
|
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
|
command.Parameters.Add(new SqlParameter("ReturnValue",
|
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
|
false, 0, 0, string.Empty, DataRowVersion.Default, null));
|
return command;
|
}
|
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
|
{
|
SqlCommand command = new SqlCommand(storedProcName, connection);
|
command.CommandType = CommandType.StoredProcedure;
|
foreach (SqlParameter parameter in parameters)
|
{
|
if (parameter != null)
|
{
|
// 检查未分配值的输出参数,将其分配以DBNull.Value.
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
(parameter.Value == null))
|
{
|
parameter.Value = DBNull.Value;
|
}
|
command.Parameters.Add(parameter);
|
}
|
}
|
|
return command;
|
}
|
///// <summary>
|
///// 执行多条SQL语句，实现数据库事务。
|
///// </summary>
|
///// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
|
//public int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
|
//{
|
// using (SqlConnection conn = new SqlConnection(this._connectionString))
|
// {
|
// conn.Open();
|
// using (SqlTransaction trans = conn.BeginTransaction())
|
// {
|
// SqlCommand cmd = new SqlCommand();
|
// try
|
// {
|
// int count = 0;
|
// //循环
|
// foreach (CommandInfo myDE in cmdList)
|
// {
|
// string cmdText = myDE.CommandText;
|
// SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
|
// PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
|
|
// if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
|
// {
|
// if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
|
// {
|
// trans.Rollback();
|
// return 0;
|
// }
|
|
// object obj = cmd.ExecuteScalar();
|
// bool isHave = false;
|
// if (obj == null && obj == DBNull.Value)
|
// {
|
// isHave = false;
|
// }
|
// isHave = Convert.ToInt32(obj) > 0;
|
|
// if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
|
// {
|
// trans.Rollback();
|
// return 0;
|
// }
|
// if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
|
// {
|
// trans.Rollback();
|
// return 0;
|
// }
|
// continue;
|
// }
|
// int val = cmd.ExecuteNonQuery();
|
// count += val;
|
// if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
|
// {
|
// trans.Rollback();
|
// return 0;
|
// }
|
// cmd.Parameters.Clear();
|
// }
|
// trans.Commit();
|
// return count;
|
// }
|
// catch (Exception ex)
|
// {
|
// trans.Rollback();
|
// throw;
|
// }
|
// }
|
// }
|
//}
|
|
//private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
|
//{
|
// if (conn.State != ConnectionState.Open)
|
// conn.Open();
|
// cmd.Connection = conn;
|
// cmd.CommandText = cmdText;
|
// if (trans != null)
|
// cmd.Transaction = trans;
|
// cmd.CommandType = CommandType.Text;//cmdType;
|
// if (cmdParms != null)
|
// {
|
// foreach (SqlParameter parameter in cmdParms)
|
// {
|
// if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
// (parameter.Value == null))
|
// {
|
// parameter.Value = DBNull.Value;
|
// }
|
// cmd.Parameters.Add(parameter);
|
// }
|
// }
|
//}
|
|
public DataTable GetDatabases()
|
{
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
connection.Open();
|
return connection.GetSchema("Databases");
|
}
|
}
|
|
//public static ArrayList GetServerList()
|
//{
|
// ArrayList list = new ArrayList();
|
// foreach (DataRow row in SqlDataSourceEnumerator.Instance.GetDataSources().Rows)
|
// {
|
// list.Add(row[0].ToString());
|
// }
|
// return list;
|
//}
|
|
public DataTable GetTables()
|
{
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
connection.Open();
|
return connection.GetSchema("Tables");
|
}
|
}
|
|
public bool SaveDataToDB(DataSet dataSet)
|
{
|
try
|
{
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
foreach (DataTable table in dataSet.Tables)
|
{
|
using (SqlCommand command = new SqlCommand("SELECT * FROM " + table.TableName + " WHERE 1<1", connection))
|
{
|
SqlDataAdapter adapter = new SqlDataAdapter(command);
|
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
|
adapter.UpdateBatchSize = 100;
|
DataTable tbchange = table.GetChanges();
|
if (tbchange != null)
|
adapter.Update(table.GetChanges());
|
}
|
}
|
}
|
}
|
catch (Exception)
|
{
|
return false;
|
}
|
return true;
|
}
|
|
public bool SaveDataToDB(DataTable dataTable)
|
{
|
try
|
{
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
using (SqlCommand command = new SqlCommand("select top 0 * from " + dataTable.TableName, connection))
|
{
|
SqlDataAdapter adapter = new SqlDataAdapter(command);
|
|
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
|
adapter.UpdateBatchSize = 100;
|
DataTable tbchange = dataTable.GetChanges();
|
if (tbchange != null)
|
adapter.Update(dataTable);
|
}
|
}
|
}
|
catch (Exception)
|
{
|
return false;
|
}
|
return true;
|
}
|
|
|
public bool SaveDataToDB(DataSet oldDataSet, DataSet newDataSet)
|
{
|
//bool flag;
|
oldDataSet.Merge(newDataSet, false);
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
connection.Open();
|
SqlTransaction transaction = connection.BeginTransaction();
|
try
|
{
|
foreach (DataTable table in oldDataSet.Tables)
|
{
|
using (SqlCommand command = new SqlCommand("SELECT * FROM " + table.TableName + " WHERE 1<1", connection, transaction))
|
{
|
SqlDataAdapter adapter = new SqlDataAdapter(command);
|
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
|
adapter.UpdateBatchSize = 500;
|
adapter.Update(table);
|
}
|
}
|
transaction.Commit();
|
return true;
|
}
|
catch (Exception)
|
{
|
transaction.Rollback();
|
throw;
|
}
|
finally
|
{
|
if (transaction != null)
|
{
|
transaction.Dispose();
|
}
|
}
|
}
|
//return flag;
|
}
|
|
public bool SaveDataToDB(DataTable oldDataTable, DataTable newDataTable)
|
{
|
bool flag;
|
oldDataTable.Merge(newDataTable, false);
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
using (SqlCommand command = new SqlCommand("SELECT * FROM " + oldDataTable.TableName + " WHERE 1<1", connection))
|
{
|
SqlDataAdapter adapter = new SqlDataAdapter(command);
|
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
|
adapter.UpdateBatchSize = 100;
|
adapter.Update(oldDataTable);
|
flag = true;
|
}
|
}
|
return flag;
|
}
|
|
public bool TestConnection()
|
{
|
bool flag;
|
try
|
{
|
using (SqlConnection connection = new SqlConnection(this._connectionString))
|
{
|
connection.Open();
|
connection.Close();
|
flag = true;
|
}
|
}
|
catch (Exception)
|
{
|
flag = false;
|
}
|
return flag;
|
}
|
|
#region IDisposable 成员
|
|
public void Dispose()
|
{
|
_connection4Tran = null;
|
_transaction = null;
|
}
|
|
#endregion
|
|
private class SqlTask
|
{
|
public string Text { get; private set; }
|
public CommandType CommandType { get; private set; }
|
public SqlParameter[] Parameters { get; private set; }
|
|
public SqlTask(string text, CommandType commandType, SqlParameter[] paras)
|
{
|
Text = text;
|
CommandType = commandType;
|
Parameters = paras;
|
}
|
}
|
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="dt">数据表，必须和同步的表结构一致</param>
|
/// <param name="tbName">同步的表名</param>
|
/// <param name="message">返回消息</param>
|
/// <returns></returns>
|
public bool DataTableToSQLServer(DataTable dt, string tbName)//, ref string message
|
{
|
if (dt == null)
|
return false;
|
using (SqlConnection destinationConnection = new SqlConnection(_connectionString))
|
{
|
destinationConnection.Open();
|
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
|
{
|
try
|
{
|
bulkCopy.DestinationTableName = tbName;//要插入的表的表名
|
bulkCopy.BatchSize = dt.Rows.Count;
|
|
foreach (DataColumn col in dt.Columns)
|
{
|
string colName = col.ColumnName;
|
bulkCopy.ColumnMappings.Add(colName, colName);//映射字段名 DataTable列名 ,数据库 对应的列名
|
}
|
bulkCopy.WriteToServer(dt);
|
//message = string.Format("{0}：插入数据成功\r\n", tbName);
|
return true;
|
}
|
catch
|
{
|
//message = string.Format("{0}：插入数据失败，异常：{1}\r\n", tbName, ex.Message);
|
throw;
|
return false;
|
}
|
finally
|
{
|
}
|
}
|
}
|
}
|
|
|
//public bool Save(DataTable Table, bool UpdateTableStatus, bool UseTransaction, bool ShowMsg)
|
//{
|
// //如果不需要保存则退出。
|
// if (Table == null )
|
// return true;
|
|
// SqlDataAdapter dataAdapter = new SqlDataAdapter();
|
|
// dataAdapter.UpdateCommand = GetUpdateCmd(Table);
|
// dataAdapter.InsertCommand = GetInsertCmd(Table);
|
// dataAdapter.DeleteCommand = GetDeleteCmd(Table);
|
|
|
// if (DBTransaction != null)
|
// {
|
// if (dataAdapter.SelectCommand != null) dataAdapter.SelectCommand.Transaction = DBTransaction;
|
// if (dataAdapter.DeleteCommand != null) dataAdapter.DeleteCommand.Transaction = DBTransaction;
|
// if (dataAdapter.InsertCommand != null) dataAdapter.InsertCommand.Transaction = DBTransaction;
|
// if (dataAdapter.UpdateCommand != null) dataAdapter.UpdateCommand.Transaction = DBTransaction;
|
// }
|
|
// //更新数据
|
// try
|
// {
|
// //启动事务
|
// if (UseTransaction == true)
|
// StartTransaction();
|
|
// DataTable tb_changes = Table.GetChanges();
|
// if (tb_changes != null)
|
// dataAdapter.Update(tb_changes);
|
// else if (HandCreateCmd == true)
|
// dataAdapter.Update(Table);
|
// //提交事务
|
// if (UseTransaction == true)
|
// CommitTransaction();
|
|
// //修改表状态 如果前面的处理出错,那么这里也不会执行,Datatable将继续保留状态.
|
// if (UpdateTableStatus == true)
|
// Table.AcceptChanges();
|
|
// ExecuteExcetion = null;
|
// }
|
// catch (Exception e)
|
// {
|
// string errorMsg = "";
|
// //2627表示主键重复，才需要重取单号。
|
// if (e is SqlException)
|
// {
|
// SqlException sqlExcept = e as SqlException;
|
// SaveKeyExist = (sqlExcept.Number == 2627);
|
// if (sqlExcept.Number == 2627)
|
// errorMsg = "当前数据表已有此记录，请确认后再保存!";
|
// else
|
// errorMsg = ((SqlException)e).Message;
|
// }
|
|
// if (e is DBConcurrencyException)
|
// {
|
// DBConcurrencyException dbexception = (e as DBConcurrencyException);
|
// errorMsg = dbexception.Row.RowError;
|
// if (errorMsg.Contains("影响") == true && dbexception.Row.RowState == DataRowState.Deleted)
|
// errorMsg = "当前数据行已删除,请重新查询!";
|
// if (errorMsg.Contains("影响") == true && dbexception.Row.RowState == DataRowState.Modified)
|
// errorMsg = "当前数据行已修改,请重新查询!";
|
// }
|
|
// //
|
// ShowMsg = ShowMsg || ShowMsgWhenSaveChildTableOnError;
|
// ExecuteExcetion = e;
|
|
// //保存失败回滚事务
|
// if (UseTransaction == true)
|
// RollbackTransaction();
|
|
// errorMsg = (errorMsg == "") ? GetErrorMsg(e) : errorMsg;
|
// WriteLog(Format("保存{0}失败,错误信息如下:{1}", Table.TableName, errorMsg), ShowMsg);
|
// return false;
|
// }
|
// finally
|
// {
|
// Table.State = OptionState.Browse;
|
// }
|
|
// if (ShowMsg == true || MustSaveLog == true)
|
// {
|
// /* 关于ShowMsg参数的使用说明：
|
// * A方法：Save(bool UseTransaction, bool ShowMsg)；
|
// * B方法：Save(DataTableExt Table,bool UpdateTableStatus, bool UseTransaction, bool ShowMsg)；
|
// *
|
// * A方法调用B方法时,是不需要提示"保存成功"的信息，所以A方法传的ShowMsg=false，但A希望在调用B方法出错时，
|
// * 提示错误信息"保存**失败.."，所以原来B方法Catch中硬编码 ShowMsg=true；在执行改方法时,不管是否需要提示,系统都会提示信息(这有违背方法本意)。
|
// *
|
// * 现在希望能利正确使用ShowMsg参数，所以引入了ShowMsgWhenSaveChildTableOnError属性(默认True)。
|
// * 当ShowMsgWhenSaveChildTableOnError等于True或ShowMsg等于True时,Catch中就会提示错误信息。
|
// *
|
// * 单据类BillClass保存时，由于特殊处理，希望过程中不提示错误信息，所以在保存时Save(UseTransaction, ShowMsg==false)和设置ShowMsgWhenSaveChildTableOnError=false。
|
// */
|
// WriteLog(string.Format("保存{0}成功!", Table.TableName), ShowMsg);
|
// }
|
// return true;
|
//}
|
|
// <summary>
|
// 得到更新命令
|
// </summary>
|
// <returns></returns>
|
//public SqlCommand GetUpdateCmd(DataTable MyTable, SqlConnection DBConn)
|
//{
|
// 生成更新语句
|
// string strSQL = string.Format("Update {0} Set ", MyTable.TableName);
|
// foreach (DataColumn col in MyTable.Columns)
|
// {
|
// strSQL = string.Format("{0} {1}=@{1},", strSQL, col.ColumnName);
|
// }
|
// strSQL = strSQL.Substring(0, strSQL.Length - 1);
|
|
// StringCollection KeyList = new StringCollection();
|
// KeyList.Add("taskid");
|
// 根据主键设置Where条件
|
// string strWhere = "";
|
// foreach (string Key in KeyList)
|
// {
|
// strWhere = strWhere == "" ? string.Format("{0}=@{0}", Key) : string.Format("{0} and {1}=@{1}", strWhere, Key);
|
// }
|
|
// strSQL = string.Format("{0} where {1}", strSQL, strWhere);
|
// SqlCommand updateCmd = new SqlCommand(strSQL, DBConn);
|
|
// 添加参数
|
// foreach (DataColumn col in MyTable.Columns)
|
// {
|
// 排除不用保存字段。
|
// SqlDbType SQLType = DataTypeMap.CSTypeToSqlType(col.DataType.Name);
|
// int Size = (col.MaxLength > 0) ? col.MaxLength : 100;
|
// updateCmd.Parameters.Add("@" + col.ColumnName, SQLType, Size, col.ColumnName);
|
// }
|
|
// return updateCmd;
|
//}
|
|
|
///// <summary>
|
///// 得到插入命令
|
///// </summary>
|
///// <returns></returns>
|
//public SqlCommand GetInsertCmd(DataTable MyTable)
|
//{
|
// //取得主键
|
// List<string> KeyList = new List<string>();
|
// KeyList.AddRange(MyTable.KeyField.Split(';'));
|
// if (KeyList.Count <= 0)
|
// return null;
|
|
// //取得不需要更新的字段列表
|
// StringCollection NotSaveFields = new StringCollection();
|
// NotSaveFields.AddRange(MyTable.NotSaveFields.Split(';'));
|
|
// //生成字段列表和值列表
|
// string strFieldList = "";
|
// string strValueList = "";
|
// foreach (DataColumn col in MyTable.Columns)
|
// {
|
// //排除主键和不用保存字段。
|
// if (NotSaveFields.Contains(col.ColumnName) == true)
|
// continue;
|
// strFieldList = strFieldList == "" ? col.ColumnName : string.Format("{0},{1}", strFieldList, col.ColumnName);
|
// strValueList = strValueList == "" ? "@" + col.ColumnName : string.Format("{0},@{1}", strValueList, col.ColumnName);
|
// }
|
// string strSQL = string.Format("Insert Into {0} ({1}) Values({2})", MyTable.UpdateTable, strFieldList, strValueList);
|
|
// //创建命令
|
// SqlCommand InsertCmd = new SqlCommand(strSQL, DBConn);
|
|
// //添加参数
|
// foreach (DataColumn col in MyTable.Columns)
|
// {
|
// //排除主键和不用保存字段。
|
// if (NotSaveFields.Contains(col.ColumnName) == true)
|
// continue;
|
// SqlDbType SQLType = DataTypeMap.CSTypeToSqlType(col.DataType.Name);
|
// int Size = (col.MaxLength > 0) ? col.MaxLength : 100;
|
|
// InsertCmd.Parameters.Add("@" + col.ColumnName, SQLType, Size, col.ColumnName);
|
// }
|
// return InsertCmd;
|
//}
|
|
///// <summary>
|
///// 得到删除命令
|
///// </summary>
|
///// <returns></returns>
|
//public SqlCommand GetDeleteCmd(DataTable MyTable)
|
//{
|
// //取得主键
|
// List<string> KeyList = new List<string>();
|
// KeyList.AddRange(MyTable.KeyField.Split(';'));
|
// if (KeyList.Count <= 0)
|
// return null;
|
|
// //生成更新语句
|
// string strSQL = string.Format("Delete {0} ", MyTable.UpdateTable);
|
|
// //根据主键设置Where条件
|
// string strWhere = "";
|
// foreach (string Key in KeyList)
|
// {
|
// strWhere = strWhere == "" ? string.Format("{0}=@{0}", Key) : string.Format("{0} and {1}=@{1}", strWhere, Key);
|
// }
|
// strSQL = string.Format("{0} where {1}", strSQL, strWhere);
|
// SqlCommand DeleteCmd = new SqlCommand(strSQL, DBConn);
|
|
// //添加参数
|
// foreach (string Key in KeyList)
|
// {
|
// DataColumn col = MyTable.Columns[Key];
|
// SqlDbType SQLType = DataTypeMap.CSTypeToSqlType(col.DataType.Name);
|
// int Size = (col.MaxLength > 0) ? col.MaxLength : 100;
|
// DeleteCmd.Parameters.Add("@" + col.ColumnName, SQLType, Size, col.ColumnName);
|
// }
|
|
// return DeleteCmd;
|
//}
|
}
|
}
|
