WordPress.com



using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Text;using Microsoft.SqlServer.Server;using System.IO;namespace SQLCLR{ class Data { public string sDebugValue; public string sDBPath; public string sDBName; public string sDBPassword; public string sDBConnectionString; public static string sLogPath; public static string sLogFileName; public static bool bLogWriteToFile; public SqlConnection oSqlConn = null; public SqlCommand oSqlCmd = null; public Data() { // defaults to application path for database if (!bCheckIfRunningFromInsideSql()) { sDBPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase); } // These properties should be set after a new intance of the object is created - SEE ABOVE FOR HOW TO CREATE sDBName = ""; sDBPassword = ""; sDBConnectionString = ""; // Log write defaults sLogPath = @"c:\logs\"; sLogFileName = "Log_SQL.txt"; bLogWriteToFile = false; } private void SetDBConnectionString() { if (!bCheckIfRunningFromInsideSql() && sDBConnectionString.Length == 0) { sDBConnectionString = ConfigurationSettings.AppSettings["ConnectionStringKey"]; } } public bool bCheckIfRunningFromInsideSql() { bool bReturn = false; SqlConnection oSqlConnection = null; try { oSqlConnection = new SqlConnection("context connection=true"); bReturn = true; } catch (Exception ex) { bReturn = false; } return bReturn; } // Connect to DB using connection parametets- aka OPEN public bool SqlOpen() { bool bRetval = false; try { if (oSqlConn == null) { SetDBConnectionString(); if (sDBConnectionString != "") { oSqlConn = new SqlConnection(sDBConnectionString); } else { try { oSqlConn = new SqlConnection("context connection=true"); } catch (SqlException ex) { } } } if (oSqlConn.State != ConnectionState.Open) { oSqlConn.Open(); oSqlCmd = oSqlConn.CreateCommand(); } bRetval = true; } catch (SqlException ex) { WriteLog("[s] SqlOpen: " + ex.Message); } catch (Exception oException) { WriteLog("[e] SqlOpen - " + oException.Message); } return bRetval; } // Disconnect - aka CLOSE public void SqlClose() { try { if (oSqlConn.State != ConnectionState.Closed) oSqlConn.Close(); } catch (SqlException oSqlException) { SqlErrors(oSqlException.Errors); } } public void SqlErrors(SqlErrorCollection errorCollection) { StringBuilder bld = new StringBuilder(); foreach (SqlError err in errorCollection) { bld.Append("\n Error Code: " + err.Number.ToString()); bld.Append("\n Message : " + err.Message); bld.Append("\n Procedure : " + err.Procedure); bld.Append("\n Source : " + err.Source); bld.Append("\n LineNumber: " + err.LineNumber); WriteLog("SqlErrors: " + bld); bld.Remove(0, bld.Length); } } public static void WriteLog(string sData) { if (bLogWriteToFile) { // write this to a text file if (!string.IsNullOrEmpty(sLogFileName)) { StringToFile(sData, sLogPath + sLogFileName); } } } //Utility functions public static bool RenameFile(string sSourceFileName, string sDestinationFileName) { bool bSuccess = true; try { File.Move(sSourceFileName, sDestinationFileName); } catch (Exception ex) { bSuccess = false; WriteLog("[EX] RenameFile - " + ex.Message); } return bSuccess; } public static void StringToFile(string sData, string sFileName) { StringToFile(sData, sFileName, false); } public static void StringToFile(string sData, string sFileName, bool bOverwrite) { try { if (bOverwrite) { StreamWriter sw = File.CreateText(sFileName); sw.WriteLine(sData); sw.Close(); sw.Dispose(); } else { StreamWriter sw = File.AppendText(sFileName); sw.WriteLine(sData); sw.Close(); sw.Dispose(); } } catch (Exception ex) { WriteLog("[EX] StringToFile - " + ex.Message); } } public DataSet GetDataSet(string command) { DataSet oDS = new DataSet(); oDS.EnforceConstraints = false; object oResult = null; if (SqlOpen()) { try { mandType = CommandType.Text; mandText = command; oSqlCmd.Parameters.Clear(); SqlDataAdapter oSQLDataAdapter = new SqlDataAdapter(oSqlCmd); oSQLDataAdapter.Fill(oDS); oResult = oDS; if (oResult != null) { return (DataSet)oResult; } } catch (SqlException oSqlException) { WriteLog("[se] SQLGetDataSet - " + oSqlException.Message); } catch (Exception oException) { WriteLog("[e] SQLGetDataSet - " + oException.Message); } } if (oResult == null) { oResult = oDS; } return (DataSet)oResult; } public bool GetDataSet(ref DataSet oDS, string command) { bool bReturn = false; oDS.EnforceConstraints = false; object oResult = null; if (SqlOpen()) { try { mandType = CommandType.Text; mandText = command; oSqlCmd.Parameters.Clear(); SqlDataAdapter oSQLDataAdapter = new SqlDataAdapter(oSqlCmd); oSQLDataAdapter.Fill(oDS); oResult = oDS; if (oResult != null) { oDS = (DataSet)oResult; bReturn = true; } } catch (SqlException oSqlException) { WriteLog("[se] SQLGetDataSet - " + oSqlException.Message); } catch (Exception oException) { WriteLog("[e] SQLGetDataSet - " + oException.Message); } } if (oResult == null) { bReturn = false; } return bReturn; } public DataSet GetDataSet(string command, CommandType cmdType, params SqlParameter[] dataParams) { DataSet oDS = new DataSet(); oDS.EnforceConstraints = false; object oResult = null; if (SqlOpen()) { try { mandText = command; oSqlCmd.Parameters.Clear(); foreach (SqlParameter oParam in dataParams) { oSqlCmd.Parameters.Add(oParam); } mandType = cmdType; SqlDataAdapter oSQLDataAdapter = new SqlDataAdapter(oSqlCmd); oSQLDataAdapter.Fill(oDS); oResult = oDS; if (oResult != null) { return (DataSet)oResult; } } catch (SqlException oSqlException) { WriteLog("[se] SQLGetDataSet - " + oSqlException.Message); SqlContext.Pipe.Send(oSqlException.Message); } catch (Exception oException) { WriteLog("[e] SQLGetDataSet - " + oException.Message); } } if (oResult == null) { oResult = oDS; } return (DataSet)oResult; } public bool GetDataSet(ref DataSet oDS, string command, CommandType cmdType, params SqlParameter[] dataParams) { bool bReturn = false; oDS.EnforceConstraints = false; object oResult = null; if (SqlOpen()) { try { mandText = command; oSqlCmd.Parameters.Clear(); foreach (SqlParameter oParam in dataParams) { oSqlCmd.Parameters.Add(oParam); } mandType = cmdType; SqlDataAdapter oSQLDataAdapter = new SqlDataAdapter(oSqlCmd); oSQLDataAdapter.Fill(oDS); oResult = oDS; if (oResult != null) { bReturn = true; } } catch (SqlException oSqlException) { WriteLog("[se] SQLGetDataSet - " + oSqlException.Message); } catch (Exception oException) { WriteLog("[e] SQLGetDataSet - " + oException.Message); } } if (oResult == null) { bReturn = false; } return bReturn; } public bool ExecSproc(string command, params SqlParameter[] dataParams) { bool bReturn = false; CommandType cmdType = CommandType.StoredProcedure; if (SqlOpen()) { try { mandText = command; oSqlCmd.Parameters.Clear(); foreach (SqlParameter oParam in dataParams) { oSqlCmd.Parameters.Add(oParam); } mandType = cmdType; oSqlCmd.ExecuteNonQuery(); bReturn = true; } catch (SqlException oSqlException) { WriteLog("[se] SQLExecSproc - " + oSqlException.Message); bReturn = false; } catch (Exception oException) { WriteLog("[e] SQLExecSproc - " + oException.Message); bReturn = false; } } return bReturn; } public bool ExecNonQuery(string command) { bool bReturn = false; CommandType cmdType = CommandType.Text; if (SqlOpen()) { try { mandText = command; mandType = cmdType; oSqlCmd.ExecuteNonQuery(); bReturn = true; } catch (SqlException oSqlException) { WriteLog("[se] SQLExecSproc - " + oSqlException.Message); bReturn = false; } catch (Exception oException) { WriteLog("[e] SQLExecSproc - " + oException.Message); bReturn = false; } } return bReturn; } public SqlParameter CreateParameter(string sParamName, object sValue) { SqlParameter param = new SqlParameter(sParamName, sValue); return param; } public static System.Data.OleDb.OleDbParameter CreateOleDBParameter(string sParamName, object sValue) { System.Data.OleDb.OleDbParameter param = new System.Data.OleDb.OleDbParameter(sParamName, sValue); return param; } public DataSet GetTableSchema(string sTableName) { return this.GetDataSet("Select Top 0 * from " + sTableName); } public string GetDataBaseName() { CommonFunctions oCF = new CommonFunctions(); string sReturn = ""; try { DataSet oDS = this.GetDataSet("SELECT DB_NAME AS cDataBaseName"); if (oCF.DataSetHasData(oDS)) { sReturn = oCF.ConvertToString(oDS.Tables[0].Rows[0]["cDataBaseName"]); } } catch (Exception ex) { sReturn = ex.Message; } return sReturn; } public TResult Exec<TResult>(string cmdText) { TResult oReturn; CommandType cmdType = CommandType.Text; mandText = cmdText; mandType = cmdType; if (SqlOpen()) { oReturn = (TResult)oSqlCmd.ExecuteScalar(); } else { oReturn = (TResult)oSqlCmd.ExecuteScalar(); } return oReturn; } public static TResult Execute<TResult>(string cmdText) { using (SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); using (SqlCommand cmd = new SqlCommand(cmdText, connection)) { return (TResult)cmd.ExecuteScalar(); } } } public static bool ZipFile(string sFileName) { return Execute<bool>(string.Format("SELECT dbo.ZipFile('{0}')", sFileName)); } public static bool UnZipFile(string sFileName) { return Execute<bool>(string.Format("SELECT dbo.UnZipFile('{0}')", sFileName)); } public static void SQLPrint(string sPrintString) { SqlPipe p; p = SqlContext.Pipe; string sPrintPart = ""; while (sPrintString.Length > 0) { if (sPrintString.Length > 4000) { sPrintPart = sPrintString.Substring(0, 4000); sPrintString = sPrintString.Substring(4000); } else { sPrintPart = sPrintString; sPrintString = ""; } p.Send(sPrintPart); } } public void RecordError(string sERR, string sDescription, string sSource, string sStackTrace, string sMethod, string sWebsite, string sSimURowID, string sCurrentTableName, string sSQLCommand) { SqlParameter param1 = CreateParameter("@uERR", sERR); SqlParameter param2 = CreateParameter("@vDescription", sDescription); SqlParameter param3 = CreateParameter("@vSource", sSource); SqlParameter param4 = CreateParameter("@vStackTrace", sStackTrace); SqlParameter param5 = CreateParameter("@vMethod", sMethod); SqlParameter param6 = CreateParameter("@vWebsite", sWebsite); SqlParameter param7 = CreateParameter("@vSimRowID", sSimURowID); SqlParameter param8 = CreateParameter("@vTableName", sCurrentTableName); SqlParameter param9 = CreateParameter("@sql_command", sSQLCommand); ExecSproc("SPA_RecordErrorMessage", param1, param2, param3, param4, param5, param6, param7, param8, param9); } }} ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download