417 lines
16 KiB
C#
417 lines
16 KiB
C#
using System;
|
||
//using System.Collections.Generic;
|
||
using System.Text;
|
||
using System.Data;
|
||
using System.Data.SqlClient;
|
||
using System.Collections;
|
||
using System.Collections.Specialized;
|
||
using System.Data.OleDb;
|
||
using SystemFramework;
|
||
|
||
//using SystemFramework;
|
||
|
||
namespace DataLinkMesWork
|
||
{
|
||
|
||
/// <summary>
|
||
/// 数据访问类——提供通用的数据访问方法
|
||
/// </summary>
|
||
public partial class SQLCommon
|
||
{
|
||
|
||
/// <summary>
|
||
/// 执行标准SQL查询语句，返回记录集
|
||
/// </summary>
|
||
/// <param name="sql">标准SQL查询语句</param>
|
||
/// <param name="connectionString">连库字符串</param>
|
||
/// <param name="dt">查询结果记录集</param>
|
||
/// <param name="oleDbParameter"></param>
|
||
/// <param name="errorMessage">错误信息</param>
|
||
/// <returns>布尔值，true表示该执行成功，false表示执行失败</returns>
|
||
public static bool ExecuteDataTable_OleDb_Insert(string sql, string connectionString, DataTable dt, ref OleDbParameter[] oleDbParameter, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
try
|
||
{
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
conn.Open();
|
||
using (OleDbDataAdapter dsCommand = new OleDbDataAdapter())
|
||
{
|
||
dsCommand.InsertCommand = new OleDbCommand(sql, conn);
|
||
for (int i = 0; i < oleDbParameter.Length; i++)
|
||
{
|
||
oleDbParameter[i].IsNullable = true;
|
||
dsCommand.InsertCommand.Parameters.Add(oleDbParameter[i]);
|
||
}
|
||
if (dt == null) return false;
|
||
dt.AcceptChanges();
|
||
for (int i = 0; i < dt.Rows.Count; i++)
|
||
{
|
||
dt.Rows[i].SetAdded();
|
||
}
|
||
dsCommand.Update(dt);
|
||
}
|
||
dt.AcceptChanges();
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteDataTable_OleDb\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 执行标准SQL查询语句，返回记录集
|
||
/// </summary>
|
||
/// <param name="sql">标准SQL查询语句</param>
|
||
/// <param name="connectionString">连库字符串</param>
|
||
/// <param name="dt">查询结果记录集</param>
|
||
/// <param name="oleDbParameter">参数</param>
|
||
/// <param name="errorMessage">错误信息</param>
|
||
/// <returns>布尔值，true表示该执行成功，false表示执行失败</returns>
|
||
public static bool ExecuteDataTable_OleDb_Update(string sql, string connectionString, DataTable dt, ref OleDbParameter[] oleDbParameter, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
//dt = new DataTable();
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
conn.Open();
|
||
using (OleDbDataAdapter dsCommand = new OleDbDataAdapter())
|
||
{
|
||
dsCommand.UpdateCommand = new OleDbCommand(sql, conn);
|
||
for (int i = 0; i < oleDbParameter.Length; i++)
|
||
{
|
||
dsCommand.UpdateCommand.Parameters.Add(oleDbParameter[i]);
|
||
}
|
||
if (dt == null) return false;
|
||
dt.AcceptChanges();
|
||
for (int i = 0; i < dt.Rows.Count; i++)
|
||
{
|
||
dt.Rows[i].SetModified();//.SetAdded();
|
||
}
|
||
dsCommand.Update(dt);
|
||
}
|
||
dt.AcceptChanges();
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteDataTable_OleDb\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 用于 Excel Access
|
||
/// 执行标准SQL语句，不要求返回结果，适合（增、删、改）
|
||
/// </summary>
|
||
/// <param name="sql">标准SQL语句</param>
|
||
/// <param name="connectionString">连库字符串</param>
|
||
/// <param name="errorMessage">错误信息</param>
|
||
/// <returns>布尔值，true表示该执行成功，false表示执行失败</returns>
|
||
public static bool ExecuteNonQuery_OleDb(string sql, string connectionString, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
|
||
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
|
||
{
|
||
conn.Open();
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery_OleDb\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn != null)
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 用于 Excel Access
|
||
/// 执行标准SQL语句集合，不要求返回结果，适合（增、删、改）
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="connectionString"></param>
|
||
/// <param name="errorMessage"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteNonQuery_OleDb(StringCollection sql, string connectionString, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
conn.Open();
|
||
for (int i = 0; i < sql.Count; i++)
|
||
{
|
||
using (OleDbCommand cmd = new OleDbCommand(sql[i].ToString(), conn))
|
||
{
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
}
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery_OleDb\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 用于 Excel Access
|
||
/// 执行标准SQL查询语句，返回记录集
|
||
/// </summary>
|
||
/// <param name="sql">标准SQL查询语句</param>
|
||
/// <param name="connectionString">连库字符串</param>
|
||
/// <param name="ds">查询结果记录集</param>
|
||
/// <param name="errorMessage">错误信息</param>
|
||
/// <returns>布尔值，true表示该执行成功，false表示执行失败</returns>
|
||
public static bool ExecuteDataset_OleDb(string sql, string connectionString, out DataSet ds, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
ds = new DataSet();
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
|
||
|
||
try
|
||
{
|
||
using (OleDbDataAdapter dsCommand = new OleDbDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new OleDbCommand(sql, conn);
|
||
conn.Open();
|
||
dsCommand.Fill(ds);
|
||
}
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteDataset_OleDb(返回DataSet)\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 执行标准SQL查询语句，返回记录集
|
||
/// </summary>
|
||
/// <param name="sql">标准SQL查询语句</param>
|
||
/// <param name="connectionString">连库字符串</param>
|
||
/// <param name="dt">查询结果记录集</param>
|
||
/// <param name="errorMessage">错误信息</param>
|
||
/// <returns>布尔值，true表示该执行成功，false表示执行失败</returns>
|
||
public static bool ExecuteDataTable_OleDb(string sql, string connectionString, out DataTable dt, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
dt = new DataTable("TableName");
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
conn.Open();
|
||
using (OleDbDataAdapter dsCommand = new OleDbDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new OleDbCommand(sql, conn);
|
||
dsCommand.Fill(dt);
|
||
}
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteDataTable_OleDb\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn != null)
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 执行标准SQL查询语句，返回记录集
|
||
/// </summary>
|
||
/// <param name="sql">标准SQL查询语句</param>
|
||
/// <param name="connectionString">连库字符串</param>
|
||
/// <param name="ds">查询结果记录集</param>
|
||
/// <param name="errorMessage">错误信息</param>
|
||
/// <returns>布尔值，true表示该执行成功，false表示执行失败</returns>
|
||
public static bool ExecuteDataTable_OleDb(string sql, string connectionString, out DataSet ds, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
ds = new DataSet();
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
try
|
||
{
|
||
conn.Open();
|
||
using (OleDbDataAdapter dsCommand = new OleDbDataAdapter())
|
||
{
|
||
dsCommand.SelectCommand = new OleDbCommand(sql, conn);
|
||
dsCommand.Fill(ds);
|
||
}
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteDataTable_OleDb(返回DataSet)\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 返回符合条件的记录行数
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="connectionString"></param>
|
||
/// <param name="count"></param>
|
||
/// <param name="errorMessage"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteOutNum_OleDb(string sql, string connectionString, out int count, out string errorMessage)
|
||
{
|
||
bool result = false;
|
||
errorMessage = "";
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
count = 0;
|
||
try
|
||
{
|
||
conn.Open();
|
||
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
|
||
{
|
||
object obj = cmd.ExecuteScalar();
|
||
if (obj != null)
|
||
{
|
||
count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
|
||
}
|
||
else
|
||
count = 0;
|
||
}
|
||
result = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteOutNum\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 增加记录，同时返回自动编号
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="connectionString"></param>
|
||
/// <param name="newID"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteNonQuery_newID_OleDb(string sql, string connectionString, out long newID)
|
||
{
|
||
string errorMessage;
|
||
using (OleDbConnection conn = new OleDbConnection(connectionString))
|
||
{
|
||
newID = 0;
|
||
try
|
||
{
|
||
conn.Open();
|
||
using (OleDbTransaction myTransaction = conn.BeginTransaction(IsolationLevel.ReadCommitted))
|
||
{
|
||
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
|
||
{
|
||
myTransaction.Commit();
|
||
cmd.ExecuteNonQuery();
|
||
cmd.CommandText = "select @@identity as id";
|
||
newID = Convert.ToInt64(cmd.ExecuteScalar());
|
||
}
|
||
}
|
||
return true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
errorMessage = e.ToString();
|
||
ApplicationLog.WriteLog(e, "\r\nExecuteOutNum\r\nSQL语句：\r\n" + sql + "\r\n连接字符串：\r\n" + connectionString);
|
||
return false;
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
conn.Close();
|
||
}
|
||
}
|
||
|
||
}
|
||
}
|
||
}
|