表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); //保存错误信息
}