/// <summary>
/// 执行查询命令
/// </summary>
/// <param name="p_SQL">要执行的sql命令或存储过程名</param>
/// <param name="p_ParaList">参数集(null代表无参数)</param>
/// <returns>填充了数据的DataSet</returns>
/// <remarks></remarks>
/// <param name="p_Type"></param>
public static DataSet FExecQuery(string p_SQL, SqlParameter[] p_ParaList, CommandType p_Type)
{
BIM_DB_Helper._strSQL = p_SQL;
SqlCommand command = BIM_DB_Helper.FCreateCommand(p_SQL, p_ParaList, p_Type);//创建sql命令对象
command.CommandTimeout = 900000000;
System.Diagnostics.Debug.Assert(null != command);
DataSet dsContain = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
System.Diagnostics.Debug.Assert(null != dsContain);
try
{
DateTime dtStart = DateTime.Now;
da.Fill(dsContain);//执行并填充
DateTime dtEnd = DateTime.Now;
TimeSpan ts = dtEnd - dtStart;
return(dsContain);
}
catch (Exception e)
{
// ExceptionManager.Raise(this.GetType(), "$Error_Command_Execute", e);
throw new Exception();
}
finally
{
command.Connection.Close();
}
}
/// <summary>
/// 执行sql
/// </summary>
/// <param name="p_SQL">sql语句</param>
/// <param name="p_ParaList">参数集(null代表无参数)</param>
/// <param name="p_Type">类型</param>
/// <returns>返回影响行数</returns>
public static int FExecNonQuery(string p_SQL, SqlParameter[] p_ParaList, CommandType p_Type)
{
BIM_DB_Helper._strSQL = p_SQL;
SqlCommand command = BIM_DB_Helper.FCreateCommand(p_SQL, p_ParaList, p_Type);//创建sql命令对象
command.CommandTimeout = 13600;
System.Diagnostics.Debug.Assert(null != command);
SqlTransaction transaction;
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
transaction = command.Connection.BeginTransaction("SampleTransaction");
command.Transaction = transaction;
try
{
DateTime dtStart = DateTime.Now;
//log.Info("************StartDateTime:" + dtStart.ToString() + "," + dtStart.Millisecond);
//log.Info(" Parameter SQL:" + command.CommandText);
int i = command.ExecuteNonQuery();//执行命令
//DateTime dtEnd = DateTime.Now;
//TimeSpan ts = dtEnd - dtStart;
//log.Info("************EndDateTime:" + dtEnd.ToString() + "," + dtEnd.Millisecond + "*********"
// + "Cost: " + ts.Seconds + ":" + ts.Milliseconds);
transaction.Commit();
return(i);
}
catch (Exception e)
{
// log.Error(e.Message + " Parameter SQL:" + command.CommandText);
transaction.Rollback();
throw new Exception(e.Message);
//ExceptionManager.Raise(this.GetType(), "$Error_Command_Execute", e);
}
}
finally
{
command.Connection.Close();
}
}
/// <summary>
/// 批量 增删改
/// </summary>
/// <param name="dt"></param>
/// <param name="_SQL">SQL语句</param>
/// <param name="_Paras">参数</param>
/// <returns></returns>
public static int FExecNonQuery(DataTable dt, string _SQL, string _Paras)
{
SqlConnection connection = new SqlConnection(_connectionString);//create connection
SqlCommand command = new SqlCommand(_SQL, connection);
SqlTransaction transaction;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
transaction = connection.BeginTransaction("SampleTransaction");
command.CommandTimeout = 900000000;
command.Connection = connection;
command.Transaction = transaction;
command.CommandType = CommandType.Text;
command.CommandText = _SQL;
try
{
int i = 0;
for (int g = 0; g < dt.Rows.Count; g++)
{
//
object[] paraValue = new object[dt.Columns.Count];
for (int b = 0; b < dt.Columns.Count; b++)
{
paraValue[b] = dt.Rows[g][b].ToString();
}
//
command.Parameters.Clear();
SqlParameter[] p_ParaList = BIM_DB_Helper.FCreateParaList(_Paras, paraValue);
if (null != p_ParaList)//添加参数
{
foreach (SqlParameter para in p_ParaList)
{
if (null != para)
{
command.Parameters.Add(para);
}
}
}
i += command.ExecuteNonQuery();
}
transaction.Commit();
connection.Close();
return(i);
}
catch (Exception ex)
{
// ExceptionManager.Raise("", "$Error_Command_Execute", ex);
transaction.Rollback();
connection.Close();
throw new Exception();
}
finally
{
connection.Close();
}
}