/// <summary>
/// 获得DataSet对象 使用完须关闭DataSet,关闭数据库连接
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns></returns>
public DataSet GetDataSet(string strSql)
{
#region
Open();
cmd = new SqlCommand(strSql, cn);
cmd.CommandTimeout = commandSqlTimeout;//这里修改成无等待时间
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
try
{
///读取数据
da.Fill(dt);
}
catch (Exception ex)
{
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": GetDataTable()+" + strSql + ": \n" + E.Message + "\n", p_file, p_pageInfo);
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行GetDataSet方法[" + strSql + "]语句发生问题：" + ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
return(dt);
#endregion
}
/// <summary>
/// 执行多条SQL语句,类似事务
/// </summary>
/// <param name="SQLStringList"></param>
public void ExecuteSqlTran(string[] SQLStringList)
{
Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
SqlTransaction tx = cn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Length; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行ExecuteSqlTran语句发生问题：" + E.Message);
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
Close();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <param name="dataSet">返回DataSet对象</param>
public void RunProc(string procName, SqlParameter[] prams, ref DataSet dataSet)
{
if (dataSet == null)
{
dataSet = new DataSet();
}
///创建SqlDataAdapter
SqlDataAdapter da = CreateProcDataAdapter(procName, prams);
try
{
///读取数据
da.Fill(dataSet);
}
catch (Exception ex)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunProc方法[" + procName + "]语句发生问题：" + ex.Message);
///记录错误日志
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunProc()+" + procName + ": \n" + ex.Message + "\n", p_file, p_pageInfo);
}
finally
{
///关闭数据库的连接
Close();
}
}
public DataSet RunProcedure_longtime(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection conn = new SqlConnection(SqlConnectionString))
{
conn.Open();
DataSet dataSet = new DataSet();
//if (Connection.State != System.Data.ConnectionState.Open)
//{
// Connection.Open();
//}
//Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);
sqlDA.SelectCommand.CommandTimeout = commandSqlTimeout;//这里修改成无等待时间;
try
{
sqlDA.Fill(dataSet, tableName);
}
catch (Exception ex)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunProcedure方法[" + storedProcName + "]语句发生问题：" + ex.Message);
}
finally
{
sqlDA.SelectCommand.Parameters.Clear();
}
//Connection.Close();
return(dataSet);
}
}
/// <summary>
/// 执行带参数的SQL返回影响的行数
/// </summary>
/// <param name="strSql"></param>
public int RunSqlInt(string strSql, SqlParameter[] parms)
{
#region 执行带参数SQL返回影响的行数
int i = 0;
try
{
Open();
//cmd = new SqlCommand(strSql, cn);
//cmd.CommandTimeout = 0;
cmd = new SqlCommand(strSql, cn);
foreach (SqlParameter p in parms)
{
cmd.Parameters.Add(p);
}
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = commandSqlTimeout;
i = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (Exception E)
{
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunSqlInt()+" + strSql + ": \n" + E.Message + "\n", p_file, p_pageInfo);
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlInt方法[" + strSql + "]语句发生问题：" + E.Message);
}
finally
{
Close();
}
return(i);
#endregion
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回存储过程返回值</returns>
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateProcCommand(procName, prams);
int iRet;
try
{
cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;
cmd.CommandTimeout = commandSqlTimeout;
///执行存储过程
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
///记录错误日志
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunProc()+" + procName + ": \n" + ex.Message + "\n", p_file, p_pageInfo);
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunProc方法[" + procName + "]语句发生问题：" + ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
///返回存储过程的参数值
iRet = (int)cmd.Parameters["@RETURN_VALUE"].Value;
///返回存储过程的参数值
return(iRet);
}
public string GetReturnString(string p_SqlString)
{
try
{
Open();
SqlDataAdapter l_DataAdapter = new SqlDataAdapter(p_SqlString, cn);
System.Data.DataTable l_DataTable = new System.Data.DataTable();
l_DataAdapter.Fill(l_DataTable);
l_DataAdapter.Dispose();
if (cn.State == ConnectionState.Open)
{
Close();
}
if (l_DataTable.Rows.Count > 0)
{
string l_count = l_DataTable.Rows[0].ItemArray.GetValue(0).ToString();
l_DataTable.Dispose();
return(l_count);
}
else
{
return("");
}
}
catch (Exception E)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlReturn方法[" + p_SqlString + "]语句发生问题：" + E.Message);
return("");
}
}
/// <summary>
/// 执行sql 返回 RunSqlReturn_class 对象
/// </summary>
/// <param name="strSql"></param>
public RunSqlReturn_class RunSql_getObjClass(string strSql)
{
#region 执行SQL返回影响的行数
RunSqlReturn_class rc = new RunSqlReturn_class();
try
{
Open();
cmd = new SqlCommand(strSql, cn);
cmd.CommandTimeout = commandSqlTimeout;
rc.iReturn = cmd.ExecuteNonQuery();
}
catch (Exception E)
{
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunSqlInt()+" + strSql + ": \n" + E.Message + "\n", p_file, p_pageInfo);
rc.Message = E.Message;
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSql_getObjClass方法[" + strSql + "]语句发生问题：" + E.Message);
}
finally
{
Close();
}
return(rc);
#endregion
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
#region
try
{
cn = new SqlConnection(SqlConnectionString);
cn.Open();
}
catch (Exception ex)
{
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": Open Connection Fail: \n" + ex.Message + "\n", p_file, p_pageInfo);
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行Open()发生问题：" + ex.Message);
}
#endregion
}
/// <summary>
/// 插入大批量数据的方法
/// </summary>
/// <param name="dt">数据集合</param>
/// <param name="strSqlTablename"></param>
/// <param name="dicList"></param>
public int DataTableToSQLServer(DataTable dt, string strSqlTablename, Dictionary <String, String> dicList)
{
int iReturn = 0;
using (SqlConnection destinationConnection = new SqlConnection(SqlConnectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
try
{
//bulkCopy.DestinationTableName = "T_EIInformation";//要插入的表的表明
//bulkCopy.ColumnMappings.Add("Email", "Email");//映射字段名 DataTable列名 ,数据库 对应的列名
//bulkCopy.ColumnMappings.Add("author", "author");
//bulkCopy.ColumnMappings.Add("Title", "Title");
//bulkCopy.ColumnMappings.Add("Type", "Type");
//bulkCopy.ColumnMappings.Add("confName", "confName");
//bulkCopy.ColumnMappings.Add("Language", "Language");
//bulkCopy.ColumnMappings.Add("Publicationyear", "Publicationyear");
//bulkCopy.ColumnMappings.Add("Conferencelocation", "Conferencelocation");
bulkCopy.DestinationTableName = strSqlTablename;
if (dicList.Count > 0)
{
foreach (var dic in dicList)
{
bulkCopy.ColumnMappings.Add(dic.Key, dic.Value);
}
}
bulkCopy.WriteToServer(dt);
iReturn = dt.Rows.Count;
}
catch (Exception ex)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行DataTableToSQLServer方法[" + strSqlTablename + "]语句发生问题：" + ex.Message);
iReturn = -1;
Console.WriteLine(ex.Message);
}
finally
{
}
}
}
return(iReturn);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <param name="dataSet">返回DataReader对象</param>
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
{
///创建Command
SqlCommand cmd = CreateProcCommand(procName, prams);
cmd.CommandTimeout = commandSqlTimeout;
try
{
///读取数据
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunProc方法[" + procName + "]语句发生问题：" + ex.Message);
dataReader = null;
///记录错误日志
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunProc()+" + procName + ": \n" + ex.Message + "\n", p_file, p_pageInfo);
}
}
/// <summary>
/// 执行SQL语句，并返回第一行第一列结果
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public string RunSqlReturn(string strSql, SqlParameter[] prams)
{
#region
string strReturn = "";
Open();
try
{
cmd = CreateSQLCommand(strSql, prams);
strReturn = cmd.ExecuteScalar().ToString();
}
catch (Exception E)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlReturn方法[" + strSql + "]语句发生问题：" + E.Message);
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunSqlReturn()+" + strSql + ": \n" + E.Message + "\n", p_file, p_pageInfo);
}
return(strReturn);
#endregion
}
/// <summary>
/// HXM 根据P_EntrustMaster中的EMR_ID字段返回与之对应的PMR_PLANID的值
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public string RunSqlPlanID(string strSql)
{
string planID = null;
try
{
Open();
cmd = new SqlCommand(strSql, cn);
planID = cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlPlanID方法[" + strSql + "]语句发生问题：" + ex.Message);
}
finally
{
Close();
}
return(planID);
}
/// <summary>
/// 执行SQL语句，并返回第一行第一列结果
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
public string RunSqlReturn(string strSql)
{
#region
string strReturn = "";
Open();
try
{
cmd = new SqlCommand(strSql, cn);
cmd.CommandTimeout = commandSqlTimeout;
strReturn = cmd.ExecuteScalar().ToString();
}
catch (Exception E)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlReturn方法[" + strSql + "]语句发生问题：" + E.Message);
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunSqlReturn()+" + strSql + ": \n" + E.Message + "\n", p_file, p_pageInfo);
}
Close();
return(strReturn);
#endregion
}
//HXM cmd.ExecuteScalar();返回结果集中的首行首列
public int RunSqlNo(string strSql)
{
int i = 0;
try
{
Open();
cmd = new SqlCommand(strSql, cn);
cmd.CommandTimeout = commandSqlTimeout;
i = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlInt方法[" + strSql + "]语句发生问题：" + ex.Message);
}
finally
{
Close();
}
return(i);
}
/// <summary>
/// 执行SQL不返回任何值
/// </summary>
/// <param name="strSql"></param>
public void RunSqlStr(string strSql)
{
#region 执行SQL不返回任何值
try
{
Open();
cmd = new SqlCommand(strSql, cn);
cmd.CommandTimeout = commandSqlTimeout;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunSqlStr()+" + strSql + ": \n" + E.Message + "\n", p_file, p_pageInfo);
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlStr方法[" + strSql + "]语句发生问题：" + ex.Message);
}
finally
{
Close();
}
#endregion
}
public void myRunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateProcCommand(procName, prams);
try
{
///执行存储过程
///
cmd.CommandTimeout = commandSqlTimeout;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
writeTxtCls wtc = new writeTxtCls();
wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行myRunProc方法[" + procName + "]语句发生问题：" + ex.Message);
///记录错误日志
//WriteLog.SaveTextToFile(DateTime.Now.ToString() + ": RunProc()+" + procName + ": \n" + ex.Message + "\n", p_file, p_pageInfo);
}
finally
{
///关闭数据库的连接
Close();
}
}