using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CBL.V12.Helper
{
public class SQLHelper
{
//数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
private static string _connectionString = "";
public string ConnectionString
{
get
{
return _connectionString;
}
set
{
_connectionString = value;
}
}
static SQLHelper()
{
_connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
}
/// <summary>
/// 表是否存在
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public static bool TabExists(string TableName)
{
bool cmdresult = true;
try
{
string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
object obj = GetSingle(strsql);
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
cmdresult = false;
}
else
{
if (int.Parse(obj.ToString()) <= 0)
{
cmdresult = false;
}
}
}
catch
{
cmdresult = false;
}
return cmdresult;
}
public static bool ColumnExists(string tableName, string columnName)
{
bool bresult = false;
try
{
string sql = string.Concat(new string[]
{
"select count(1) from syscolumns where [id]=object_id('",tableName,"') and [name]='",columnName,"'"});
object res = GetSingle(sql);
if (object.Equals(res, null) || object.Equals(res, DBNull.Value))
{
bresult = false;
}
else
{
if (int.Parse(res.ToString()) <= 0)
{
bresult = false;
}
}
}
catch
{
bresult = false;
}
return bresult;
}
/// <summary>
/// 返回执行结果
/// </summary>
/// <param name="cmdText"></param>
/// <param name="spts"></param>
/// <returns></returns>
public static bool Excute(string cmdText, SqlParameter[] spts)
{
bool result = false;
SqlConnection sqlConnection = null;
try
{
sqlConnection = new SqlConnection(_connectionString);
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
for (int i = 0; i < spts.Length; i++)
{
sqlCommand.Parameters.Add(spts[i]);
}
sqlCommand.ExecuteNonQuery();
result = true;
}
catch (Exception ex)
{
Lgr.Log.Error(ex.Message, ex);
}
finally
{
if (sqlConnection != null)
{
sqlConnection.Close();
}
}
return result;
}
public static bool Exists(string strSql)
{
bool cmdresult = true;
try
{
object obj = GetSingle(strSql);
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
cmdresult = false;
}
else
{
if (int.Parse(obj.ToString()) <= 0)
{
cmdresult = false;
}
}
}
catch
{
cmdresult = false;
}
return cmdresult;
}
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
bool cmdresult = true;
try
{
object obj = GetSingle(strSql, cmdParms);
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
cmdresult = false;
}
else
{
if (int.Parse(obj.ToString()) <= 0)
{
cmdresult = false;
}
}
}
catch
{
cmdresult = false;
}
return cmdresult;
}
public static int GetMaxID(string FieldName, string TableName)
{
int iresult = -1;
try
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
iresult = -1;
}
else
{
iresult = int.Parse(obj.ToString());
}
}
catch
{
iresult = -1;
}
return iresult;
}
public static void ExecuteInsertCommand(DataTable dt, bool IsDelete = false)
{
if (dt != null && dt.Rows.Count > 0)
{
if (IsDelete)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlTransaction sqlbulkTransaction = conn.BeginTransaction();
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);
for (int i = 0; i < dt.Columns.Count; i++)
{
bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
bulkCopy.DestinationTableName = dt.TableName;
bulkCopy.BatchSize = dt.Rows.Count;
try
{
new SqlCommand
{
Connection = conn,
CommandText = string.Format("TRUNCATE TABLE {0}", dt.TableName),
Transaction = sqlbulkTransaction
}.ExecuteNonQuery();
bulkCopy.WriteToServer(dt);
sqlbulkTransaction.Commit();
}
catch
{
sqlbulkTransaction.Rollback();
}
finally
{
conn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
}
else
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
for (int i = 0; i < dt.Columns.Count; i++)
{
bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
bulkCopy.DestinationTableName = dt.TableName;
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
conn.Close();
}
}
}
}
public static int ExecuteSql(string SQLString)
{
int result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
result = rows;
}
catch (SqlException e)
{
throw e;
}
finally
{
connection.Close();
}
}
}
return result;
}
public static int ExecuteSqlByTime(string SQLString, int Times)
{
int result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
int rows = cmd.ExecuteNonQuery();
result = rows;
}
catch (SqlException e)
{
throw e;
}
finally
{
connection.Close();
}
}
}
return result;
}
public static int ExecuteSql(List<string> SQLStringList)
{
int count = 0;
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand
{
Connection = conn
};
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
string strsql = SQLStringList[i];
if (strsql.Trim().Length > 1)
{
try
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
catch
{
}
}
}
}
catch
{
count = 0;
}
finally
{
conn.Close();
}
}
return count;
}
public static int ExecuteSqlTran(List<string> SQLStringList)
{
int result;
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand
{
Connection = conn
};
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int i = 0; i < SQLStringList.Count; i++)
{
string strsql = SQLStringList[i];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
result = count;
}
catch
{
tx.Rollback();
result = 0;
}
finally
{
conn.Close();
}
}
return result;
}
public static int ExecuteSql(string SQLString, string content)
{
int result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
SqlParameter myParameter = new SqlParameter("@content", SqlDbType.NText)
{
Value = content
};
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
result = rows;
}
catch (SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
return result;
}
public static object ExecuteSqlGet(string SQLString, string content)
{
object result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
SqlParameter myParameter = new SqlParameter("@content", SqlDbType.NText)
{
Value = content
};
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
result = null;
}
else
{
result = obj;
}
}
catch (SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
return result;
}
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
int result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
SqlParameter myParameter = new SqlParameter("@fs", SqlDbType.Image)
{
Value = fs
};
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
result = rows;
}
catch (SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
return result;
}
/// <summary>
/// 返回第一行第一列
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static object GetSingle(string SQLString)
{
object obj = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
obj = cmd.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
obj = null;
}
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
return obj;
}
/// <summary>
/// 返回第一行第一列
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
public static object GetSingle(string SQLString, int Times)
{
object obj = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
obj = cmd.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
obj = null;
}
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
return obj;
}
public static SqlDataReader ExecuteReader(string SQLString)
{
SqlDataReader myReader = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
myReader = cmd.ExecuteReader();
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
return myReader;
}
public static DataSet Query(string SQLString)
{
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
}
return ds;
}
public static DataSet Query(string SQLString, int Times)
{
DataSet result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
new SqlDataAdapter(SQLString, connection)
{
SelectCommand =
{
CommandTimeout = Times
}
}.Fill(ds, "ds");
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
result = ds;
}
return result;
}
public static bool TestConnection()
{
bool resut = false;
try
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
con.Open();
resut = true;
}
}
catch
{
}
return resut;
}
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
int result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
result = rows;
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
}
}
return result;
}
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
foreach (DictionaryEntry myDE in SQLStringList)
{
PrepareCommand(cmd, conn, trans, myDE.Key.ToString(), (SqlParameter[])myDE.Value);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
conn.Close();
}
}
public static int ExecuteSqlTran(List<CommandInfo> cmdList)
{
int result;
using (SqlConnection conn = new SqlConnection(_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();
result = 0;
return result;
}
object obj = cmd.ExecuteScalar();
if (obj == null && obj == DBNull.Value)
{
}
bool isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
result = 0;
return result;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
result = 0;
return result;
}
}
else
{
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
result = 0;
return result;
}
cmd.Parameters.Clear();
}
}
trans.Commit();
result = count;
}
catch
{
trans.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
return result;
}
public static void ExecuteSqlTranWithIndentity(List<CommandInfo> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int indentity = 0;
foreach (CommandInfo myDE in SQLStringList)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
SqlParameter[] array = cmdParms;
for (int i = 0; i < array.Length; i++)
{
SqlParameter q = array[i];
if (q.Direction == ParameterDirection.InputOutput)
{
q.Value = indentity;
}
}
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
array = cmdParms;
for (int i = 0; i < array.Length; i++)
{
SqlParameter q = array[i];
if (q.Direction == ParameterDirection.Output)
{
indentity = Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int indentity = 0;
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
SqlParameter[] array = cmdParms;
for (int i = 0; i < array.Length; i++)
{
SqlParameter q = array[i];
if (q.Direction == ParameterDirection.InputOutput)
{
q.Value = indentity;
}
}
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
array = cmdParms;
for (int i = 0; i < array.Length; i++)
{
SqlParameter q = array[i];
if (q.Direction == ParameterDirection.Output)
{
indentity = Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
conn.Close();
}
}
public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
{
object result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
result = null;
}
else
{
result = obj;
}
}
catch (SqlException e)
{
throw e;
}
}
}
return result;
}
public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader result;
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
result = myReader;
}
catch (SqlException e)
{
throw e;
}
finally
{
//cmd.Dispose();
//connection.Close();
}
return result;
}
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
{
DataSet result;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (SqlException ex)
{
Lgr.Log.Info(ex.Message);
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
result = ds;
}
}
return result;
}
private static 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;
if (cmdParms != null)
{
for (int i = 0; i < cmdParms.Length; i++)
{
SqlParameter parameter = cmdParms[i];
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlDataReader result;
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
SqlCommand command = this.BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
SqlDataReader returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
result = returnReader;
}
return result;
}
public DataSet RunProcedure(string storedProcName, string tableName)
{
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
new SqlDataAdapter { SelectCommand = this.BuildQueryCommand(connection, storedProcName, null) }.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
new SqlDataAdapter { SelectCommand = this.BuildQueryCommand(connection, storedProcName, parameters) }.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
{
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter
{
SelectCommand = this.BuildQueryCommand(connection, storedProcName, parameters)
};
adapter.SelectCommand.CommandTimeout = Times;
adapter.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection)
{
CommandType = CommandType.StoredProcedure
};
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
SqlParameter parameter = (SqlParameter)parameters[i];
if (parameter != null)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
}
return command;
}
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
SqlCommand command = this.BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
int num = (int)command.Parameters["ReturnValue"].Value;
connection.Close();
return num;
}
}
public void RunProcedureNone(string storedProcName, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
connection.Open();
SqlCommand command = this.BuildIntCommand(connection, storedProcName, parameters);
command.ExecuteNonQuery();
connection.Close();
}
}
public DataSet RunProcedure(string storedProcName)
{
DataSet result;
using (SqlConnection sqlConnection = new SqlConnection(this.ConnectionString))
{
DataSet dataSet = new DataSet();
try
{
sqlConnection.Open();
new SqlDataAdapter
{
SelectCommand = new SqlCommand(storedProcName, sqlConnection)
}.Fill(dataSet);
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
sqlConnection.Close();
}
result = dataSet;
}
return result;
}
private SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = this.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;
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="tblName"></param>
/// <param name="strGetFields"></param>
/// <param name="fldName"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="strWhere"></param>
/// <param name="count"></param>
/// <returns></returns>
public static DataSet GetRecordByPage(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, string strWhere, out int count)
{
DataSet ds = new DataSet();
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand
{
Connection = conn
};
SetParams(cmd);
cmd.Parameters[0].Value = tblName;
cmd.Parameters[1].Value = strGetFields;
cmd.Parameters[2].Value = fldName;
cmd.Parameters[3].Value = strWhere;
cmd.Parameters[4].Value = PageSize;
cmd.Parameters[5].Value = PageIndex;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PROCE_PAGECHANGE";
cmd.CommandTimeout = 180;
SqlDataAdapter adapter = new SqlDataAdapter
{
SelectCommand = cmd
};
DataSet source = new DataSet();
adapter.Fill(ds);
//ds.Tables.RemoveAt(0);
object o = cmd.Parameters["@TotalRecord"].Value;
count = (o == null || o == DBNull.Value) ? 0 : System.Convert.ToInt32(o);
}
}
catch (SqlException e)
{
throw e;
}
return ds;
}
private static void SetParams(SqlCommand cmd)
{
cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.VarChar, 255));
cmd.Parameters.Add(new SqlParameter("@ReFieldsStr", SqlDbType.VarChar, 1000));
cmd.Parameters.Add(new SqlParameter("@OrderString", SqlDbType.VarChar, 255));
cmd.Parameters.Add(new SqlParameter("@WhereString", SqlDbType.VarChar, 1500));
cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int));
SqlParameter param = new SqlParameter("@TotalRecord", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(param);
}
/// <summary>
/// 异步执行
/// </summary>
/// <param name="sql"></param>
public static void ExecAsync(string sql)
{
//注意不能写 using, 也不能在 finally 中关闭连接
//连接的关闭只能在 AsyncCallback 异步调用方法中完成
//但在 catch 中必须有关闭操作，防止万一
SqlConnection conn = new SqlConnection(_connectionString);
try
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@isAsync", true);
AsyncCallback callback = new AsyncCallback(HandleCallback);
command.BeginExecuteNonQuery(callback, command);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
if (conn != null)
conn.Close();
}
}
/// <summary>
/// 异步回调方法
/// </summary>
/// <param name="result"></param>
private static void HandleCallback(IAsyncResult result)
{
SqlCommand command = (SqlCommand)result.AsyncState;
try
{
int rowCount = command.EndExecuteNonQuery(result);
Console.WriteLine("{0:HH:mm:ss} 异步方法完成", DateTime.Now);
//PrintInto();
//return rowCount;
//this.Invoke(myDataDelegate, dataTable);
}
catch (Exception ex)
{
Console.WriteLine("Error :{0}", ex.Message);
//return 0;
}
finally
{
if (command.Connection != null)
command.Connection.Close();
}
}
/// <summary>
/// 输出表内容
/// </summary>
private static void PrintInto()
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select isAsync,cnt from dbo.TestAsync", conn);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
//Lgr.Log.Info("isAsync:{0}\t cnt:{1}", sdr["isAsync"], sdr["cnt"]);
}
}
}
public static bool UpdateTable(DataTable dt, string SQLString, string key, SqlConnection conn, SqlTransaction trans, SqlParameter[] cmdParms)
{
bool result = false;
using (SqlCommand cmd = new SqlCommand(SQLString, conn))
{
cmd.Transaction = trans;
cmd.Parameters.Clear();
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);
}
}
DataTable tempDT = new DataTable();
DataTable dsTable = null;
//DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
SqlCommandBuilder cb = new SqlCommandBuilder(adapter)
{
QuotePrefix = "[",
QuoteSuffix = "]"
};
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
adapter.Fill(tempDT);
//DataTable tempDT = new DataTable();
//DataTable dsTable = null;
//设置DataTable主键字段
tempDT.PrimaryKey = new DataColumn[] { tempDT.Columns[key] };
//取DataSet中表1的数据，此处的dsTable是用于盛放从Excel表中读出的数据
dsTable = dt;
dsTable.PrimaryKey = new DataColumn[] { dsTable.Columns[key] };
//遍历dsTable中的每一行数据
foreach (DataRow dr in dsTable.Rows)
{
//Lgr.Log.Info(dr["F_FullName"].ToString());
//在tempDT中查找数据库中是否已经存在该条数据
DataRow modyRow = tempDT.Rows.Find(dr[key]);
//如果数据库已经存在该行，则修改记录，不进行插入
if (modyRow != null)
{
//设置该行记录为“已修改”状态，次操作必须。
tempDT.Rows.Find(dr[key]).SetModified();
//重新给该记录赋值
tempDT.Rows.Find(dr[key]).ItemArray = dr.ItemArray;
}
else
{
//新增
//dr["F_ModuleId"] = keyValue;
tempDT.Rows.Add(dr.ItemArray);
}
}
//删除不在表内的数据
foreach (DataRow row in tempDT.Rows)
{
DataRow modyRow = dsTable.Rows.Find(row[key]);
if (modyRow == null)
{
tempDT.Rows.Find(row[key]).Delete();
}
}
adapter.Update(tempDT);//对表的更新提交到数据库
result = true;
}
catch (SqlException ex)
{
Lgr.Log.Error("", ex);
trans.Rollback();
throw ex;
}
finally
{
//conn.Close();
}
}
return result;
}
public static bool UpdateTable(DataSet ds, string SQLString, string key, SqlConnection conn, SqlTransaction trans, string keyValue, SqlParameter[] cmdParms)
{
bool result = false;
using (SqlCommand cmd = new SqlCommand(SQLString, conn))
{
cmd.Transaction = trans;
cmd.Parameters.Clear();
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);
}
}
DataTable tempDT = new DataTable();
DataTable dsTable = null;
//DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
SqlCommandBuilder cb = new SqlCommandBuilder(adapter)
{
QuotePrefix = "[",
QuoteSuffix = "]"
};
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
adapter.Fill(tempDT);
//DataTable tempDT = new DataTable();
//DataTable dsTable = null;
//设置DataTable主键字段
tempDT.PrimaryKey = new DataColumn[] { tempDT.Columns[key] };
//取DataSet中表1的数据，此处的dsTable是用于盛放从Excel表中读出的数据
dsTable = ds.Tables[0];
dsTable.PrimaryKey = new DataColumn[] { dsTable.Columns[key] };
//遍历dsTable中的每一行数据
foreach (DataRow dr in dsTable.Rows)
{
//Lgr.Log.Info(dr["F_FullName"].ToString());
//在tempDT中查找数据库中是否已经存在该条数据
DataRow modyRow = tempDT.Rows.Find(dr[key]);
//如果数据库已经存在该行，则修改记录，不进行插入
if (modyRow != null)
{
//设置该行记录为“已修改”状态，次操作必须。
tempDT.Rows.Find(dr[key]).SetModified();
//重新给该记录赋值
tempDT.Rows.Find(dr[key]).ItemArray = dr.ItemArray;
}
else
{
//新增
//dr["F_ModuleId"] = keyValue;
tempDT.Rows.Add(dr.ItemArray);
}
}
//删除不在表内的数据
foreach (DataRow row in tempDT.Rows)
{
DataRow modyRow = dsTable.Rows.Find(row[key]);
if (modyRow == null)
{
tempDT.Rows.Find(row[key]).Delete();
}
}
adapter.Update(tempDT);//对表的更新提交到数据库
result = true;
}
catch (SqlException ex)
{
Lgr.Log.Error("", ex);
trans.Rollback();
throw ex;
}
finally
{
//conn.Close();
}
}
return result;
}
}
}
浙公网安备 33010602011771号