ALTER PROCEDURE [dbo].[csp_MakeCount]
(
@TABLENAME VARCHAR(255)= 'xyzzy',
@LANG CHAR(2) = '' -- VB or C# (default set in configration section)
) AS
-- Purpose:
-- Create count script from table definition
-- Copyright (C) 2008 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
-- Notes:
-- 1) Run from database under development
-- csp_MakeDelete 'TABLENAME', 'XX'
-- where XX is VB or C#
-- 2) The TABLENAME is case insensitive, however the table must exist
-- 3) Id and TIMESTAMP columns are specially handled
-- 4) Tables withan identity Id column will have a where clause generated
-- otherwise
-- Changes:
-- 12Mar2008 Clive Original coding
DECLARE @ColName VARCHAR(32)
DECLARE @ColLength INT
DECLARE @HasIdent INT
DECLARE @RowCount INT
DECLARE @RowIndex INT
DECLARE @HasTSTAMP INT
DECLARE @PrintLine VARCHAR(132)
DECLARE @SpName VARCHAR(32)
DECLARE @DateWork VARCHAR(16)
DECLARE @TN VARCHAR(32)
DECLARE @USER VARCHAR(32)
DECLARE @DOM_NAME VARCHAR(50)
DECLARE @SpPrefix VARCHAR(10)
DECLARE @SET_DB VARCHAR(50)
DECLARE @ObPrefix VARCHAR(10)
DECLARE @ALLOWED BIT
DECLARE @DefLanguage CHAR(2)
DECLARE @Separator2 VARCHAR(1)
SELECT @LANG = RTRIM(UPPER(LTRIM(@LANG)))
SELECT @ALLOWED = 0
IF @LANG = 'VB' SELECT @ALLOWED = 1
IF @LANG = 'C#' SELECT @ALLOWED = 1
-- Configuration start
SELECT @SpPrefix = 'usp_' -- Set to desired prefix for stored procedures
SELECT @DOM_NAME = 'DALETH' + '\' -- Set to your domain name
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB = 'asp_dbPermissions'
SELECT @ObPrefix = 'x' -- Prefix for object variables
SELECT @DefLanguage = 'VB' -- Default language C# or VB
SELECT @Separator2 = '' -- Separator between Tablename and action ('_' or '')
-- Configuration end
IF @ALLOWED = 0 SET @LANG = @DefLanguage
IF @TABLENAME ='xyzzy'
OR (NOT EXISTS (SELECT * FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'))
BEGIN
PRINT '--Use csp_MakeCount TABLENAME (XX) to generate update S.P. for TABLENAME'
PRINT '-- and XX is the language for the Wrapper VB or C#'
PRINT '--or csp_MakeCount garbage to get this help text!'
RETURN
END
SELECT @TN = name FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
SELECT @RowCount=0
DECLARE xArgs CURSOR FOR
SELECT C.name FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN
ORDER BY C.colid
OPEN xArgs
FETCH xArgs INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RowCount=@RowCount+1
FETCH xArgs INTO @ColName
END
CLOSE xArgs
DEALLOCATE xArgs
SELECT @SpName=@SpPrefix + RTRIM(@TN) + @Separator2 + 'Count'
SELECT @ColLength= MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(80),C.name))))
FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN AND C.status=128
IF EXISTS(select * from sysobjects where UPPER(name) = UPPER(@SpName))
SELECT @PrintLine='ALTER PROCEDURE dbo.' + @SpName
ELSE
SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName
PRINT @PrintLine
PRINT '('
PRINT ' @Count INT OUTPUT'
PRINT ') AS'
PRINT '-- Purpose:'
SELECT @PrintLine='-- Count records on ' + @TN + ' table'
PRINT @PrintLine
PRINT '-- Parameters:'
PRINT '-- Count - Count of records'
PRINT '-- History: '
SELECT @USER = system_user
IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
END
SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
SELECT @DateWork=substring(@DateWork,1,2)
+ SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
SELECT @PrintLine='-- ' + @DateWork + ' '
SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
PRINT @PrintLine
PRINT 'SET NOCOUNT ON'
DECLARE @SetValue VARCHAR(80)
SELECT @PrintLine='SELECT @COUNT= COUNT(*) FROM ' + @TN
PRINT @PrintLine
PRINT 'RETURN'
PRINT '-------------- this is the end ----------------'
PRINT '/* Data Access code follows:'
IF @LANG = 'VB' BEGIN
PRINT ' #Region " Count "'
PRINT ' '''''' <summary> '
PRINT ' '''''' Count records on ' + @TN + ' table '
PRINT ' '''''' </summary>'
PRINT ' '''''' <remarks>'
PRINT ' '''''' This code was autogenerated on ' + @DateWork
PRINT ' '''''' </remarks>'
PRINT ' Public Function Count' + @TN + '() AS Integer'
PRINT ' Dim iCount As Integer '' set to 0 by runtime '
PRINT ' Dim sConnect As String = CommonData.GetConnect()'
PRINT ' Dim ' + @ObPrefix + 'SqlConnection As SqlConnection = new SqlConnection(sConnect)'
PRINT ' Dim ' + @ObPrefix + 'SqlCommand As SqlCommand = new SqlCommand("'
+ @SpName + '", ' + @ObPrefix + 'SqlConnection)'
PRINT ' Try'
PRINT ' ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure'
PRINT ' ' + @ObPrefix + 'SqlCommand.Parameters.Add("@Count", SqlDbType.Int)'
PRINT ' ' + @ObPrefix + 'SqlCommand.Parameters("@Count").Direction = ParameterDirection.Output'
PRINT ' ' + @ObPrefix + 'SqlCommand.Connection.Open()'
PRINT ' ' + @ObPrefix + 'SqlCommand.ExecuteNonQuery()'
PRINT ' ' + @ObPrefix + 'SqlCommand.Connection.Close()'
PRINT ' iCount = '
+ 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@Count"].Value)'
END
ELSE BEGIN
PRINT ' #region " Count "'
PRINT ' /// <summary> '
PRINT ' /// Count records on ' + @TN + ' table '
PRINT ' /// </summary>'
PRINT ' /// <remarks>'
PRINT ' /// This code was autogenerated on ' + @DateWork
PRINT ' /// </remarks>'
PRINT ' /// <param name="iCount">Count of records</param>'
PRINT ' public int Delete' + @TN + '()'
PRINT ' {'
PRINT ' int iCount; // set to 0 by runtime'
PRINT ' string sConnect = CommonData.GetConnect();'
PRINT ' SqlConnection ' + @ObPrefix + 'SqlConnection = new SqlConnection(sConnect);'
PRINT ' SqlCommand ' + @ObPrefix + 'SqlCommand = new SqlCommand("' + @SpName + '", ' + @ObPrefix + 'SqlConnection);'
PRINT ' try'
PRINT ' {'
PRINT ' ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure;'
PRINT ' ' + @ObPrefix + 'SqlCommand.Parameters.Add("@Count", SqlDbType.Int);'
PRINT ' ' + @ObPrefix + 'SqlCommand.Parameters["@Count"].Direction = ParameterDirection.Output;'
PRINT ' ' + @ObPrefix + 'SqlCommand.Connection.Open();'
PRINT ' ' + @ObPrefix + 'SqlCommand.ExecuteNonQuery();'
PRINT ' ' + @ObPrefix + 'SqlCommand.Connection.Close();'
PRINT ' iCount = '
+ 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@Count"].Value);'
PRINT ' ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT ' ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT ' }'
END
IF @LANG = 'VB' BEGIN
PRINT ' Catch ex As Exception'
PRINT ' Dim sMethod As String = System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString()'
PRINT ' CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "iId="
+ iId.ToString())'
PRINT ' iCount = 0'
PRINT ' Finally '
PRINT ' ' + @ObPrefix + 'SqlCommand.Dispose()'
PRINT ' ' + @ObPrefix + 'SqlConnection.Dispose()'
PRINT ' End Try'
PRINT ' Return iCount'
PRINT ' End Function'
PRINT ' #End Region'
END
ELSE BEGIN
PRINT ' catch (Exception ex)'
PRINT ' {'
PRINT ' string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
PRINT ' CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT ' iCount = 0;'
PRINT ' }'
PRINT ' finally'
PRINT ' {'
PRINT ' ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT ' ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT ' }'
PRINT ' return iCount;'
PRINT ' }'
PRINT ' #endregion'
END
PRINT '*/'
PRINT 'GO'
PRINT @SET_DB
PRINT 'GO'
RETURN
GO
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
For completeness, here is the part for getting the count of records in a table,
csp_MakeListAll 'Details', 'C#'
produced
CREATE PROCEDURE dbo.usp_details_ListAll AS
-- Purpose:
-- Select list of all on details table
-- History:
-- 08Mar2008 ACERXP\Clive Original coding
SET NOCOUNT ON
SELECT
id,
Title,
Name,
Email,
teamemtable_id
FROM details
ORDER BY
id
RETURN
----------------- this is the end --------------
GO
asp_dbPermissions
GO
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
#region " List "
/// <summary>
/// Select list of all on details table
/// </summary>
/// <remarks>
/// This code was autogenerated on 08Mar2008
/// </remarks>
/// <returns>dataset of details </returns>
public DataSet ListdetailsAll()
{
return CommonData.GetDataset("usp_details_ListAll");
}
#endregion
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
CREATE Procedure [dbo].[csp_MakeListAll]
(
@TABLENAME VARCHAR(255)= 'xyzzy',
@LANG VARCHAR(2) = 'VB' -- VB or C# (default set in configration section)
) AS
-- Purpose:
-- Create List script from table definition
-- Copyright (C) 2000, 2003, 2004, 2006, 2007, 2008 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
-- Notes:
-- 1) Run from any database on this server by entering
-- sp_MakeSelect TABLENAME XX
-- where XX is either VB or C#
-- 2) The TABLENAME is case insensitive, however the table must exist
-- 3) Id and TIMESTAMP columns are specially handled
-- 4) Tables with Id column will have a where clause using the
-- Id column - this normally suffices.
-- Changes:
-- 11Nov2007 Clive Convert to SQL2005 Operation (n.b. this cannot reside in Master as a result)
-- 24Jun2006 Clive Make VB/C# switchable
-- 14Oct2000 Clive - Original coding
DECLARE @ColName VARCHAR(32)
DECLARE @ColLength INT
DECLARE @COUNT_ROW INT
DECLARE @HasIdent INT
DECLARE @RowCount INT
DECLARE @RowIndex INT
DECLARE @PrintLine VARCHAR(132)
DECLARE @SpName VARCHAR(32)
DECLARE @SpUpper VARCHAR(32)
DECLARE @DateWork VARCHAR(16)
DECLARE @TN VARCHAR(32)
DECLARE @SetValue VARCHAR(80)
DECLARE @USER VARCHAR(32)
DECLARE @DOM_NAME VARCHAR(50)
DECLARE @SpPrefix VARCHAR(10)
DECLARE @SET_DB VARCHAR(50)
DECLARE @ObPrefix VARCHAR(10)
DECLARE @DefLanguage CHAR(2)
DECLARE @Separator2 VARCHAR(1)
DECLARE @ALLOWED BIT
SELECT @ALLOWED = 0
SELECT @LANG = RTRIM(UPPER(LTRIM(@LANG)))
IF @LANG = 'VB' SELECT @ALLOWED = 1
IF @LANG = 'C#' SELECT @ALLOWED = 1
-- Configuration start
SELECT @SpPrefix = 'usp_' -- Set to desired prefix for stored procedures
SELECT @DOM_NAME = 'DALETH' + '\' -- Set to your domain name
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB = 'asp_dbPermissions'
SELECT @ObPrefix = 'x' -- Prefix for object variables
SELECT @DefLanguage = 'VB' -- Default language C# or VB
SELECT @Separator2 = '_' -- Separator between Tablename and action
-- Configuration end
IF @ALLOWED = 0 SET @LANG = @DefLanguage
IF @TABLENAME ='xyzzy'
GOTO HelpText
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U')
GOTO HelpText
ELSE BEGIN
SELECT @TN = name FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
SELECT @RowCount=0
DECLARE xArgs CURSOR FOR
SELECT C.name FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN
ORDER BY C.colid
OPEN xArgs
FETCH xArgs INTO @ColName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @RowCount=@RowCount+1
FETCH xArgs INTO @ColName
END
CLOSE xArgs
DEALLOCATE xArgs
-- Change next line to change stored procedure name
SELECT @SpName = @SpPrefix + RTRIM(@TN) + '_ListAll'
-- Configuration end
SELECT @ColLength= MAX(DATALENGTH(C.name))
FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN AND C.status<>128
SELECT @SpUpper = UPPER(@SpName)
IF EXISTS(select * from sysobjects
where (UPPER(name) = @SpUpper) AND (sysstat & 0xf = 4))
SELECT @PrintLine='ALTER PROCEDURE dbo.' + @SpName + ' AS'
ELSE
SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName + ' AS'
PRINT @PrintLine
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype
AND U.name<>'timestamp' AND C.status=128
SELECT @HasIdent=COUNT(*) FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN AND C.status=128
DECLARE @ArgName VARCHAR(36),
@Type VARCHAR(32),
@Length VARCHAR(40)
SELECT @RowIndex=0
PRINT '-- Purpose:'
SELECT @PrintLine='-- Select list of all on ' + @TN + ' table'
PRINT @PrintLine
--PRINT '-- Resultset:'
--PRINT '-- '
PRINT '-- History: '
SELECT @USER = system_user
-----
IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
END
SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
SELECT @DateWork=substring(@DateWork,1,2) + SUBSTRING(@DateWork,4,3)
+ SUBSTRING(@DateWork,8,4)
SELECT @PrintLine='-- ' + @DateWork + ' '
SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
PRINT @PrintLine
PRINT 'SET NOCOUNT ON'
SELECT @PrintLine='SELECT '
PRINT @PrintLine
SELECT @RowCount=COUNT(*)
FROM syscolumns C, sysobjects O, systypes U
WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype
AND U.name<>'timestamp' AND C.NAME NOT IN
('ADDDATE','MODDATE','DELDATE','TSTAMP','LAST_PERSON_ID')
DECLARE xArgs CURSOR FOR
SELECT C.name FROM syscolumns C, sysobjects O, systypes U
WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype
AND U.name<>'timestamp' AND C.NAME NOT IN
('ADDDATE','MODDATE','DELDATE','TSTAMP','LAST_PERSON_ID')
ORDER BY C.colid
SELECT @RowIndex=0
OPEN xArgs
FETCH xArgs INTO @ArgName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @RowIndex=@RowIndex + 1
SELECT @ArgName=SUBSTRING(@ArgName
+ ' ',1,@ColLength+2)
SELECT @PrintLine = ' ' + RTRIM(@ArgName)
IF @RowIndex <> @RowCount
SELECT @PrintLine=@PrintLine + ','
PRINT @PrintLine
FETCH xArgs INTO @ArgName
END
CLOSE xArgs
DEALLOCATE xArgs
SELECT @PrintLine=' FROM ' + @TN
PRINT @PrintLine
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN
IF @RowCount = 0 BEGIN
PRINT '-- You MUST amend the following line to correctly identify the row to order by'
PRINT 'ORDER BY SOMECOLUMN'
END
ELSE BEGIN
PRINT 'ORDER BY '
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN
AND C.status=128
DECLARE xArgs CURSOR FOR
SELECT C.name
FROM syscolumns C, sysobjects O, systypes U
WHERE C.id = O.id AND O.name = @TN
AND C.xusertype=U.xusertype AND C.status=128
ORDER BY C.colid
SELECT @RowIndex=0
OPEN xArgs
FETCH xArgs INTO @SetValue
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @RowIndex=@RowIndex + 1
SELECT @PrintLine=' ' + @SetValue
IF @RowIndex <> @RowCount
SELECT @PrintLine=@PrintLine + ' AND'
PRINT @PrintLine
FETCH xArgs INTO @SetValue
END
CLOSE xArgs
DEALLOCATE xArgs
END
END
END
PRINT 'RETURN'
PRINT '----------------- this is the end --------------'
PRINT '/* Data Access code follows:'
IF @LANG = 'VB' BEGIN
PRINT ' #Region " List "'
PRINT ' '''''' <summary> '
PRINT ' '''''' Select list of all on ' + @TN + ' table '
PRINT ' '''''' </summary>'
PRINT ' '''''' <remarks>'
PRINT ' '''''' This code was autogenerated on ' + @DateWork
PRINT ' '''''' </remarks>'
---------
PRINT ' '''''' <returns>dataset of ' + @TN + ' </returns>'
PRINT ' Public Function List' + @TN + 'All() As DataSet '
PRINT ' Return CommonData.GetDataset("' + @SpName + '")'
PRINT ' End Function'
PRINT ' #End Region'
END
ELSE BEGIN
PRINT ' #region " List "'
PRINT ' /// <summary> '
PRINT ' /// Select list of all on ' + @TN + ' table '
PRINT ' /// </summary>'
PRINT ' /// <remarks>'
PRINT ' /// This code was autogenerated on ' + @DateWork
PRINT ' /// </remarks>'
PRINT ' /// <returns>dataset of ' + @TN + ' </returns>'
PRINT ' public DataSet List' + @TN + 'All()'
PRINT ' {'
PRINT ' return CommonData.GetDataset("' + @SpName + '");'
PRINT ' }'
PRINT ' #endregion'
END
PRINT '*/'
PRINT 'GO'
PRINT @SET_DB
PRINT 'GO'
RETURN
HelpText:
PRINT '--Use sp_MakeListAll 'TABLENAME', 'XX' to generate select S.P. for TABLENAME'
PRINT ' and XX is the language for the Wrapper VB or C#'
PRINT '--or sp_MakeListAll garbage to get this help text!'
RETURN
----------------- this is the end --------------
GO
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
#region " Copyleft "
// Copyright (C) 2000, 2003, 2004, 2005, 2007 Clive Chinery
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#endregion
#region " Usings "
using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Text;
using System.Xml;
#endregion
namespace CommonCS
{
/// <summary>
/// Common Data functions
/// </summary>
public sealed partial class CommonData
{
#region " Constants "
/// <summary>
/// With the GetBaseDirectory it should not be neccessary to rename this file as it
/// can be located with application.
/// </summary>
private static string CONFIG_FILE = "CONFIG.XML";
/// <summary>
/// Hold name of module
/// </summary>
private static string MODULE_NAME = "CommonData.cs";
#endregion
#region " Calc Functions "
/// <summary>
/// Calculate Percentage from Decimal Values
/// </summary>
/// <param name="decExpression1">Numerator value</param>
/// <param name="decExpression2">Divisor value</param>
/// <returns></returns>
public static decimal CalcPercent(decimal decExpression1, decimal decExpression2)
{
if (decExpression2 == 0)
return 0;
else
return ((100 * decExpression1) / decExpression2);
}
/// <summary>
/// Calculate Percentage from Integer Values
/// </summary>
/// <param name="iExpression1">Numerator value</param>
/// <param name="iExpression2">Divisor value</param>
/// <returns></returns>
public static int CalcPercent(int iExpression1, int iExpression2)
{
if (iExpression2 == 0)
return 0;
else
return ((100 * iExpression1) / iExpression2);
}
#endregion
#region " Decimal Functions "
/// <summary>
/// Adjust decimal value to a (sanitized) number of decimal places
/// </summary>
/// <param name="decIN">Decimal value</param>
/// <param name="iPlaces">Number of decimal places</param>
/// <returns>Adjusted value</returns>
public static decimal DecimalAdjust(decimal decIN, int iPlaces)
{
if (iPlaces < 0) iPlaces = 0;
if (iPlaces > 28) iPlaces = 28;
return decimal.Round(decIN, iPlaces);
}
#endregion
#region " Filter constants "
private const string FILTER_NUMERIC = "0123456789";
private const string FILTER_DECIMAL = FILTER_NUMERIC + "-.";
private const string FILTER_INTEGER = FILTER_NUMERIC + "-";
private const string FILTER_LOWER = "abcdefghijklmnopqrstuvwxyz";
private const string FILTER_POSTCODE = "ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789";
private const string FILTER_UPPER = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
private const string FILTER_TELEPHONE = FILTER_LOWER + FILTER_INTEGER + " +()";
private const string FILTER_TIME = FILTER_NUMERIC + ":";
private const string FILTER_ALPHA = FILTER_LOWER + FILTER_UPPER;
private const string FILTER_ALPHANUMERIC = FILTER_ALPHA + FILTER_NUMERIC;
private const string FILTER_GENERAL_TEXT = FILTER_ALPHANUMERIC + " :/\\()-=+\n";
#endregion
#region " Filter Functions (see notes with FilterGeneralText) "
#region " FilterAlpha "
/// <summary>
/// Filter Alpha (upper + lower case) Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterAlpha(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_ALPHA, ref bDataDropped);
}
/// <summary>
/// Filter Alpha (upper + lower case) Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterAlpha(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_ALPHA, ref bDataDropped);
}
#endregion
#region " FilterAlphaNumeric "
/// <summary>
/// Filter AlphaNumeric (upper + lower + integer case) Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterAlphaNumeric(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_ALPHANUMERIC, ref bDataDropped);
}
/// <summary>
/// Filter AlphaNumeric (upper + lower + integer case) Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterAlphaNumeric(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_ALPHANUMERIC, ref bDataDropped);
}
#endregion
#region " FilterDecimal "
/// <summary>
/// Filter Decimal Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterDecimal(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_DECIMAL, ref bDataDropped);
}
/// <summary>
/// Filter Decimal Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterDecimal(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_DECIMAL, ref bDataDropped);
}
#endregion
#region " FilterGeneralText (plus notes on usage) "
/// <summary>
/// Filter general text
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
/// <remarks>These filter functions are built upon the premise: "All input is evil".
/// These functions filter input to a range of permitted values controlled by
/// appropriate constants. By defining the allowed values within string constants
/// the allowed values can readily be changed.
/// Input is usually from a textbox but could also be an argument from the query string.
/// When working with non-english languages the General Text will need to be rewritten
/// on an exclude basis.
/// The filter functions filter on a permitted values basis - Thus FilterDecimal
/// would pass the second decimal point in "1.22.2".
/// </remarks>
public static string FilterGeneralText(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_GENERAL_TEXT, ref bDataDropped);
}
/// <summary>
/// Filter general text
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterGeneralText(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_GENERAL_TEXT, ref bDataDropped);
}
#endregion
#region " FilterInteger "
/// <summary>
/// Filter Integer Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterInteger(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_INTEGER, ref bDataDropped);
}
/// <summary>
/// Filter Integer Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterInteger(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_INTEGER, ref bDataDropped);
}
#endregion
#region " FilterLowerCase "
/// <summary>
/// Filter lower case
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterLowerCase(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_LOWER, ref bDataDropped);
}
/// <summary>
/// Filter lower case
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterLowerCase(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_LOWER, ref bDataDropped);
}
#endregion
#region " FilterPostcode "
/// <summary>
/// Filter Postcode (text forced to upper case)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterPostcode(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn.ToUpper(), FILTER_POSTCODE, ref bDataDropped);
}
/// <summary>
/// Filter Postcode (text forced to upper case)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterPostcode(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn.ToUpper(), FILTER_POSTCODE, ref bDataDropped);
}
#endregion
#region " FilterTelephone "
/// <summary>
/// Filter Telephone (text forced tolower case)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterTelephone(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn.ToLower(), FILTER_TELEPHONE, ref bDataDropped);
}
/// <summary>
/// Filter Telephone (text forced tolower case)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterTelephone(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn.ToLower(), FILTER_TELEPHONE, ref bDataDropped);
}
#endregion
#region " FilterTime "
/// <summary>
/// FilterTime (00:00 or 00:00:00 format)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterTime(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_TIME, ref bDataDropped);
}
/// <summary>
/// FilterTime (00:00 or 00:00:00 format)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterTime(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_TIME, ref bDataDropped);
}
#endregion
#region " FilterUpperCase "
/// <summary>
/// Filter upper case
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterUpperCase(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_UPPER, ref bDataDropped);
}
/// <summary>
/// Filter upper case
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterUpperCase(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_UPPER, ref bDataDropped);
}
#endregion
#region " FilterWorker "
/// <summary>
/// Filter Worker
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="sValid">Whitelist of allowed characters</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
private static string FilterWorker(string sIn, string sValid, ref bool bDataDropped)
{
try
{
bDataDropped = false;
if (sIn.Length == 0) return sIn;
StringBuilder sOutput = new StringBuilder(sIn.Length);
for (int iLoop = 0; iLoop < sIn.Length; iLoop++)
{
string sValue = sIn.Substring(iLoop, 1);
if (sValid.IndexOf(sValue) >= 0) // sValue is valid!
sOutput.Append(sValue);
else
bDataDropped = true;
}
return sOutput.ToString();
}
catch (Exception ex)
{ // System.Reflection.MethodInfo.GetCurrentMethod.Name
string sMethod = "FilterWorker";
CommonData.WriteEventError(MODULE_NAME, sMethod, ex, String.Empty);
return sIn;
}
}
#endregion
#endregion
#region " Get Functions "
#region " GetAudit"
/// <summary>
/// Return Audit Level
/// </summary>
/// <returns>0=Audit off, 1=Audit Fail, 2=Audit Fail & Success</returns>
public static int GetAuditLevel()
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
return iAuditLevel;
}
#endregion
#region " GetBaseDirectory"
/// <summary>
/// Get location where dot net started looking for this assembly
/// </summary>
/// <remarks>Brock Allen of DevelopMentor (http://staff.develop.com/ballen) suggested this.
/// <list type="Application type v path returned">
/// <item>For WinForms, Windows Service and DosCommand
/// returns the installation directory with a trailing slash</item>
/// <item>For WebForm and Web Service
/// returns the root of the site (with trailing slash) not the exe location</item>
/// </list>
/// </remarks>
public static string GetBaseDirectory()
{
return AppDomain.CurrentDomain.BaseDirectory;
}
#endregion
#region " GetConfig.."
/// <summary>
/// Get path to Configuration File
/// </summary>
/// <returns>Returns path name to config file</returns>
public static string GetConfigPathName()
{
bool bConfigFileFound = false;
return CommonData.GetConfigPathName(ref bConfigFileFound);
}
/// <summary>
/// Get path to Configuration File
/// </summary>
/// <param name="bConfigFileFound">Set true if configuration file found</param>
/// <returns>Returns path name to config file</returns>
/// <remarks>Locating the configuration file is the trivial task that would be reasonably
/// be expected. Put the config file in the directory that GetBaseDirectory will
/// find it in. It is advisable to make the application startup check that the
/// config file is present by checking that bConfigFileFound is set true.
/// </remarks>
public static string GetConfigPathName(ref bool bConfigFileFound)
{
bConfigFileFound = false;
string sDaseDirectory = CommonData.GetBaseDirectory();
if (!sDaseDirectory.EndsWith("/"))
{
if (!sDaseDirectory.EndsWith("\\")) sDaseDirectory += "\\";
}
sDaseDirectory += CONFIG_FILE;
if (File.Exists(sDaseDirectory))
bConfigFileFound = true;
else
{
// Attempt alternative directory
//sDaseDirectory = this.GetCurrentDirectory;
//if (!sDaseDirectory.EndsWith("\\")) sDaseDirectory += "\\";
//sDaseDirectory += CONFIG_FILE;
if (File.Exists(sDaseDirectory))
bConfigFileFound = true;
else
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog("Application");
xLog.Source = "Application";
xLog.WriteEntry("Could not find " + CONFIG_FILE + " in " + CommonData.GetBaseDirectory()
+ " or " + CommonData.GetCurrentDirectory());
}
}
return sDaseDirectory;
}
private static void GetConfigVariables(out string sEventLogName, out int iAuditLevel, out int iLogLevel, out string sConnect)
{
XmlDocument xXmlDocument = new XmlDocument();
try
{
string sPathName = GetConfigPathName();
if (File.Exists(sPathName))
{
xXmlDocument.Load(sPathName); // Load config file
sConnect = xXmlDocument.SelectSingleNode("/DATALAYER/CONNECT").InnerText;
sEventLogName = xXmlDocument.SelectSingleNode("/DATALAYER/EVENTLOGNAME").InnerText;
iAuditLevel = Convert.ToInt32(xXmlDocument.SelectSingleNode("/DATALAYER/AUDITLEVEL").InnerText);
iLogLevel = Convert.ToInt32(xXmlDocument.SelectSingleNode("/DATALAYER/LOGLEVEL").InnerText);
if (iAuditLevel < 0) iAuditLevel = 0;
if (iAuditLevel > 2) iAuditLevel = 2;
if (iLogLevel < 0) iLogLevel = 0;
if (iLogLevel > 3) iLogLevel = 3;
}
else
{
sConnect = "";
sEventLogName = "";
iAuditLevel = 0;
iLogLevel = 0;
}
}
catch (Exception objError)
{
throw objError;
}
finally
{
xXmlDocument = null;
}
}
#endregion
#region " GetConnect "
/// <summary>
/// Return connect string
/// </summary>
/// <returns>Return connection string from CONFIG.XML</returns>
public static string GetConnect()
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
return sConnect;
}
#endregion
#region " GetCurrentDirectory "
/// <summary>
/// Get current directory
/// </summary>
/// <returns>Returns current directory</returns>
public static string GetCurrentDirectory()
{
return System.Environment.CurrentDirectory.ToString();
}
#endregion
#region " GetDataset "
/// <summary>
/// Run stored procedure with no arguments returning a dataset
/// </summary>
/// <param name="sProcName">Procedure Name</param>
/// <returns>Dataset</returns>
public static DataSet GetDataset(string sProcName)
{
DataSet xDataSet = new DataSet();
string sConnect = GetConnect();
SqlConnection xSqlConnection = new SqlConnection(sConnect);
try
{
SqlCommand xSqlCommand = new SqlCommand(sProcName, xSqlConnection);
xSqlCommand.CommandType = CommandType.StoredProcedure;
xSqlConnection.Open();
SqlDataAdapter xSqlDataAdapter = new SqlDataAdapter(xSqlCommand);
xSqlDataAdapter.Fill(xDataSet);
xSqlDataAdapter.Dispose();
}
catch (Exception ex)
{
string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
WriteEventError(MODULE_NAME, sMethod, ex, "Running:" + sProcName);
}
finally
{
if (xSqlConnection != null) xSqlConnection.Close();
xSqlConnection.Dispose();
}
return xDataSet;
}
#endregion
#region " GetEvent.."
/// <summary>
/// Get Custom Event Log as Dataset
/// </summary>
/// <remarks>Not for users. May be consumed by data grid or web service.</remarks>
/// <returns>Dataset of event log in most recent first sequence.</returns>
public static DataSet GetEventLogDataset
{
get
{
string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
DataSet xDataSet = new DataSet();
try
{
string sEventLogName = CommonData.GetEventLogName();
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
if (xLog.Entries.Count == 0)
CommonData.WriteEventInformation(MODULE_NAME, sMethod, "Dummy entry");
DataTable xDataTable = new DataTable();
xDataTable.Columns.Add("EntryType");
xDataTable.Columns.Add("TimeGenerated");
xDataTable.Columns.Add("Message");
xDataTable.Columns.Add("Category");
for (int iLoop = xLog.Entries.Count - 1; iLoop >= 0; iLoop--)
{
string[] sRow = new string[]
{
xLog.Entries[iLoop].EntryType.ToString(),
xLog.Entries[iLoop].TimeGenerated.ToString(),
xLog.Entries[iLoop].Message,
xLog.Entries[iLoop].Category
};
xDataTable.Rows.Add(sRow);
}
xDataSet.Tables.Add(xDataTable);
}
catch (Exception ex)
{
CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");
}
return xDataSet;
}
}
/// <summary>
/// Get Event Log Name from CONFIG file
/// </summary>
/// <returns>Return name of custom event log</returns>
public static string GetEventLogName()
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
return sEventLogName;
}
#endregion
#region " GetLogLevel "
/// <summary>
/// Returns Log Level
/// </summary>
/// <returns>0=Log off, 1=Errors only, 2=Errors+Warnings, 3=Errors+Warnings+Information</returns>
public static int GetLogLevel()
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
return iLogLevel;
}
#endregion
#region " GetMachineName "
/// <summary>
/// Get Machine Name
/// </summary>
/// <returns>Returns machine name</returns>
public static string GetMachineName()
{
return System.Environment.MachineName.ToString();
}
#endregion
#region " GetOs..."
/// <summary>
/// Get OS Platform
/// </summary>
/// <returns></returns>
public static string GetOsVersionPlatform()
{
return System.Environment.OSVersion.Platform.ToString();
}
/// <summary>
/// Get OS Version
/// </summary>
/// <returns></returns>
public static string GetOsVersionVersion()
{
return System.Environment.OSVersion.Version.ToString();
}
#endregion
#region " GetStackTrace "
/// <summary>
/// Get stack of current calls
/// </summary>
/// <returns></returns>
public static string GetStackTrace()
{
return System.Environment.StackTrace.ToString();
}
#endregion
#region " GetSystemDirectory "
/// <summary>
/// Get Location of System Directory
/// </summary>
/// <returns></returns>
public static string GetSystemDirectory()
{
return System.Environment.SystemDirectory.ToString();
}
#endregion
#region " GetUser..."
/// <summary>
/// Get domain user is logged into
/// </summary>
/// <returns></returns>
public static string GetUserDomainName()
{
return System.Environment.UserDomainName.ToString();
}
/// <summary>
/// Get logged-in user name
/// </summary>
/// <returns></returns>
public static string GetUserName()
{
return System.Environment.UserName.ToString();
}
#endregion
#region " GetVersion..."
/// <summary>
/// Get version build number
/// </summary>
/// <returns></returns>
public static string GetVersionBuild()
{
return System.Environment.Version.Build.ToString();
}
/// <summary>
/// Get Major version number
/// </summary>
/// <returns></returns>
public static string GetVersionMajor()
{
return System.Environment.Version.Major.ToString();
}
/// <summary>
/// Get minor version number
/// </summary>
/// <returns></returns>
public static string GetVersionMinor()
{
return System.Environment.Version.Minor.ToString();
}
/// <summary>
/// Get Revision number
/// </summary>
/// <returns></returns>
public static string GetVersionRevision()
{
return System.Environment.Version.Revision.ToString();
}
#endregion
#endregion
#region " Is Functions "
/// <summary>
/// Test if supplied string is blank
/// </summary>
/// <param name="sTest">String to test</param>
/// <returns>True if input string is blank</returns>
public static bool IsBlank(string sTest)
{
return (sTest.Trim().Length == 0);
}
/// <summary>
/// Test if supplied string is not blank
/// </summary>
/// <param name="sTest">String to test</param>
/// <returns>True if input string is not blank</returns>
public static bool IsNotBlank(string sTest)
{
return (sTest.Trim().Length != 0);
}
/// <summary>
/// Test if input string is numeric - NOT WORKING
/// </summary>
/// <param name="sTest"></param>
/// <returns>True if string is numeric</returns>
private static bool IsNumeric(string sTest)
{
System.Text.RegularExpressions.Regex xRegEx
= new System.Text.RegularExpressions.Regex("^\\d*\\.{0,1}\\d+$");
return xRegEx.IsMatch(sTest);
}
#endregion
#region " Null Functions "
/// <summary>
/// Convert nullable value to boolean
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static bool NullToBoolean(object vValue)
{
try
{
if (vValue != null)
return System.Convert.ToBoolean(vValue);
else
return false;
}
catch { return false; }
}
/// <summary>
/// Convert nullable value to boolean
/// </summary>
/// <param name="vValue"></param>
/// <param name="bDefault"></param>
/// <returns></returns>
public static bool NullToBoolean(object vValue, bool bDefault)
{
try
{
if (vValue != null)
return System.Convert.ToBoolean(vValue);
else
return false;
}
catch { return bDefault; }
}
/// <summary>
/// Convert nullable value to date string
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static string NullToDateString(object vValue)
{
try
{
if (vValue != null)
{
System.DateTime dValue = System.Convert.ToDateTime(vValue);
return dValue.ToString("dd/MM/yyyy HH:mm", DateTimeFormatInfo.InvariantInfo);
}
else
return "";
}
catch { return ""; }
}
/// <summary>
/// Convert nullable value to date string with sepecified format
/// </summary>
/// <param name="vValue"></param>
/// <param name="sFormat"></param>
/// <returns></returns>
public static string NullToDateString(object vValue, string sFormat)
{
try
{
if (vValue != null)
{
System.DateTime dValue = System.Convert.ToDateTime(vValue);
return dValue.ToString(sFormat, DateTimeFormatInfo.InvariantInfo);
}
else
return "";
}
catch { return ""; }
}
/// <summary>
/// Convert nullable value to decimal value
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static decimal NullToDecimal(object vValue)
{
try
{
if (vValue != null)
return System.Convert.ToDecimal(vValue);
else
return 0;
}
catch { return 0; }
}
/// <summary>
/// Convert nullable value to Integer
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static int NullToInteger(object vValue)
{
try
{
if (vValue != null)
return System.Convert.ToInt32(vValue);
else
return 0;
}
catch { return 0; }
}
/// <summary>
/// Convert nullable value to string
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static string NullToString(object vValue)
{
try
{
if (vValue != null)
return System.Convert.ToString(vValue);
else
return "";
}
catch { return ""; }
}
#endregion
#region " WriteEvent "
#region " WriteEventError "
/// <summary>
/// Write error flagged event log entry
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="xError">From catch (Exception ex)</param>
public static void WriteEventError(string sPage, string sMethod, Exception xError)
{
string sNote = "";
CommonData.WriteEventError(sPage, sMethod, xError, sNote);
}
/// <summary>
/// Write error flagged event log entry
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="xError">From catch (Exception ex)</param>
/// <param name="sNote">Optional note</param>
public static void WriteEventError(string sPage, string sMethod, Exception xError, string sNote)
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
string sMessage = "";
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
if (sMessage.Length > 0)
sMessage += "\n" + xError.Message;
else
sMessage = xError.Message;
while (xError.InnerException != null)
{
xError = xError.InnerException;
sMessage += "\n" + xError.Message;
}
sMessage += "\n" + xError.StackTrace;
sMessage += "\n" + xError.Source;
if (sNote.Length > 0) sMessage += "\n" + sNote;
if (iLogLevel > 0)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.Error);
}
catch { }
}
}
}
#endregion
#region " WriteEventFailureAudit "
/// <summary>
/// Write Event Log Audit Failure Message
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="sNote">Message to write to event log</param>
public static void WriteEventFailureAudit(string sPage, string sMethod, string sNote)
{
string sMessage = sNote;
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
if (iAuditLevel > 1)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.FailureAudit);
}
catch { }
}
}
}
#endregion
#region " WriteEventInformation "
/// <summary>
/// Write Event Log Information Message
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="sNote">Message to write to event log</param>
public static void WriteEventInformation(string sPage, string sMethod, string sNote)
{
string sMessage = sNote;
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
if (iLogLevel == 3)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.Information);
}
catch { }
}
}
}
#endregion
#region " WriteEventSuccessAudit "
/// <summary>
/// Write Event Log Audit Success Message
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="sNote">Message to write to event log</param>
public static void WriteEventSuccessAudit(string sPage, string sMethod, string sNote)
{
string sMessage = sNote;
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
if (iAuditLevel == 2)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.SuccessAudit);
}
catch { }
}
}
}
#endregion
#region " WriteEventWarning "
/// <summary>
/// Write Event Log Event Warning Message
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="sNote">Message to write to event log</param>
public static void WriteEventWarning(string sPage, string sMethod, string sNote)
{
string sMessage = sNote;
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
if (iLogLevel > 1)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.Warning);
}
catch { }
}
}
}
#endregion
#endregion
}
}
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
Well that finishes the promised stored procedures and wrapper code! The advantage of the TSQL generation scripts is that they can be modified to suit local requirements. For completeness, I do need to update and post here the VB.NET version of the support
library, once I have updated it.
There are some NUNIT tests for the library at http://forums.asp.net/p/1207886/2120356.aspx#2120356
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
You will need to put the code into a separate class (I started out posting a complete class) - this class could be part of your regular web site (or web service) or be a separate class project. The latter has the advantage that it can be tested by an NUnit
class project. The disadvantage of class project is that you either hard code the connection string (not good), pass in the connection string from the UI (messy) or allow the data layer to read its own configuration file (I use CONFIG.XML) via the common data
library. If you put the CommonData class library within your UI, you can modify the GetConnect to read read from the config file.
In my opinion a separate data layer is the better approach.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
>But I'm a bit lost how to include each code within the earlier code
Just paste from the #Region to the #End Region after the #Region to the #End Region in the class. It was necessary to do it this way, otherwise the chunks would get too big.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
A further note - the generator routines contain a section like:
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB = 'asp_dbPermissions'
SELECT @ObPrefix = 'x' -- Prefix for object variables
SELECT @DefLanguage = 'VB' -- Default language C# or VB
SELECT @Separator2 = '' -- Separator between Tablename and action
-- Configuration end
Change @SET_DB to your routine for setting stored procedure permissions or blank it out if wish to do it your self. A small system can easily have hundreds of stored procedures or more, setting them by hand can take hours, but a permissions routine can
do it very reliably (why send a human to do a machines job!)
Change obPrefix if you prefer another prefix to x for object variables
Change DefLanguge to C# if you want the wrapper code to default to C#
Put a value in separator2 if you TableYOURVALUEInsert to TableInsert
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 08, 2008 02:20 PM|LINK
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[csp_MakeCount]
(
@TABLENAME VARCHAR(255)= 'xyzzy',
@LANG CHAR(2) = '' -- VB or C# (default set in configration section)
) AS
-- Purpose:
-- Create count script from table definition
-- Copyright (C) 2008 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
-- Notes:
-- 1) Run from database under development
-- csp_MakeDelete 'TABLENAME', 'XX'
-- where XX is VB or C#
-- 2) The TABLENAME is case insensitive, however the table must exist
-- 3) Id and TIMESTAMP columns are specially handled
-- 4) Tables withan identity Id column will have a where clause generated
-- otherwise
-- Changes:
-- 12Mar2008 Clive Original coding
DECLARE @ColName VARCHAR(32)
DECLARE @ColLength INT
DECLARE @HasIdent INT
DECLARE @RowCount INT
DECLARE @RowIndex INT
DECLARE @HasTSTAMP INT
DECLARE @PrintLine VARCHAR(132)
DECLARE @SpName VARCHAR(32)
DECLARE @DateWork VARCHAR(16)
DECLARE @TN VARCHAR(32)
DECLARE @USER VARCHAR(32)
DECLARE @DOM_NAME VARCHAR(50)
DECLARE @SpPrefix VARCHAR(10)
DECLARE @SET_DB VARCHAR(50)
DECLARE @ObPrefix VARCHAR(10)
DECLARE @ALLOWED BIT
DECLARE @DefLanguage CHAR(2)
DECLARE @Separator2 VARCHAR(1)
SELECT @LANG = RTRIM(UPPER(LTRIM(@LANG)))
SELECT @ALLOWED = 0
IF @LANG = 'VB' SELECT @ALLOWED = 1
IF @LANG = 'C#' SELECT @ALLOWED = 1
-- Configuration start
SELECT @SpPrefix = 'usp_' -- Set to desired prefix for stored procedures
SELECT @DOM_NAME = 'DALETH' + '\' -- Set to your domain name
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB = 'asp_dbPermissions'
SELECT @ObPrefix = 'x' -- Prefix for object variables
SELECT @DefLanguage = 'VB' -- Default language C# or VB
SELECT @Separator2 = '' -- Separator between Tablename and action ('_' or '')
-- Configuration end
IF @ALLOWED = 0 SET @LANG = @DefLanguage
IF @TABLENAME ='xyzzy'
OR (NOT EXISTS (SELECT * FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'))
BEGIN
PRINT '--Use csp_MakeCount TABLENAME (XX) to generate update S.P. for TABLENAME'
PRINT '-- and XX is the language for the Wrapper VB or C#'
PRINT '--or csp_MakeCount garbage to get this help text!'
RETURN
END
SELECT @TN = name FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
SELECT @RowCount=0
DECLARE xArgs CURSOR FOR
SELECT C.name FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN
ORDER BY C.colid
OPEN xArgs
FETCH xArgs INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RowCount=@RowCount+1
FETCH xArgs INTO @ColName
END
CLOSE xArgs
DEALLOCATE xArgs
SELECT @SpName=@SpPrefix + RTRIM(@TN) + @Separator2 + 'Count'
SELECT @ColLength= MAX(DATALENGTH(RTRIM(CONVERT(VARCHAR(80),C.name))))
FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN AND C.status=128
IF EXISTS(select * from sysobjects where UPPER(name) = UPPER(@SpName))
SELECT @PrintLine='ALTER PROCEDURE dbo.' + @SpName
ELSE
SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName
PRINT @PrintLine
PRINT '('
PRINT ' @Count INT OUTPUT'
PRINT ') AS'
PRINT '-- Purpose:'
SELECT @PrintLine='-- Count records on ' + @TN + ' table'
PRINT @PrintLine
PRINT '-- Parameters:'
PRINT '-- Count - Count of records'
PRINT '-- History: '
SELECT @USER = system_user
IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
END
SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
SELECT @DateWork=substring(@DateWork,1,2)
+ SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
SELECT @PrintLine='-- ' + @DateWork + ' '
SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
PRINT @PrintLine
PRINT 'SET NOCOUNT ON'
DECLARE @SetValue VARCHAR(80)
SELECT @PrintLine='SELECT @COUNT= COUNT(*) FROM ' + @TN
PRINT @PrintLine
PRINT 'RETURN'
PRINT '-------------- this is the end ----------------'
PRINT '/* Data Access code follows:'
IF @LANG = 'VB' BEGIN
PRINT ' #Region " Count "'
PRINT ' '''''' <summary> '
PRINT ' '''''' Count records on ' + @TN + ' table '
PRINT ' '''''' </summary>'
PRINT ' '''''' <remarks>'
PRINT ' '''''' This code was autogenerated on ' + @DateWork
PRINT ' '''''' </remarks>'
PRINT ' Public Function Count' + @TN + '() AS Integer'
PRINT ' Dim iCount As Integer '' set to 0 by runtime '
PRINT ' Dim sConnect As String = CommonData.GetConnect()'
PRINT ' Dim ' + @ObPrefix + 'SqlConnection As SqlConnection = new SqlConnection(sConnect)'
PRINT ' Dim ' + @ObPrefix + 'SqlCommand As SqlCommand = new SqlCommand("'
+ @SpName + '", ' + @ObPrefix + 'SqlConnection)'
PRINT ' Try'
PRINT ' ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure'
PRINT ' ' + @ObPrefix + 'SqlCommand.Parameters.Add("@Count", SqlDbType.Int)'
PRINT ' ' + @ObPrefix + 'SqlCommand.Parameters("@Count").Direction = ParameterDirection.Output'
PRINT ' ' + @ObPrefix + 'SqlCommand.Connection.Open()'
PRINT ' ' + @ObPrefix + 'SqlCommand.ExecuteNonQuery()'
PRINT ' ' + @ObPrefix + 'SqlCommand.Connection.Close()'
PRINT ' iCount = '
+ 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@Count"].Value)'
END
ELSE BEGIN
PRINT ' #region " Count "'
PRINT ' /// <summary> '
PRINT ' /// Count records on ' + @TN + ' table '
PRINT ' /// </summary>'
PRINT ' /// <remarks>'
PRINT ' /// This code was autogenerated on ' + @DateWork
PRINT ' /// </remarks>'
PRINT ' /// <param name="iCount">Count of records</param>'
PRINT ' public int Delete' + @TN + '()'
PRINT ' {'
PRINT ' int iCount; // set to 0 by runtime'
PRINT ' string sConnect = CommonData.GetConnect();'
PRINT ' SqlConnection ' + @ObPrefix + 'SqlConnection = new SqlConnection(sConnect);'
PRINT ' SqlCommand ' + @ObPrefix + 'SqlCommand = new SqlCommand("' + @SpName + '", ' + @ObPrefix + 'SqlConnection);'
PRINT ' try'
PRINT ' {'
PRINT ' ' + @ObPrefix + 'SqlCommand.CommandType = CommandType.StoredProcedure;'
PRINT ' ' + @ObPrefix + 'SqlCommand.Parameters.Add("@Count", SqlDbType.Int);'
PRINT ' ' + @ObPrefix + 'SqlCommand.Parameters["@Count"].Direction = ParameterDirection.Output;'
PRINT ' ' + @ObPrefix + 'SqlCommand.Connection.Open();'
PRINT ' ' + @ObPrefix + 'SqlCommand.ExecuteNonQuery();'
PRINT ' ' + @ObPrefix + 'SqlCommand.Connection.Close();'
PRINT ' iCount = '
+ 'CommonData.NullToInteger(' + @ObPrefix + 'SqlCommand.Parameters["@Count"].Value);'
PRINT ' ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT ' ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT ' }'
END
IF @LANG = 'VB' BEGIN
PRINT ' Catch ex As Exception'
PRINT ' Dim sMethod As String = System.Reflection.MethodInfo.GetCurrentMethod().Name.ToString()'
PRINT ' CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "iId="
+ iId.ToString())'
PRINT ' iCount = 0'
PRINT ' Finally '
PRINT ' ' + @ObPrefix + 'SqlCommand.Dispose()'
PRINT ' ' + @ObPrefix + 'SqlConnection.Dispose()'
PRINT ' End Try'
PRINT ' Return iCount'
PRINT ' End Function'
PRINT ' #End Region'
END
ELSE BEGIN
PRINT ' catch (Exception ex)'
PRINT ' {'
PRINT ' string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;'
PRINT ' CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");'
PRINT ' iCount = 0;'
PRINT ' }'
PRINT ' finally'
PRINT ' {'
PRINT ' ' + @ObPrefix + 'SqlCommand.Dispose();'
PRINT ' ' + @ObPrefix + 'SqlConnection.Dispose();'
PRINT ' }'
PRINT ' return iCount;'
PRINT ' }'
PRINT ' #endregion'
END
PRINT '*/'
PRINT 'GO'
PRINT @SET_DB
PRINT 'GO'
RETURN
GO
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 08, 2008 02:51 PM|LINK
For completeness, here is the part for getting the count of records in a table,
csp_MakeListAll 'Details', 'C#'
produced
CREATE PROCEDURE dbo.usp_details_ListAll AS
-- Purpose:
-- Select list of all on details table
-- History:
-- 08Mar2008 ACERXP\Clive Original coding
SET NOCOUNT ON
SELECT
id,
Title,
Name,
Email,
teamemtable_id
FROM details
ORDER BY
id
RETURN
----------------- this is the end --------------
GO
asp_dbPermissions
GO
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 08, 2008 02:52 PM|LINK
The wrapper code was:
#region " List "
/// <summary>
/// Select list of all on details table
/// </summary>
/// <remarks>
/// This code was autogenerated on 08Mar2008
/// </remarks>
/// <returns>dataset of details </returns>
public DataSet ListdetailsAll()
{
return CommonData.GetDataset("usp_details_ListAll");
}
#endregion
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 08, 2008 02:54 PM|LINK
The script that produced it was:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE Procedure [dbo].[csp_MakeListAll]
(
@TABLENAME VARCHAR(255)= 'xyzzy',
@LANG VARCHAR(2) = 'VB' -- VB or C# (default set in configration section)
) AS
-- Purpose:
-- Create List script from table definition
-- Copyright (C) 2000, 2003, 2004, 2006, 2007, 2008 Clive Chinery
--
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
--
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
--
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
-- Notes:
-- 1) Run from any database on this server by entering
-- sp_MakeSelect TABLENAME XX
-- where XX is either VB or C#
-- 2) The TABLENAME is case insensitive, however the table must exist
-- 3) Id and TIMESTAMP columns are specially handled
-- 4) Tables with Id column will have a where clause using the
-- Id column - this normally suffices.
-- Changes:
-- 11Nov2007 Clive Convert to SQL2005 Operation (n.b. this cannot reside in Master as a result)
-- 24Jun2006 Clive Make VB/C# switchable
-- 14Oct2000 Clive - Original coding
DECLARE @ColName VARCHAR(32)
DECLARE @ColLength INT
DECLARE @COUNT_ROW INT
DECLARE @HasIdent INT
DECLARE @RowCount INT
DECLARE @RowIndex INT
DECLARE @PrintLine VARCHAR(132)
DECLARE @SpName VARCHAR(32)
DECLARE @SpUpper VARCHAR(32)
DECLARE @DateWork VARCHAR(16)
DECLARE @TN VARCHAR(32)
DECLARE @SetValue VARCHAR(80)
DECLARE @USER VARCHAR(32)
DECLARE @DOM_NAME VARCHAR(50)
DECLARE @SpPrefix VARCHAR(10)
DECLARE @SET_DB VARCHAR(50)
DECLARE @ObPrefix VARCHAR(10)
DECLARE @DefLanguage CHAR(2)
DECLARE @Separator2 VARCHAR(1)
DECLARE @ALLOWED BIT
SELECT @ALLOWED = 0
SELECT @LANG = RTRIM(UPPER(LTRIM(@LANG)))
IF @LANG = 'VB' SELECT @ALLOWED = 1
IF @LANG = 'C#' SELECT @ALLOWED = 1
-- Configuration start
SELECT @SpPrefix = 'usp_' -- Set to desired prefix for stored procedures
SELECT @DOM_NAME = 'DALETH' + '\' -- Set to your domain name
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB = 'asp_dbPermissions'
SELECT @ObPrefix = 'x' -- Prefix for object variables
SELECT @DefLanguage = 'VB' -- Default language C# or VB
SELECT @Separator2 = '_' -- Separator between Tablename and action
-- Configuration end
IF @ALLOWED = 0 SET @LANG = @DefLanguage
IF @TABLENAME ='xyzzy'
GOTO HelpText
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U')
GOTO HelpText
ELSE BEGIN
SELECT @TN = name FROM sysobjects
WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
SELECT @RowCount=0
DECLARE xArgs CURSOR FOR
SELECT C.name FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN
ORDER BY C.colid
OPEN xArgs
FETCH xArgs INTO @ColName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @RowCount=@RowCount+1
FETCH xArgs INTO @ColName
END
CLOSE xArgs
DEALLOCATE xArgs
-- Change next line to change stored procedure name
SELECT @SpName = @SpPrefix + RTRIM(@TN) + '_ListAll'
-- Configuration end
SELECT @ColLength= MAX(DATALENGTH(C.name))
FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN AND C.status<>128
SELECT @SpUpper = UPPER(@SpName)
IF EXISTS(select * from sysobjects
where (UPPER(name) = @SpUpper) AND (sysstat & 0xf = 4))
SELECT @PrintLine='ALTER PROCEDURE dbo.' + @SpName + ' AS'
ELSE
SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName + ' AS'
PRINT @PrintLine
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O, systypes U
WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype
AND U.name<>'timestamp' AND C.status=128
SELECT @HasIdent=COUNT(*) FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN AND C.status=128
DECLARE @ArgName VARCHAR(36),
@Type VARCHAR(32),
@Length VARCHAR(40)
SELECT @RowIndex=0
PRINT '-- Purpose:'
SELECT @PrintLine='-- Select list of all on ' + @TN + ' table'
PRINT @PrintLine
--PRINT '-- Resultset:'
--PRINT '-- '
PRINT '-- History: '
SELECT @USER = system_user
-----
IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
END
SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
SELECT @DateWork=substring(@DateWork,1,2) + SUBSTRING(@DateWork,4,3)
+ SUBSTRING(@DateWork,8,4)
SELECT @PrintLine='-- ' + @DateWork + ' '
SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
PRINT @PrintLine
PRINT 'SET NOCOUNT ON'
SELECT @PrintLine='SELECT '
PRINT @PrintLine
SELECT @RowCount=COUNT(*)
FROM syscolumns C, sysobjects O, systypes U
WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype
AND U.name<>'timestamp' AND C.NAME NOT IN
('ADDDATE','MODDATE','DELDATE','TSTAMP','LAST_PERSON_ID')
DECLARE xArgs CURSOR FOR
SELECT C.name FROM syscolumns C, sysobjects O, systypes U
WHERE C.id = O.id AND O.name = @TN AND C.xusertype=U.xusertype
AND U.name<>'timestamp' AND C.NAME NOT IN
('ADDDATE','MODDATE','DELDATE','TSTAMP','LAST_PERSON_ID')
ORDER BY C.colid
SELECT @RowIndex=0
OPEN xArgs
FETCH xArgs INTO @ArgName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @RowIndex=@RowIndex + 1
SELECT @ArgName=SUBSTRING(@ArgName
+ ' ',1,@ColLength+2)
SELECT @PrintLine = ' ' + RTRIM(@ArgName)
IF @RowIndex <> @RowCount
SELECT @PrintLine=@PrintLine + ','
PRINT @PrintLine
FETCH xArgs INTO @ArgName
END
CLOSE xArgs
DEALLOCATE xArgs
SELECT @PrintLine=' FROM ' + @TN
PRINT @PrintLine
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN
IF @RowCount = 0 BEGIN
PRINT '-- You MUST amend the following line to correctly identify the row to order by'
PRINT 'ORDER BY SOMECOLUMN'
END
ELSE BEGIN
PRINT 'ORDER BY '
SELECT @RowCount=COUNT(*) FROM syscolumns C, sysobjects O
WHERE C.id = O.id AND O.name = @TN
AND C.status=128
DECLARE xArgs CURSOR FOR
SELECT C.name
FROM syscolumns C, sysobjects O, systypes U
WHERE C.id = O.id AND O.name = @TN
AND C.xusertype=U.xusertype AND C.status=128
ORDER BY C.colid
SELECT @RowIndex=0
OPEN xArgs
FETCH xArgs INTO @SetValue
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @RowIndex=@RowIndex + 1
SELECT @PrintLine=' ' + @SetValue
IF @RowIndex <> @RowCount
SELECT @PrintLine=@PrintLine + ' AND'
PRINT @PrintLine
FETCH xArgs INTO @SetValue
END
CLOSE xArgs
DEALLOCATE xArgs
END
END
END
PRINT 'RETURN'
PRINT '----------------- this is the end --------------'
PRINT '/* Data Access code follows:'
IF @LANG = 'VB' BEGIN
PRINT ' #Region " List "'
PRINT ' '''''' <summary> '
PRINT ' '''''' Select list of all on ' + @TN + ' table '
PRINT ' '''''' </summary>'
PRINT ' '''''' <remarks>'
PRINT ' '''''' This code was autogenerated on ' + @DateWork
PRINT ' '''''' </remarks>'
---------
PRINT ' '''''' <returns>dataset of ' + @TN + ' </returns>'
PRINT ' Public Function List' + @TN + 'All() As DataSet '
PRINT ' Return CommonData.GetDataset("' + @SpName + '")'
PRINT ' End Function'
PRINT ' #End Region'
END
ELSE BEGIN
PRINT ' #region " List "'
PRINT ' /// <summary> '
PRINT ' /// Select list of all on ' + @TN + ' table '
PRINT ' /// </summary>'
PRINT ' /// <remarks>'
PRINT ' /// This code was autogenerated on ' + @DateWork
PRINT ' /// </remarks>'
PRINT ' /// <returns>dataset of ' + @TN + ' </returns>'
PRINT ' public DataSet List' + @TN + 'All()'
PRINT ' {'
PRINT ' return CommonData.GetDataset("' + @SpName + '");'
PRINT ' }'
PRINT ' #endregion'
END
PRINT '*/'
PRINT 'GO'
PRINT @SET_DB
PRINT 'GO'
RETURN
HelpText:
PRINT '--Use sp_MakeListAll 'TABLENAME', 'XX' to generate select S.P. for TABLENAME'
PRINT ' and XX is the language for the Wrapper VB or C#'
PRINT '--or sp_MakeListAll garbage to get this help text!'
RETURN
----------------- this is the end --------------
GO
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 08, 2008 02:57 PM|LINK
The C# library that the wrapper code follows:
#region " Copyleft "
// Copyright (C) 2000, 2003, 2004, 2005, 2007 Clive Chinery
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#endregion
#region " Usings "
using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Text;
using System.Xml;
#endregion
namespace CommonCS
{
/// <summary>
/// Common Data functions
/// </summary>
public sealed partial class CommonData
{
#region " Constants "
/// <summary>
/// With the GetBaseDirectory it should not be neccessary to rename this file as it
/// can be located with application.
/// </summary>
private static string CONFIG_FILE = "CONFIG.XML";
/// <summary>
/// Hold name of module
/// </summary>
private static string MODULE_NAME = "CommonData.cs";
#endregion
#region " Calc Functions "
/// <summary>
/// Calculate Percentage from Decimal Values
/// </summary>
/// <param name="decExpression1">Numerator value</param>
/// <param name="decExpression2">Divisor value</param>
/// <returns></returns>
public static decimal CalcPercent(decimal decExpression1, decimal decExpression2)
{
if (decExpression2 == 0)
return 0;
else
return ((100 * decExpression1) / decExpression2);
}
/// <summary>
/// Calculate Percentage from Integer Values
/// </summary>
/// <param name="iExpression1">Numerator value</param>
/// <param name="iExpression2">Divisor value</param>
/// <returns></returns>
public static int CalcPercent(int iExpression1, int iExpression2)
{
if (iExpression2 == 0)
return 0;
else
return ((100 * iExpression1) / iExpression2);
}
#endregion
#region " Decimal Functions "
/// <summary>
/// Adjust decimal value to a (sanitized) number of decimal places
/// </summary>
/// <param name="decIN">Decimal value</param>
/// <param name="iPlaces">Number of decimal places</param>
/// <returns>Adjusted value</returns>
public static decimal DecimalAdjust(decimal decIN, int iPlaces)
{
if (iPlaces < 0) iPlaces = 0;
if (iPlaces > 28) iPlaces = 28;
return decimal.Round(decIN, iPlaces);
}
#endregion
#region " Filter constants "
private const string FILTER_NUMERIC = "0123456789";
private const string FILTER_DECIMAL = FILTER_NUMERIC + "-.";
private const string FILTER_INTEGER = FILTER_NUMERIC + "-";
private const string FILTER_LOWER = "abcdefghijklmnopqrstuvwxyz";
private const string FILTER_POSTCODE = "ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789";
private const string FILTER_UPPER = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
private const string FILTER_TELEPHONE = FILTER_LOWER + FILTER_INTEGER + " +()";
private const string FILTER_TIME = FILTER_NUMERIC + ":";
private const string FILTER_ALPHA = FILTER_LOWER + FILTER_UPPER;
private const string FILTER_ALPHANUMERIC = FILTER_ALPHA + FILTER_NUMERIC;
private const string FILTER_GENERAL_TEXT = FILTER_ALPHANUMERIC + " :/\\()-=+\n";
#endregion
#region " Filter Functions (see notes with FilterGeneralText) "
#region " FilterAlpha "
/// <summary>
/// Filter Alpha (upper + lower case) Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterAlpha(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_ALPHA, ref bDataDropped);
}
/// <summary>
/// Filter Alpha (upper + lower case) Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterAlpha(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_ALPHA, ref bDataDropped);
}
#endregion
#region " FilterAlphaNumeric "
/// <summary>
/// Filter AlphaNumeric (upper + lower + integer case) Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterAlphaNumeric(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_ALPHANUMERIC, ref bDataDropped);
}
/// <summary>
/// Filter AlphaNumeric (upper + lower + integer case) Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterAlphaNumeric(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_ALPHANUMERIC, ref bDataDropped);
}
#endregion
#region " FilterDecimal "
/// <summary>
/// Filter Decimal Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterDecimal(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_DECIMAL, ref bDataDropped);
}
/// <summary>
/// Filter Decimal Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterDecimal(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_DECIMAL, ref bDataDropped);
}
#endregion
#region " FilterGeneralText (plus notes on usage) "
/// <summary>
/// Filter general text
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
/// <remarks>These filter functions are built upon the premise: "All input is evil".
/// These functions filter input to a range of permitted values controlled by
/// appropriate constants. By defining the allowed values within string constants
/// the allowed values can readily be changed.
/// Input is usually from a textbox but could also be an argument from the query string.
/// When working with non-english languages the General Text will need to be rewritten
/// on an exclude basis.
/// The filter functions filter on a permitted values basis - Thus FilterDecimal
/// would pass the second decimal point in "1.22.2".
/// </remarks>
public static string FilterGeneralText(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_GENERAL_TEXT, ref bDataDropped);
}
/// <summary>
/// Filter general text
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterGeneralText(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_GENERAL_TEXT, ref bDataDropped);
}
#endregion
#region " FilterInteger "
/// <summary>
/// Filter Integer Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterInteger(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_INTEGER, ref bDataDropped);
}
/// <summary>
/// Filter Integer Values
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterInteger(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_INTEGER, ref bDataDropped);
}
#endregion
#region " FilterLowerCase "
/// <summary>
/// Filter lower case
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterLowerCase(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_LOWER, ref bDataDropped);
}
/// <summary>
/// Filter lower case
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterLowerCase(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_LOWER, ref bDataDropped);
}
#endregion
#region " FilterPostcode "
/// <summary>
/// Filter Postcode (text forced to upper case)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterPostcode(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn.ToUpper(), FILTER_POSTCODE, ref bDataDropped);
}
/// <summary>
/// Filter Postcode (text forced to upper case)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterPostcode(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn.ToUpper(), FILTER_POSTCODE, ref bDataDropped);
}
#endregion
#region " FilterTelephone "
/// <summary>
/// Filter Telephone (text forced tolower case)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterTelephone(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn.ToLower(), FILTER_TELEPHONE, ref bDataDropped);
}
/// <summary>
/// Filter Telephone (text forced tolower case)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterTelephone(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn.ToLower(), FILTER_TELEPHONE, ref bDataDropped);
}
#endregion
#region " FilterTime "
/// <summary>
/// FilterTime (00:00 or 00:00:00 format)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterTime(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_TIME, ref bDataDropped);
}
/// <summary>
/// FilterTime (00:00 or 00:00:00 format)
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterTime(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_TIME, ref bDataDropped);
}
#endregion
#region " FilterUpperCase "
/// <summary>
/// Filter upper case
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <returns>Filtered string</returns>
public static string FilterUpperCase(string sIn)
{
bool bDataDropped = false;
return FilterWorker(sIn, FILTER_UPPER, ref bDataDropped);
}
/// <summary>
/// Filter upper case
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
public static string FilterUpperCase(string sIn, ref bool bDataDropped)
{
return FilterWorker(sIn, FILTER_UPPER, ref bDataDropped);
}
#endregion
#region " FilterWorker "
/// <summary>
/// Filter Worker
/// </summary>
/// <param name="sIn">Input to filter</param>
/// <param name="sValid">Whitelist of allowed characters</param>
/// <param name="bDataDropped">Set true if data is dropped</param>
/// <returns>Filtered string</returns>
private static string FilterWorker(string sIn, string sValid, ref bool bDataDropped)
{
try
{
bDataDropped = false;
if (sIn.Length == 0) return sIn;
StringBuilder sOutput = new StringBuilder(sIn.Length);
for (int iLoop = 0; iLoop < sIn.Length; iLoop++)
{
string sValue = sIn.Substring(iLoop, 1);
if (sValid.IndexOf(sValue) >= 0) // sValue is valid!
sOutput.Append(sValue);
else
bDataDropped = true;
}
return sOutput.ToString();
}
catch (Exception ex)
{ // System.Reflection.MethodInfo.GetCurrentMethod.Name
string sMethod = "FilterWorker";
CommonData.WriteEventError(MODULE_NAME, sMethod, ex, String.Empty);
return sIn;
}
}
#endregion
#endregion
#region " Get Functions "
#region " GetAudit"
/// <summary>
/// Return Audit Level
/// </summary>
/// <returns>0=Audit off, 1=Audit Fail, 2=Audit Fail & Success</returns>
public static int GetAuditLevel()
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
return iAuditLevel;
}
#endregion
#region " GetBaseDirectory"
/// <summary>
/// Get location where dot net started looking for this assembly
/// </summary>
/// <remarks>Brock Allen of DevelopMentor (http://staff.develop.com/ballen) suggested this.
/// <list type="Application type v path returned">
/// <item>For WinForms, Windows Service and DosCommand
/// returns the installation directory with a trailing slash</item>
/// <item>For WebForm and Web Service
/// returns the root of the site (with trailing slash) not the exe location</item>
/// </list>
/// </remarks>
public static string GetBaseDirectory()
{
return AppDomain.CurrentDomain.BaseDirectory;
}
#endregion
#region " GetConfig.."
/// <summary>
/// Get path to Configuration File
/// </summary>
/// <returns>Returns path name to config file</returns>
public static string GetConfigPathName()
{
bool bConfigFileFound = false;
return CommonData.GetConfigPathName(ref bConfigFileFound);
}
/// <summary>
/// Get path to Configuration File
/// </summary>
/// <param name="bConfigFileFound">Set true if configuration file found</param>
/// <returns>Returns path name to config file</returns>
/// <remarks>Locating the configuration file is the trivial task that would be reasonably
/// be expected. Put the config file in the directory that GetBaseDirectory will
/// find it in. It is advisable to make the application startup check that the
/// config file is present by checking that bConfigFileFound is set true.
/// </remarks>
public static string GetConfigPathName(ref bool bConfigFileFound)
{
bConfigFileFound = false;
string sDaseDirectory = CommonData.GetBaseDirectory();
if (!sDaseDirectory.EndsWith("/"))
{
if (!sDaseDirectory.EndsWith("\\")) sDaseDirectory += "\\";
}
sDaseDirectory += CONFIG_FILE;
if (File.Exists(sDaseDirectory))
bConfigFileFound = true;
else
{
// Attempt alternative directory
//sDaseDirectory = this.GetCurrentDirectory;
//if (!sDaseDirectory.EndsWith("\\")) sDaseDirectory += "\\";
//sDaseDirectory += CONFIG_FILE;
if (File.Exists(sDaseDirectory))
bConfigFileFound = true;
else
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog("Application");
xLog.Source = "Application";
xLog.WriteEntry("Could not find " + CONFIG_FILE + " in " + CommonData.GetBaseDirectory()
+ " or " + CommonData.GetCurrentDirectory());
}
}
return sDaseDirectory;
}
private static void GetConfigVariables(out string sEventLogName, out int iAuditLevel, out int iLogLevel, out string sConnect)
{
XmlDocument xXmlDocument = new XmlDocument();
try
{
string sPathName = GetConfigPathName();
if (File.Exists(sPathName))
{
xXmlDocument.Load(sPathName); // Load config file
sConnect = xXmlDocument.SelectSingleNode("/DATALAYER/CONNECT").InnerText;
sEventLogName = xXmlDocument.SelectSingleNode("/DATALAYER/EVENTLOGNAME").InnerText;
iAuditLevel = Convert.ToInt32(xXmlDocument.SelectSingleNode("/DATALAYER/AUDITLEVEL").InnerText);
iLogLevel = Convert.ToInt32(xXmlDocument.SelectSingleNode("/DATALAYER/LOGLEVEL").InnerText);
if (iAuditLevel < 0) iAuditLevel = 0;
if (iAuditLevel > 2) iAuditLevel = 2;
if (iLogLevel < 0) iLogLevel = 0;
if (iLogLevel > 3) iLogLevel = 3;
}
else
{
sConnect = "";
sEventLogName = "";
iAuditLevel = 0;
iLogLevel = 0;
}
}
catch (Exception objError)
{
throw objError;
}
finally
{
xXmlDocument = null;
}
}
#endregion
#region " GetConnect "
/// <summary>
/// Return connect string
/// </summary>
/// <returns>Return connection string from CONFIG.XML</returns>
public static string GetConnect()
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
return sConnect;
}
#endregion
#region " GetCurrentDirectory "
/// <summary>
/// Get current directory
/// </summary>
/// <returns>Returns current directory</returns>
public static string GetCurrentDirectory()
{
return System.Environment.CurrentDirectory.ToString();
}
#endregion
#region " GetDataset "
/// <summary>
/// Run stored procedure with no arguments returning a dataset
/// </summary>
/// <param name="sProcName">Procedure Name</param>
/// <returns>Dataset</returns>
public static DataSet GetDataset(string sProcName)
{
DataSet xDataSet = new DataSet();
string sConnect = GetConnect();
SqlConnection xSqlConnection = new SqlConnection(sConnect);
try
{
SqlCommand xSqlCommand = new SqlCommand(sProcName, xSqlConnection);
xSqlCommand.CommandType = CommandType.StoredProcedure;
xSqlConnection.Open();
SqlDataAdapter xSqlDataAdapter = new SqlDataAdapter(xSqlCommand);
xSqlDataAdapter.Fill(xDataSet);
xSqlDataAdapter.Dispose();
}
catch (Exception ex)
{
string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
WriteEventError(MODULE_NAME, sMethod, ex, "Running:" + sProcName);
}
finally
{
if (xSqlConnection != null) xSqlConnection.Close();
xSqlConnection.Dispose();
}
return xDataSet;
}
#endregion
#region " GetEvent.."
/// <summary>
/// Get Custom Event Log as Dataset
/// </summary>
/// <remarks>Not for users. May be consumed by data grid or web service.</remarks>
/// <returns>Dataset of event log in most recent first sequence.</returns>
public static DataSet GetEventLogDataset
{
get
{
string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;
DataSet xDataSet = new DataSet();
try
{
string sEventLogName = CommonData.GetEventLogName();
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
if (xLog.Entries.Count == 0)
CommonData.WriteEventInformation(MODULE_NAME, sMethod, "Dummy entry");
DataTable xDataTable = new DataTable();
xDataTable.Columns.Add("EntryType");
xDataTable.Columns.Add("TimeGenerated");
xDataTable.Columns.Add("Message");
xDataTable.Columns.Add("Category");
for (int iLoop = xLog.Entries.Count - 1; iLoop >= 0; iLoop--)
{
string[] sRow = new string[]
{
xLog.Entries[iLoop].EntryType.ToString(),
xLog.Entries[iLoop].TimeGenerated.ToString(),
xLog.Entries[iLoop].Message,
xLog.Entries[iLoop].Category
};
xDataTable.Rows.Add(sRow);
}
xDataSet.Tables.Add(xDataTable);
}
catch (Exception ex)
{
CommonData.WriteEventError(MODULE_NAME, sMethod, ex, "");
}
return xDataSet;
}
}
/// <summary>
/// Get Event Log Name from CONFIG file
/// </summary>
/// <returns>Return name of custom event log</returns>
public static string GetEventLogName()
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
return sEventLogName;
}
#endregion
#region " GetLogLevel "
/// <summary>
/// Returns Log Level
/// </summary>
/// <returns>0=Log off, 1=Errors only, 2=Errors+Warnings, 3=Errors+Warnings+Information</returns>
public static int GetLogLevel()
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
return iLogLevel;
}
#endregion
#region " GetMachineName "
/// <summary>
/// Get Machine Name
/// </summary>
/// <returns>Returns machine name</returns>
public static string GetMachineName()
{
return System.Environment.MachineName.ToString();
}
#endregion
#region " GetOs..."
/// <summary>
/// Get OS Platform
/// </summary>
/// <returns></returns>
public static string GetOsVersionPlatform()
{
return System.Environment.OSVersion.Platform.ToString();
}
/// <summary>
/// Get OS Version
/// </summary>
/// <returns></returns>
public static string GetOsVersionVersion()
{
return System.Environment.OSVersion.Version.ToString();
}
#endregion
#region " GetStackTrace "
/// <summary>
/// Get stack of current calls
/// </summary>
/// <returns></returns>
public static string GetStackTrace()
{
return System.Environment.StackTrace.ToString();
}
#endregion
#region " GetSystemDirectory "
/// <summary>
/// Get Location of System Directory
/// </summary>
/// <returns></returns>
public static string GetSystemDirectory()
{
return System.Environment.SystemDirectory.ToString();
}
#endregion
#region " GetUser..."
/// <summary>
/// Get domain user is logged into
/// </summary>
/// <returns></returns>
public static string GetUserDomainName()
{
return System.Environment.UserDomainName.ToString();
}
/// <summary>
/// Get logged-in user name
/// </summary>
/// <returns></returns>
public static string GetUserName()
{
return System.Environment.UserName.ToString();
}
#endregion
#region " GetVersion..."
/// <summary>
/// Get version build number
/// </summary>
/// <returns></returns>
public static string GetVersionBuild()
{
return System.Environment.Version.Build.ToString();
}
/// <summary>
/// Get Major version number
/// </summary>
/// <returns></returns>
public static string GetVersionMajor()
{
return System.Environment.Version.Major.ToString();
}
/// <summary>
/// Get minor version number
/// </summary>
/// <returns></returns>
public static string GetVersionMinor()
{
return System.Environment.Version.Minor.ToString();
}
/// <summary>
/// Get Revision number
/// </summary>
/// <returns></returns>
public static string GetVersionRevision()
{
return System.Environment.Version.Revision.ToString();
}
#endregion
#endregion
#region " Is Functions "
/// <summary>
/// Test if supplied string is blank
/// </summary>
/// <param name="sTest">String to test</param>
/// <returns>True if input string is blank</returns>
public static bool IsBlank(string sTest)
{
return (sTest.Trim().Length == 0);
}
/// <summary>
/// Test if supplied string is not blank
/// </summary>
/// <param name="sTest">String to test</param>
/// <returns>True if input string is not blank</returns>
public static bool IsNotBlank(string sTest)
{
return (sTest.Trim().Length != 0);
}
/// <summary>
/// Test if input string is numeric - NOT WORKING
/// </summary>
/// <param name="sTest"></param>
/// <returns>True if string is numeric</returns>
private static bool IsNumeric(string sTest)
{
System.Text.RegularExpressions.Regex xRegEx
= new System.Text.RegularExpressions.Regex("^\\d*\\.{0,1}\\d+$");
return xRegEx.IsMatch(sTest);
}
#endregion
#region " Null Functions "
/// <summary>
/// Convert nullable value to boolean
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static bool NullToBoolean(object vValue)
{
try
{
if (vValue != null)
return System.Convert.ToBoolean(vValue);
else
return false;
}
catch { return false; }
}
/// <summary>
/// Convert nullable value to boolean
/// </summary>
/// <param name="vValue"></param>
/// <param name="bDefault"></param>
/// <returns></returns>
public static bool NullToBoolean(object vValue, bool bDefault)
{
try
{
if (vValue != null)
return System.Convert.ToBoolean(vValue);
else
return false;
}
catch { return bDefault; }
}
/// <summary>
/// Convert nullable value to date string
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static string NullToDateString(object vValue)
{
try
{
if (vValue != null)
{
System.DateTime dValue = System.Convert.ToDateTime(vValue);
return dValue.ToString("dd/MM/yyyy HH:mm", DateTimeFormatInfo.InvariantInfo);
}
else
return "";
}
catch { return ""; }
}
/// <summary>
/// Convert nullable value to date string with sepecified format
/// </summary>
/// <param name="vValue"></param>
/// <param name="sFormat"></param>
/// <returns></returns>
public static string NullToDateString(object vValue, string sFormat)
{
try
{
if (vValue != null)
{
System.DateTime dValue = System.Convert.ToDateTime(vValue);
return dValue.ToString(sFormat, DateTimeFormatInfo.InvariantInfo);
}
else
return "";
}
catch { return ""; }
}
/// <summary>
/// Convert nullable value to decimal value
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static decimal NullToDecimal(object vValue)
{
try
{
if (vValue != null)
return System.Convert.ToDecimal(vValue);
else
return 0;
}
catch { return 0; }
}
/// <summary>
/// Convert nullable value to Integer
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static int NullToInteger(object vValue)
{
try
{
if (vValue != null)
return System.Convert.ToInt32(vValue);
else
return 0;
}
catch { return 0; }
}
/// <summary>
/// Convert nullable value to string
/// </summary>
/// <param name="vValue"></param>
/// <returns></returns>
public static string NullToString(object vValue)
{
try
{
if (vValue != null)
return System.Convert.ToString(vValue);
else
return "";
}
catch { return ""; }
}
#endregion
#region " WriteEvent "
#region " WriteEventError "
/// <summary>
/// Write error flagged event log entry
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="xError">From catch (Exception ex)</param>
public static void WriteEventError(string sPage, string sMethod, Exception xError)
{
string sNote = "";
CommonData.WriteEventError(sPage, sMethod, xError, sNote);
}
/// <summary>
/// Write error flagged event log entry
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="xError">From catch (Exception ex)</param>
/// <param name="sNote">Optional note</param>
public static void WriteEventError(string sPage, string sMethod, Exception xError, string sNote)
{
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
string sMessage = "";
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
if (sMessage.Length > 0)
sMessage += "\n" + xError.Message;
else
sMessage = xError.Message;
while (xError.InnerException != null)
{
xError = xError.InnerException;
sMessage += "\n" + xError.Message;
}
sMessage += "\n" + xError.StackTrace;
sMessage += "\n" + xError.Source;
if (sNote.Length > 0) sMessage += "\n" + sNote;
if (iLogLevel > 0)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.Error);
}
catch { }
}
}
}
#endregion
#region " WriteEventFailureAudit "
/// <summary>
/// Write Event Log Audit Failure Message
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="sNote">Message to write to event log</param>
public static void WriteEventFailureAudit(string sPage, string sMethod, string sNote)
{
string sMessage = sNote;
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
if (iAuditLevel > 1)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.FailureAudit);
}
catch { }
}
}
}
#endregion
#region " WriteEventInformation "
/// <summary>
/// Write Event Log Information Message
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="sNote">Message to write to event log</param>
public static void WriteEventInformation(string sPage, string sMethod, string sNote)
{
string sMessage = sNote;
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
if (iLogLevel == 3)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.Information);
}
catch { }
}
}
}
#endregion
#region " WriteEventSuccessAudit "
/// <summary>
/// Write Event Log Audit Success Message
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="sNote">Message to write to event log</param>
public static void WriteEventSuccessAudit(string sPage, string sMethod, string sNote)
{
string sMessage = sNote;
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
if (iAuditLevel == 2)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.SuccessAudit);
}
catch { }
}
}
}
#endregion
#region " WriteEventWarning "
/// <summary>
/// Write Event Log Event Warning Message
/// </summary>
/// <param name="sPage">MODULE_NAME or FORM_NAME</param>
/// <param name="sMethod">From string sMethod = System.Reflection.MethodInfo.GetCurrentMethod().Name;</param>
/// <param name="sNote">Message to write to event log</param>
public static void WriteEventWarning(string sPage, string sMethod, string sNote)
{
string sMessage = sNote;
if (sPage.LastIndexOf("/", 1) > 0) sPage = sPage.Substring(sPage.LastIndexOf("/", 1) + 1);
if (sMethod.Length > 0) sMessage = sMethod + ":" + sMessage;
if (sPage.Length > 0) sMessage = sPage + ":" + sMessage;
string sEventLogName; int iAuditLevel; int iLogLevel; string sConnect;
GetConfigVariables(out sEventLogName, out iAuditLevel, out iLogLevel, out sConnect);
if (iLogLevel > 1)
{
if (System.Diagnostics.EventLog.SourceExists(sEventLogName))
{
System.Diagnostics.EventLog xLog = new System.Diagnostics.EventLog(sEventLogName);
xLog.Source = sEventLogName;
try
{
xLog.WriteEntry(sMessage, System.Diagnostics.EventLogEntryType.Warning);
}
catch { }
}
}
}
#endregion
#endregion
}
}
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 08, 2008 03:04 PM|LINK
Well that finishes the promised stored procedures and wrapper code! The advantage of the TSQL generation scripts is that they can be modified to suit local requirements. For completeness, I do need to update and post here the VB.NET version of the support library, once I have updated it.
There are some NUNIT tests for the library at http://forums.asp.net/p/1207886/2120356.aspx#2120356
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
jack007
Member
165 Points
588 Posts
Re: filling the textbox,dropdownlist from the database??
Mar 09, 2008 09:20 AM|LINK
Hi TATWORTH ,
Thanks for the code.
But i m bit lost how to include each code within the earlier code.[8-)]
i m not that much familiar with those codes.
Anyway Thanks.
Jack.
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 09, 2008 12:57 PM|LINK
You will need to put the code into a separate class (I started out posting a complete class) - this class could be part of your regular web site (or web service) or be a separate class project. The latter has the advantage that it can be tested by an NUnit class project. The disadvantage of class project is that you either hard code the connection string (not good), pass in the connection string from the UI (messy) or allow the data layer to read its own configuration file (I use CONFIG.XML) via the common data library. If you put the CommonData class library within your UI, you can modify the GetConnect to read read from the config file.
In my opinion a separate data layer is the better approach.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 09, 2008 01:18 PM|LINK
>But I'm a bit lost how to include each code within the earlier code
Just paste from the #Region to the #End Region after the #Region to the #End Region in the class. It was necessary to do it this way, otherwise the chunks would get too big.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
TATWORTH
All-Star
72405 Points
14018 Posts
MVP
Re: filling the textbox,dropdownlist from the database??
Mar 29, 2008 09:39 PM|LINK
A further note - the generator routines contain a section like:
-- Set @SET_DB to name of SP to set permissions or to blank
SELECT @SET_DB = 'asp_dbPermissions'
SELECT @ObPrefix = 'x' -- Prefix for object variables
SELECT @DefLanguage = 'VB' -- Default language C# or VB
SELECT @Separator2 = '' -- Separator between Tablename and action
-- Configuration end
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239