//潘化英：写的用来访问指定存储过程，并执行，通过参数获取返回值，不需要返回其他某个类型的
public static void ExecProc_NoReturn(string storedProcName, IDataParameter[] parameters)
{
try
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
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);
}
}
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, storedProcName, "ExecProc_NoReturn_1");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "ExecProc_NoReturn_1", 0);
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
/// //袁依平在用。。。。执行查询sql语句的方法
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, SQLString, "DbHelperSQLQuery");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "DbHelperSQLQuery", 0);
}
finally
{
connection.Close();
}
return(ds);
}
}
///// <summary>
///// 执行SQL语句，返回影响的记录数
///// </summary>
///// <param name="SQLString">SQL语句</param>
///// <returns>影响的记录数</returns>
/////
//public static int ExecuteSql(string SQLString)
//{
// using (SqlConnection connection = new SqlConnection(connectionString))
// {
// using (SqlCommand cmd = new SqlCommand(SQLString, connection))
// {
// try
// {
// connection.Open();
// int rows = cmd.ExecuteNonQuery();
// return rows;
// }
// catch (System.Data.SqlClient.SqlException e)
// {
// connection.Close();
// throw e;
// }
// }
// }
//}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
///小万改造
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (Exception e)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(e, SQLString, "DbHelperExecuteSql");
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "DbHelperExecuteSql", 0);
return(-1);
}
finally
{
connection.Close();
}
}
}
}
//#region 小万： 执行存储过程，返回一个DataSet对象 public DataSet GetDataSet(string procName, params SqlParameter[] parameters)
///// <summary>
///// 执行存储过程，返回一个DataSet 对象
///// </summary>
//public DataSet GetDataSet(string procName, params SqlParameter[] parameters)
//{
// SqlDataAdapter adapter = new SqlDataAdapter(procName, _conn as SqlConnection);
// adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
// foreach (SqlParameter parameter in parameters)
// {
// adapter.SelectCommand.Parameters.Add(parameter);
// }
// DataSet ds = new DataSet();
// adapter.Fill(ds);
// return ds;
//}
//#endregion
//王思远写的方法
public DataSet GetDataSet(List <string> sqls, List <string> Dt_Name)
{
string SqlText = "";
try
{
DataSet ds = new DataSet();
for (int i = 0; i < sqls.Count; i++)
{
SqlText = sqls[i];
SqlDataAdapter da = new SqlDataAdapter(SqlText, _conn);
da.Fill(ds, Dt_Name[i]);
}
return(ds);
}
catch (Exception ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, SqlText, "Conn_GetDataSet");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "Conn_GetDataSet", 0);
return(null);
}
}
/// <summary>
/// 小周用：查询数据得到一个DataTable
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public static DataTable GetDataTable(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
try
{
DataSet ds = new DataSet();
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
}
catch (System.Data.SqlClient.SqlException ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, SQLString, "Db_GetDataTable");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "Db_GetDataTable", 0);
}
return(dt);
}
}
/// <summary>
/// 执行存储过程，返回一个 DataTable 对象
/// </summary>
public DataSet GetDataSet(string procName, params SqlParameter[] parameters)
{
DataSet ds = new DataSet();
try
{
SqlDataAdapter adapter = new SqlDataAdapter(procName, _conn as SqlConnection);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
adapter.SelectCommand.Parameters.Add(parameter);
}
adapter.Fill(ds);
}
catch (Exception ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, procName, "Conn_GetDataSet_2");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "Conn_GetDataSet_2", 0);
ds = null;
}
return(ds);
}
//小英正在用
public DataSet Proc_GetDataSet(List <string> sql, List <string> tablename)//完整sql语句List，对应表名List，这个是传递多个查询语句以对应的表名，访问固定存储过程，返回一个dataset，目的是查询的不需要在语句中open key ,close key 的
{
string sqls = "";
try
{
DataSet ds = new DataSet();
for (int i = 0; i < sql.Count; i++)
{
sqls = sql[i];
SqlDataAdapter da_getall = new SqlDataAdapter("GetQueryData", _conn);
da_getall.SelectCommand.CommandType = CommandType.StoredProcedure;
da_getall.SelectCommand.Parameters.Add("@sqls", SqlDbType.VarChar, -1);
da_getall.SelectCommand.Parameters["@sqls"].Value = sqls;
da_getall.Fill(ds, tablename[i]);
}
return(ds);
}
catch (Exception ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, sqls, "Proc_GetDataSet");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "Proc_GetDataSet", 0);
return(null);
}
}
/// <summary>
/// DataTable转换成xml
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public string DtToXml(DataTable dt)
{
try
{
System.IO.TextWriter tw = new System.IO.StringWriter();
if (dt != null)//2015-12-04加是否为null判断
{
dt.TableName = dt.TableName.Length == 0 ? "Table1" : dt.TableName;
dt.WriteXml(tw);
dt.WriteXmlSchema(tw);
}
return(tw.ToString());
}
catch (Exception ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, "无", "xmlhelper_DtToXml");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "xmlhelper_DtToXml", 0);
//错误信息返回本地
string SendError = Analysis_Error.CatchErrorForClient(ex);
return(SendError);
}
}
//周海梅：找到指定节点的值并返回给调用者
public string GetInnerText(string strXml, string parentNode, string xmlNode) //strXml:xml字符串//parentNode:父节点或者需要查找的大节点//xmlNode:需要找到的节点
{
string nodeText = "";
try
{
XmlDocument xml = new XmlDocument();
xml.LoadXml(strXml);
XmlNode root = xml.SelectSingleNode(parentNode);
XmlNodeList nodelist = root.ChildNodes;
foreach (XmlNode node in nodelist)
{
if (node.Name == xmlNode)
{
XmlElement ele = (XmlElement)node;
nodeText = ele.InnerText;
}
}
}
catch (Exception ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, strXml, "xmlhelper_GetInnerText");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "xmlhelper_GetInnerText", 0);
}
return(nodeText);
}
//袁依平正在用。。。执行sql语句插入、修改、删除的方法
public static int ExecuteSqlTran(List <String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
int count = 0;
string strsql = "";
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return(count);
}
catch (Exception ex)
{
tx.Rollback();
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, strsql, "DbHelperExecuteSqlTran");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "DbHelperExecuteSqlTran", 0);
return(-1);
// throw ex;
}
finally
{
conn.Close();
}
}
}
//小英正在用
public DataTable Proc_GetDataTable(string sql)//这个是传递一个完整的语句，访问固定存储过程，返回一个datatable，目的是查询的不需要在语句中open key ,close key 的
{
try
{
DataTable dt = new DataTable();
SqlDataAdapter da_getall = new SqlDataAdapter("GetQueryData", _conn);
da_getall.SelectCommand.CommandType = CommandType.StoredProcedure;
da_getall.SelectCommand.Parameters.Add("@sqls", SqlDbType.VarChar, -1);
da_getall.SelectCommand.Parameters["@sqls"].Value = sql;
da_getall.Fill(dt);
return(dt);
}
catch (Exception ex)
{
AnalysisError Analysis_Error = new AnalysisError();
string SaveError = Analysis_Error.CatchErrorForSave(ex, sql, "Proc_GetDataTable");
//保存错误信息在Logs中
Deal_error Dealerror = new Deal_error();
Dealerror.deal_Error(SaveError, "OtherError", "Proc_GetDataTable", 0);
return(null);
}
}
