612 lines
24 KiB
C#
612 lines
24 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Collections.Specialized;
|
||
using System.Configuration;
|
||
using System.Data;
|
||
using System.Data.SqlClient;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
|
||
namespace DataLinkMesWork
|
||
{
|
||
public class DataAccess
|
||
{
|
||
public static string ConnectionString_MES = ConfigurationManager.AppSettings["ConnectionString_MES"];
|
||
public static bool ExecuteNonQuery(string sql, string connectionString, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
using (SqlCommand cmd = new SqlCommand(sql, conn))
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
//ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL<51><4C>䣺\r\n" + sql + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
|
||
}
|
||
public static bool ExecuteDataset(string sql, string connectionString, out DataSet ds, out string errorMessage)
|
||
{
|
||
errorMessage = "";
|
||
bool result = true;
|
||
errorMessage = "";
|
||
ds = new DataSet();
|
||
|
||
/// SQLSERVER
|
||
/// SQLiteDB
|
||
try
|
||
{
|
||
//bool result = false;
|
||
errorMessage = "";
|
||
ds = new DataSet();
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new SqlCommand(sql, conn);
|
||
dsCommand.Fill(ds);
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
//ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL<51><4C>䣺\r\n" + sql + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
catch (Exception err)
|
||
{
|
||
|
||
}
|
||
|
||
return result;
|
||
|
||
|
||
}
|
||
/// <summary>
|
||
/// 增 删 改
|
||
/// </summary>
|
||
/// <param name="procedureName"></param>
|
||
/// <param name="sqlParameters"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] sqlParameters)
|
||
{
|
||
string connectionString = ConnectionString_MES;
|
||
string errorMessage;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlCommand cmd = new SqlCommand(procedureName, conn))
|
||
{
|
||
cmd.CommandTimeout = 0;
|
||
cmd.CommandType = CommandType.StoredProcedure;
|
||
if (sqlParameters != null)
|
||
{
|
||
for (int i = 0; i < sqlParameters.Length; i++)
|
||
{
|
||
cmd.Parameters.Add(sqlParameters[i]);
|
||
}
|
||
}
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n<>洢<EFBFBD><E6B4A2><EFBFBD>̣<EFBFBD>\r\n" + procedureName + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] sqlParameters,out DataTable dt)
|
||
{
|
||
string connectionString = ConnectionString_MES;
|
||
string errorMessage;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
//ds = new DataSet();
|
||
dt = new DataTable();
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
|
||
dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
|
||
dsCommand.SelectCommand.CommandTimeout = 0;
|
||
if (sqlParameters != null)
|
||
{
|
||
for (int i = 0; i < sqlParameters.Length; i++)
|
||
{
|
||
dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
|
||
}
|
||
}
|
||
|
||
dsCommand.Fill(dt);
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n<>洢<EFBFBD><E6B4A2><EFBFBD>̣<EFBFBD>\r\n" + procedureName + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] sqlParameters, out DataSet ds)
|
||
{
|
||
string connectionString = ConnectionString_MES;
|
||
string errorMessage;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
ds = new DataSet();
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
|
||
dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
|
||
dsCommand.SelectCommand.CommandTimeout = 0;
|
||
if (sqlParameters != null)
|
||
{
|
||
for (int i = 0; i < sqlParameters.Length; i++)
|
||
{
|
||
dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
|
||
}
|
||
}
|
||
|
||
dsCommand.Fill(ds);
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n<>洢<EFBFBD><E6B4A2><EFBFBD>̣<EFBFBD>\r\n" + procedureName + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
public static bool ExecuteDataset(string sql, out DataSet ds)
|
||
{
|
||
string errorMessage;
|
||
string connectionString = ConnectionString_MES;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
ds = new DataSet();
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new SqlCommand(sql, conn);
|
||
dsCommand.Fill(ds);
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL<51><4C>䣺\r\n" + sql + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
public static bool ExecuteDataTable(string sql, out DataTable dt)
|
||
{
|
||
//public static bool ExecuteDataTable(string sql, string connectionString, out DataTable dt, out string errorMessage)
|
||
string errorMessage;
|
||
string connectionString = ConnectionString_MES;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
dt = new DataTable();
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new SqlCommand(sql, conn);
|
||
dsCommand.Fill(dt);
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL<51><4C>䣺\r\n" + sql + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
public static DataTable ExecuteSQL(string sql, ref SqlParameter[] sqlParameters)
|
||
{
|
||
string connectionString = ConnectionString_MES;
|
||
string err;
|
||
bool result = false;
|
||
err = "";
|
||
DataTable dt = new DataTable();
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter(sql, conn))
|
||
{
|
||
if (sqlParameters != null)
|
||
{
|
||
for (int i = 0; i < sqlParameters.Length; i++)
|
||
{
|
||
dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
|
||
}
|
||
}
|
||
dsCommand.Fill(dt);
|
||
}
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
err = e.ToString();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteSelectMesWork\r\n" + sql + "\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return dt;
|
||
}
|
||
|
||
public static bool ExecuteSQL(string sql)
|
||
{
|
||
string connectionString = ConnectionString_MES;
|
||
string errorMessage;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
using (SqlCommand cmd = new SqlCommand(sql, conn))
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL<51><4C>䣺\r\n" + sql + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 提供通用的调用存储过程的方法（增加数据的数据表的到数据库的调用）
|
||
/// </summary>
|
||
/// <param name="procedureName">存储过程名称</param>
|
||
/// <param name="sqlParameters">存储过程参数数组</param>
|
||
/// <param name="dt">增加数据的数据表</param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] sqlParameters, DataTable dt)
|
||
{
|
||
string connectionString = ConnectionString_MES;
|
||
string errorMessage;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
// if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter())
|
||
{
|
||
dsCommand.InsertCommand = new SqlCommand(procedureName, conn);
|
||
dsCommand.InsertCommand.CommandType = CommandType.StoredProcedure;
|
||
for (int i = 0; i < sqlParameters.Length; i++)
|
||
{
|
||
dsCommand.InsertCommand.Parameters.Add(sqlParameters[i]);
|
||
}
|
||
if (dt == null) return false;
|
||
dt.AcceptChanges();
|
||
for (int i = 0; i < dt.Rows.Count; i++)
|
||
{
|
||
dt.Rows[i].SetAdded();
|
||
}
|
||
dsCommand.Update(dt);
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n<>洢<EFBFBD><E6B4A2><EFBFBD>̣<EFBFBD>\r\n" + procedureName + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 提供通用的调用存储过程的方法（增加数据的数据表的到数据库的调用）
|
||
/// </summary>
|
||
/// <param name="procedureName"></param>
|
||
/// <param name="dt"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteStoredProcedure(string procedureName, out DataTable dt)
|
||
{
|
||
string connectionString = ConnectionString_MES;
|
||
string errorMessage;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
dt = new DataTable();
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
|
||
dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
|
||
|
||
dsCommand.Fill(dt);
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n<>洢<EFBFBD><E6B4A2><EFBFBD>̣<EFBFBD>\r\n" + procedureName + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="sqlParameters"></param>
|
||
/// <param name="tb"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteSQL(string sql, ref SqlParameter[] sqlParameters, DataTable dt)
|
||
{
|
||
string connectionString = ConnectionString_MES;
|
||
string errorMessage;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
|
||
|
||
try
|
||
{
|
||
// if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
using (SqlDataAdapter dsCommand = new SqlDataAdapter())
|
||
{
|
||
dsCommand.InsertCommand = new SqlCommand(sql, conn);
|
||
|
||
for (int i = 0; i < sqlParameters.Length; i++)
|
||
{
|
||
sqlParameters[i].IsNullable = true;
|
||
dsCommand.InsertCommand.Parameters.Add(sqlParameters[i]);
|
||
}
|
||
if (dt == null) return false;
|
||
dt.AcceptChanges();
|
||
for (int i = 0; i < dt.Rows.Count; i++)
|
||
{
|
||
dt.Rows[i].SetAdded();
|
||
}
|
||
dsCommand.Update(dt);
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
//ApplicationConfig.ConnectionStringPIS_NEW_Value = "0";
|
||
//TestConnection();
|
||
//errorMessage = e.ToString();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteSQL\r\nSQL<51><4C>䣺\r\n" + sql + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
public static bool ExecuteNonQuery_newID(string sql, out long newID)
|
||
{
|
||
sql += ";select @@identity";
|
||
string connectionString;
|
||
connectionString = ConnectionString_MES;
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
using (SqlCommand cmd = new SqlCommand(sql, conn))
|
||
{
|
||
conn.Open();
|
||
newID = Convert.ToInt64(cmd.ExecuteScalar());
|
||
}
|
||
return true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
newID = 0;
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\n ExecuteNonQuery_newID\r\n<>洢<EFBFBD><E6B4A2><EFBFBD>̣<EFBFBD>\r\n" + sql + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
return false;
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
}
|
||
|
||
public static bool ExecuteOutNum(string sql, out int count)
|
||
{
|
||
string connectionString;
|
||
connectionString = ConnectionString_MES;
|
||
string errorMessage;
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (SqlConnection conn = new SqlConnection(connectionString))
|
||
{
|
||
count = 0;
|
||
try
|
||
{
|
||
using (SqlCommand cmd = new SqlCommand(sql, conn))
|
||
{
|
||
//if (!SqlServerConnectionStatus) return false;
|
||
conn.Open();
|
||
count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
|
||
}
|
||
//SqlServerConnectionStatus = true;
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
//TestConnection();
|
||
////ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL<51><4C>䣺\r\n" + sql + "\r\n<><6E><EFBFBD><EFBFBD><EFBFBD>ַ<EFBFBD><D6B7><EFBFBD><EFBFBD><EFBFBD>\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
}
|
||
|
||
|
||
}
|