表SQL
/****** Object: Table [dbo].[IError] Script Date: 09/05/2012 17:00:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING OFF GO CREATE TABLE [dbo].[IError]( [ErrorModuleID] [varchar](500) NOT NULL, [ErrorClassName] [varchar](50) NULL, [ErrorMethodName] [varchar](50) NULL, [ErrorMessage] [varchar](1000) NOT NULL, [ErrorSource] [varchar](1000) NULL, [ErrorStackTrace] [varchar](1000) NULL, [ErrorTargetSite] [varchar](1000) NULL, [ErrorSQL] [varchar](8000) NULL, [subCode] [varchar](20) NULL, [subName] [varchar](20) NULL, [subMachine] [varchar](20) NULL, [subTime] [datetime] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
保存异常信息到IError存储过程：
/* 功 能 ：保存异常信息到IError 涉 及 表 ：IError */ ALTER PROCEDURE [dbo].[pt_ErrorSaveError] @ErrorModuleID varchar(500), @ErrorClassName varchar(50), @ErrorMethodName varchar(50), @ErrorMessage varchar(1000), @ErrorSource varchar(1000), @ErrorStackTrace varchar(1000), @ErrorTargetsite varchar(1000), @subCode varchar(50), @subName varchar(50), @subMachine varchar(50), @ErrorSQL varchar(4000) AS declare @subTime datetime set @subTime = getdate() insert into IError (ErrorModuleID,ErrorClassName,ErrorMethodName,ErrorMessage,ErrorSource,ErrorStackTrace,ErrorTargetsite,ErrorSQL,subCode,subName,subMachine,subTime) values (@ErrorModuleID,@ErrorClassName,@ErrorMethodName,@ErrorMessage,@ErrorSource,@ErrorStackTrace,@ErrorTargetsite,@ErrorSQL,@subCode,@subName,@subMachine,@subTime)
SaveError 类：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
namespace Common
{
public partial class SaveError
{
private string strConnection = "Data Source=127.0.0.1;Initial Catalog=SysAdmin;Persist Security Info=True;User ID=sa;Password=123"; //连接字符串
private SqlConnection conn; //数据库连接
private SqlCommand comm; //SqlCommand
//打开连接
public bool OpenConn()
{
bool bResult = false;
if (this.conn == null)
{
StringBuilder strBd = new StringBuilder();
strBd.Append("时间： " + System.DateTime.Now.ToString() + System.Environment.NewLine);
strBd.Append("Message: 数据库连接失败，SqlConnection为空" + System.Environment.NewLine);
this.SaveErrorMessageToFile(strBd.ToString());
bResult = false;
}
else
{
if (this.conn.State != System.Data.ConnectionState.Open)
{
if (this.conn.ConnectionString.Length == 0)
{
this.conn.ConnectionString = this.strConnection;
}
try
{
this.conn.Open();
bResult = true;
}
catch (Exception)
{
bResult = false;
}
}
else
{
bResult = true;
}
}
return bResult;
}
//释放关闭连接
public void DisposeConn()
{
if (this.conn != null && this.conn.State == System.Data.ConnectionState.Open)
{
this.conn.Close();
this.conn = null;
}
}
/// <summary>
/// 获取登陆用户信息，保存到错误日志中
/// </summary>
/// <param name="UserCode"></param>
/// <param name="UserName"></param>
/// <returns></returns>
public bool GetUserInfo(out string UserCode, out string UserName)
{
UserCode = "01211231";
UserName = "测试";
return true;
}
#region 保存异常专业存储过程,使用专用方法，防止第归错误，此方法中如果出现错误记录文本文件
public bool SaveErrorMessageToDB(System.Exception e, string ErrorSQL)
{
bool bRelult = false;
using (this.conn = new SqlConnection(this.strConnection))
{
string UserCode;
string UserName;
this.GetUserInfo(out UserCode, out UserName);
System.Diagnostics.StackTrace st = new System.Diagnostics.StackTrace(true);
// int deep = st.FrameCount;
string CallClassName = st.GetFrame(2).GetMethod().ReflectedType.FullName;
string CallMethodName = st.GetFrame(3).GetMethod().Name;
string ProcedureName = "SP_SaveError";
this.comm = new SqlCommand(ProcedureName, this.conn);
this.comm.CommandType = CommandType.StoredProcedure;
this.comm.Parameters.Add("@ErrorModuleID", "IDAL");
this.comm.Parameters.Add("@ErrorClassName", CallClassName);
this.comm.Parameters.Add("@ErrorMethodName", CallMethodName);
this.comm.Parameters.Add("@ErrorMessage", e.Message);
this.comm.Parameters.Add("@ErrorSource", e.Source);
this.comm.Parameters.Add("@ErrorStackTrace", "");//e.StackTrace
this.comm.Parameters.Add("@ErrorSQL", ErrorSQL);
this.comm.Parameters.Add("@ErrorTargetsite", e.TargetSite.ReflectedType.FullName);
this.comm.Parameters.Add("@subCode", UserCode);
this.comm.Parameters.Add("@subName", UserName);
this.comm.Parameters.Add("@subMachine", System.Environment.MachineName);
try
{
if (OpenConn() == false) //打开连接
{
return false;
}
this.comm.ExecuteNonQuery();
this.conn.Close();
bRelult = true;
}
catch (SqlException ex)
{
StringBuilder strBd = new StringBuilder();
strBd.Append("时间： " + System.DateTime.Now.ToString() + System.Environment.NewLine);
strBd.Append("UserCode： " + UserCode + System.Environment.NewLine);
strBd.Append("UserName： " + UserName + System.Environment.NewLine);
strBd.Append("Message: " + ex.Message + System.Environment.NewLine);
strBd.Append("ConnectionString: " + this.conn.ConnectionString + System.Environment.NewLine);
strBd.Append("Source: " + ex.Source + "ErrorSQL:" + ErrorSQL + System.Environment.NewLine);
strBd.Append("Server: " + ex.Server + System.Environment.NewLine);
strBd.Append("Procedure: " + ex.Procedure + System.Environment.NewLine);
strBd.Append("Number: " + ex.Number + System.Environment.NewLine);
strBd.Append("StackTrace: " + ex.StackTrace + System.Environment.NewLine + System.Environment.NewLine);
strBd.Append("TargetSite: " + ex.TargetSite + System.Environment.NewLine + System.Environment.NewLine);
this.SaveErrorMessageToFile(strBd.ToString());
}
catch (SystemException ex)
{
StringBuilder strBd = new StringBuilder();
strBd.Append("时间： " + System.DateTime.Now.ToString() + System.Environment.NewLine);
strBd.Append("UserCode： " + UserCode + System.Environment.NewLine);
strBd.Append("UserName： " + UserName + System.Environment.NewLine);
strBd.Append("Message: " + ex.Message + System.Environment.NewLine);
strBd.Append("ConnectionString: " + this.conn.ConnectionString + System.Environment.NewLine);
strBd.Append("Source: " + ex.Source + "ErrorSQL:" + ErrorSQL + System.Environment.NewLine);
strBd.Append("StackTrace: " + ex.StackTrace + System.Environment.NewLine + System.Environment.NewLine);
strBd.Append("TargetSite: " + ex.TargetSite + System.Environment.NewLine + System.Environment.NewLine);
this.SaveErrorMessageToFile(strBd.ToString());
}
catch (Exception ex)
{
StringBuilder strBd = new StringBuilder();
strBd.Append("时间： " + System.DateTime.Now.ToString() + System.Environment.NewLine);
strBd.Append("UserCode： " + UserCode + System.Environment.NewLine);
strBd.Append("UserName： " + UserName + System.Environment.NewLine);
strBd.Append("Message: " + ex.Message + System.Environment.NewLine);
strBd.Append("ConnectionString: " + this.conn.ConnectionString + System.Environment.NewLine);
strBd.Append("Source: " + ex.Source + "ErrorSQL:" + ErrorSQL + System.Environment.NewLine);
strBd.Append("StackTrace: " + ex.StackTrace + System.Environment.NewLine);
strBd.Append("TargetSite: " + ex.TargetSite + System.Environment.NewLine + System.Environment.NewLine);
this.SaveErrorMessageToFile(strBd.ToString());
}
finally
{
this.DisposeConn(); //释放连接
this.comm.Dispose();
}
}
return bRelult;
}
/// <summary>
/// 如果保存异常时出现了错误，写入错误日志文件
/// </summary>
/// <param name="ErrorMessage"></param>
/// <returns></returns>
public bool SaveErrorMessageToFile(string ErrorMessage)
{
bool bResult = false;
string path = "";//System.Environment.SystemDirectory;
string ServerMapPath = System.Windows.Forms.Application.StartupPath;
if (ServerMapPath != null && ServerMapPath != string.Empty)
{
path = ServerMapPath + @"ErrorDBLog";
}
if (Directory.Exists(path) == false)
{
Directory.CreateDirectory(path);
}
string filePath = path + @"DBError.log";
StreamWriter sw = null;
try
{
sw = new StreamWriter(filePath, true);
sw.WriteLine(ErrorMessage);
bResult = true;
}
catch (Exception ex)
{
string Message = ex.Message;
}
finally
{
if (sw != null)
{
sw.Close();
}
}
return bResult;
}
#endregion
}
}
类调用方法：
try
{
DataSet ds = new DataSet();
string strConn="Data Source=127.0.0.1;Initial Catalog=SysAdmin;Persist Security Info=True;User ID=sa;Password=123";
string strSQL = "selecl SysID,[SysName] from SubSystem where SysState='OK' order by sysid asc";
SqlConnection connection = new SqlConnection(strConn);
comm = new SqlCommand(strSQL, connection);
connection.Open();
object o = comm.ExecuteScalar();
}
catch (Exception e)
{
string ErrorSQL = "SQL语句：" + this.comm.CommandText;
err.SaveErrorMessageToDB(e, ErrorSQL); //保存错误信息
}