public static int ExecuteSqlByTime(DbSetting dbset, string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(dbset.Writer))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="dbset">SQL链接</param>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet QueryWithWrite(DbSetting dbset, string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(dbset.Writer))
{
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 (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return(ds);
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="dbset">SQL链接</param>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(DbSetting dbset, string SQLString, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(dbset.Writer);
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return(myReader);
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
// finally
// {
// cmd.Dispose();
// connection.Close();
// }
}
public static bool Exists(DbSetting dbset, string strSql)
{
object obj = GetSingle(dbset, strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString()); //也可能=0
}
if (cmdresult == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 表是否存在
/// </summary>
/// <param name="dbset">SQL链接</param>
/// <param name="TableName"></param>
/// <returns></returns>
public static bool TabExists(DbSetting dbset, string TableName)
{
string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
object obj = GetSingle(dbset, strsql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return(false);
}
else
{
return(true);
}
}
public static bool BulkCopy(DbSetting dbset, DataTable sourceTable, string destTable)
{
if (sourceTable == null || string.IsNullOrEmpty(destTable))
{
//throw new ArgumentNullException("sourceTable or destTable");
return(false);
}
else if (sourceTable.Rows.Count == 0)
{
return(true);
}
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(dbset.Writer))
{
sqlBulkCopy.DestinationTableName = destTable;
sqlBulkCopy.BatchSize = sourceTable.Rows.Count;
foreach (DataColumn item in sourceTable.Columns)
{
sqlBulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
sqlBulkCopy.WriteToServer(sourceTable);
}
return(true);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="dbset">SQL链接</param>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static int ExecuteSqlTran(DbSetting dbset, System.Collections.Generic.List <CommandInfo> cmdList)
{
using (SqlConnection conn = new SqlConnection(dbset.Writer))
{
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
{
trans.Rollback();
throw;
}
}
}
}