C#異常處理表、類、SQL

標籤：

表SQL

/****** Object:  Table [dbo].[IError]    Script Date: 09/05/2012 17:00:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING OFFGOCREATE 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]GOSET ANSI_PADDING OFFGO

  儲存異常資訊到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)ASdeclare @subTime datetimeset @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);//儲存錯誤資訊            }

 

C#異常處理表、類、SQL