285 lines
8.1 KiB
C#
285 lines
8.1 KiB
C#
using System;
|
||
using System.Data;
|
||
using System.Data.SqlClient;
|
||
using System.IO;
|
||
using System.Threading;
|
||
|
||
|
||
namespace AllBase
|
||
{
|
||
public class SqlHelper
|
||
{
|
||
/// <summary>
|
||
/// 数据库链接串
|
||
/// </summary>
|
||
private string ConnString = "";
|
||
/// <summary>
|
||
/// 是否已经链接成功
|
||
/// </summary>
|
||
public bool Connected {
|
||
get { return Connection != null && Connection.State == ConnectionState.Open; }
|
||
}
|
||
/// <summary>
|
||
/// 数据库链接
|
||
/// </summary>
|
||
private SqlConnection Connection =null;
|
||
/// <summary>
|
||
/// 执行超时
|
||
/// </summary>
|
||
public int TimeOut = 100;
|
||
|
||
public SqlHelper(string conn)
|
||
{
|
||
ConnString = conn;
|
||
ConnTo();
|
||
}
|
||
|
||
public string Replace(string key)
|
||
{
|
||
return key.Replace("'", "''");
|
||
}
|
||
|
||
/// <summary>
|
||
/// 执行指定数据库连接对象的命令
|
||
/// </summary>
|
||
/// <param name="sql">T存储过程名称或T-SQL语句</param>
|
||
/// <returns>返回影响的行数</returns>
|
||
public int ExecuteNonQuery(string sql)
|
||
{
|
||
SqlCommand cmd = PrepareCommand(sql);
|
||
try
|
||
{
|
||
// Finally, execute the command
|
||
int retval = cmd.ExecuteNonQuery();
|
||
return retval;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
ErrorDoing(e.Message,sql);
|
||
|
||
}
|
||
return 0;
|
||
}
|
||
/// <summary>
|
||
/// 获取插入ID
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <returns></returns>
|
||
public int InsertId(string sql)
|
||
{
|
||
sql += ";SELECT @@IDENTITY";
|
||
string id = ExecuteFirst(sql);
|
||
int i = 0;
|
||
int.TryParse(id, out i);
|
||
return i;
|
||
}
|
||
/// <summary>
|
||
/// 是否存在
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <returns></returns>
|
||
public bool Exists(string sql)
|
||
{
|
||
string id = ExecuteFirst(sql);
|
||
return !string.IsNullOrEmpty(id);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
|
||
/// </summary>
|
||
/// <param name="sql">存储过程名或T-SQL语句</param>
|
||
/// <returns>返回一个包含结果集的DataSet</returns>
|
||
public DataSet ExecuteDataset(string sql)
|
||
{
|
||
|
||
SqlCommand cmd = PrepareCommand(sql);
|
||
try
|
||
{
|
||
// 创建SqlDataAdapter和DataSet.
|
||
SqlDataAdapter da = new SqlDataAdapter(cmd);
|
||
|
||
DataSet ds = new DataSet();
|
||
// 填充DataSet.
|
||
da.Fill(ds);
|
||
cmd.Parameters.Clear();
|
||
return ds;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
ErrorDoing(e.Message,sql);
|
||
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
|
||
/// </summary>
|
||
/// <param name="sql">存储过程名或T-SQL语句</param>
|
||
/// <returns>返回一个包含结果集的DataSet</returns>
|
||
public DataTable ExecuteDataTable(string sql)
|
||
{
|
||
|
||
SqlCommand cmd = PrepareCommand(sql);
|
||
try
|
||
{
|
||
// 创建SqlDataAdapter和DataSet.
|
||
SqlDataAdapter da = new SqlDataAdapter(cmd);
|
||
|
||
DataTable ds = new DataTable();
|
||
// 填充DataSet.
|
||
da.Fill(ds);
|
||
cmd.Parameters.Clear();
|
||
return ds;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
ErrorDoing(e.Message,sql);
|
||
|
||
}
|
||
return null;
|
||
|
||
}
|
||
|
||
/// <summary>
|
||
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
|
||
/// </summary>
|
||
/// <param name="sql">存储过程名或T-SQL语句</param>
|
||
/// <returns>返回一个包含结果集的DataSet</returns>
|
||
public DataRow ExecuteDataTableRow(string sql)
|
||
{
|
||
|
||
SqlCommand cmd = PrepareCommand(sql);
|
||
try
|
||
{
|
||
// 创建SqlDataAdapter和DataSet.
|
||
SqlDataAdapter da = new SqlDataAdapter(cmd);
|
||
|
||
DataTable ds = new DataTable();
|
||
// 填充DataSet.
|
||
da.Fill(ds);
|
||
if (ds.Rows.Count > 0)
|
||
return ds.Rows[0];
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
ErrorDoing(e.Message,sql);
|
||
|
||
}
|
||
|
||
return null;
|
||
|
||
}
|
||
|
||
/// <summary>
|
||
/// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
|
||
/// </summary>
|
||
/// <param name="sql">存储过程名称或T-SQL语句</param>
|
||
/// <returns>返回结果集中的第一行第一列</returns>
|
||
public string ExecuteFirst(string sql)
|
||
{
|
||
|
||
SqlCommand cmd = PrepareCommand(sql);
|
||
try
|
||
{
|
||
SqlDataAdapter da = new SqlDataAdapter(cmd);
|
||
DataTable ds = new DataTable();
|
||
// 填充DataSet.
|
||
da.Fill(ds);
|
||
// 执行SqlCommand命令,并返回结果.
|
||
var retval = cmd.ExecuteScalar();
|
||
|
||
return retval==null?"": retval.ToString();
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
ErrorDoing(e.Message,sql);
|
||
|
||
}
|
||
return "";
|
||
}
|
||
|
||
|
||
|
||
/// <summary>
|
||
/// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
|
||
/// </summary>
|
||
/// <param name="commandText">存储过程名或都T-SQL命令文本</param>
|
||
private SqlCommand PrepareCommand(string commandText)
|
||
{
|
||
SqlCommand command = new SqlCommand();
|
||
if (Connection.State != ConnectionState.Open)
|
||
ConnTo();
|
||
|
||
command.Connection = Connection;
|
||
command.CommandText = commandText;
|
||
command.CommandType = CommandType.Text;
|
||
command.CommandTimeout = TimeOut;
|
||
return command;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 针对不同错误，执行不同的修正
|
||
/// </summary>
|
||
/// <param name="message"></param>
|
||
private void ErrorDoing(string message, string sql)
|
||
{
|
||
if (string.IsNullOrEmpty(message))
|
||
return;
|
||
if (message.Contains("Incorrect string value"))
|
||
return;
|
||
if (message.Contains("Connection must be valid and open"))
|
||
ConnTo();
|
||
if (message.Contains("There is already an open DataReader"))
|
||
{
|
||
Thread.Sleep(500);
|
||
ConnTo();
|
||
}
|
||
if (message.Contains("Fatal error encountered during command execution."))
|
||
ConnTo();
|
||
if (message.Contains(" StreamWriter ") || message.Contains("未将对象引用设置到对象的实例") || message.Contains("未将对象"))
|
||
ConnTo();
|
||
LogSave.Add("执行SQL："+sql+" 错误："+message);
|
||
Random ra = new Random(Guid.NewGuid().GetHashCode());
|
||
Thread.Sleep(ra.Next(200, 2000));
|
||
}
|
||
|
||
/// <summary>
|
||
/// 链接数据库
|
||
/// </summary>
|
||
private void ConnTo()
|
||
{
|
||
try
|
||
{
|
||
Connection = new SqlConnection(ConnString);
|
||
Connection.Open();
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
ErrorDoing(e.Message,ConnString);
|
||
}
|
||
|
||
}
|
||
|
||
/// <summary>
|
||
/// 关闭链接
|
||
/// </summary>
|
||
public void Close()
|
||
{
|
||
try
|
||
{
|
||
Connection.Close();
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
|
||
|
||
}
|
||
}
|
||
|
||
|
||
}
|
||
|
||
|
||
}
|