This message was discovered on ASPFriends.com 'aspngcodegiveawayswap' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
| Francesco Sanfilippo |
Made some performance and Connection object related tweaks, and retested all
methods. New code is below. Simply cut and paste its contents into a new
.cs file in your app. Filename doesn't matter. Then paste:
using Neurodesign.DataAccess;
on any page where you need it. Code samples are in headers for the main
methods. Here's a sample of a complete usage of the object from a
global.asax file:
protected void Application_OnStart(Object sender, EventArgs e)
{
ArrayList alBanners = new ArrayList();
SqlService sql = new
SqlService(ConfigurationSettings.AppSettings["MyDSN"]);
sql.AddParameter("@id", SqlDbType.TinyInt, 1, ParameterDirection.Input,
"0");
SqlDataReader dr = sql.ExecSPRetDR("p_banner_list");
while (dr.Read())
{
alBanners.Insert((int) dr["id_banner"], (string) dr["banner_url"]);
}
Application["alBanners"] = alBanners;
alBanners = null;
dr.Close();
sql.Dispose();
}
Let me know if you have any questions!
Francesco Sanfilippo
Click here to reveal e-mail address
***************** CODE FOLLOWS **********************
using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
namespace Neurodesign.DataAccess
{
#region SqlService Class
#region Class Header and Documentation
//--------------------------------------------------------------------------------------------------------
//--- SqlService Class, version 3.65, last updated January 26, 2002
(ASP.NET Beta 2)
//---
//--- The SqlService class simplifies, modularizes, and standardizes the
ADO.NET interface
//--- to MS SQL Server. This ensures consistent data access throughout the
application.
//--- This version of the SqlService class only provides access to SQL
Server 7.0 to 2000
//--- via parameterized stored procedures, for the highest performance.
//---
//--- Notes and Remarks:
//--- 1. Still unsure whether Conn is really closed in the NonQuery method
//---
//--- Coming Soon:
//--- Transactions, XML, Exception Handling, Output Parameters
//---
//--- written by: Francesco Sanfilippo, Neurodesign Software
//--- email address: Click here to reveal e-mail address
//--------------------------------------------------------------------------------------------------------
#endregion Class Header and Documentation
public sealed class SqlService : IDisposable
{
#region Private Types and Variables
//------------------------------------------------------------------------------------------------------
//--- Private Class level variables
//--- ConnectionString - the connection string
//--- Connection - the SqlConnection object
//--- ParameterArray - the ArrayList that collects all parameters to a
stored procedure
//------------------------------------------------------------------------------------------------------
private string ConnectionString = null;
private SqlConnection Connection = null;
private ArrayList ParameterArray = null;
#endregion Private Types and Variables
#region Constructors, Destructors
//------------------------------------------------------------------------------------------------------
//--- Default class constructor, not overloaded, requires Connection
String
//--- If you need a SqlService more than once on a page, only call this
constructor the
//--- first time. For subsequent calls to SqlService, use Reset() to
reuse the object.
//------------------------------------------------------------------------------------------------------
public SqlService(string ConnString)
{
ConnectionString = ConnString;
}
//------------------------------------------------------------------------------------------------------
//--- Opens the SqlConnection object using the provided Connection String
//------------------------------------------------------------------------------------------------------
private void Open()
{
if ((Connection == null) || (Connection.State != ConnectionState.Open))
{
Connection = new SqlConnection(ConnectionString);
Connection.Open();
}
}
//------------------------------------------------------------------------------------------------------
//--- Closes Connection, returns Connection to pool, does not null
Connection
//--- Nulls ParameterArray to prepare for new Command, does not destroy
SqlService
//--- ** To be called BETWEEN sequential uses of SqlService within the
same scope!
//------------------------------------------------------------------------------------------------------
public void Reset()
{
if ((Connection != null) || (Connection.State != ConnectionState.Closed))
{
Connection.Dispose();
}
ParameterArray = null;
}
//------------------------------------------------------------------------------------------------------
//--- Calls Reset() for initial cleanup, nulls Connection and
ConnectionString
//--- Instructs GC to skip Finalization since we have done it explicitly
here
//--- ** To be called only when completely FINISHED using SqlService (on a
page)!
//------------------------------------------------------------------------------------------------------
public void Dispose()
{
Reset();
Connection = null;
ConnectionString = null;
GC.SuppressFinalize(this);
}
//------------------------------------------------------------------------------------------------------
//--- Default Finalizer for the SqlService object, just here for the sake
of being complete
//------------------------------------------------------------------------------------------------------
~SqlService()
{
}
#endregion Constructors, Destructors
#region Utility Methods
//------------------------------------------------------------------------------------------------------
//--- Adds a SQL stored procedure parameter to the private ParameterArray
object
//------------------------------------------------------------------------------------------------------
public void AddParameter(string pname, SqlDbType pdatatype, int psize,
ParameterDirection pdirection, string pvalue)
{
if (ParameterArray == null)
{
ParameterArray = new ArrayList();
}
Parameter param = new Parameter(pname, pdatatype, psize, pdirection,
pvalue);
ParameterArray.Add(param);
param.Dispose();
param = null;
}
//------------------------------------------------------------------------------------------------------
//--- Converts a custom Parameter object to a SQLParameter object
//--- NOTE: Not clear on best way to clean up SqlParameter resources in
here...
//------------------------------------------------------------------------------------------------------
private SqlParameter ConvertToSqlParam(Parameter p)
{
SqlParameter sp = new SqlParameter(p.ParameterName, p.DataType, p.Size);
sp.Direction = p.Direction;
sp.Value = p.Value;
return sp;
}
//------------------------------------------------------------------------------------------------------
//--- Opens the Connection object as late as possible, creates a
SqlCommand object
//--- requires the stored procedure name
//--- assumes the ParameterArray has already been populated, if parameters
exist
//------------------------------------------------------------------------------------------------------
private SqlCommand CreateCommand(string sp_name)
{
Open();
SqlCommand cmd = new SqlCommand(sp_name, Connection);
cmd.CommandType = CommandType.StoredProcedure;
if (ParameterArray != null)
{
Parameter p = null;
SqlParameter sp = null;
IEnumerator counter = ParameterArray.GetEnumerator();
while (counter.MoveNext())
{
p = (Parameter) counter.Current;
sp = ConvertToSqlParam(p);
cmd.Parameters.Add(sp);
}
counter = null;
ParameterArray = null;
p.Dispose();
p = null;
sp = null;
}
cmd.Dispose();
return cmd;
}
#endregion Utility Methods
#region ExecSPRetDS - DataSet
//------------------------------------------------------------------------------------------------------
//--- Executes a stored procedure and returns a DataSet
//--- requires the stored procedure name and the DataTable table name
//--- assumes the ParameterArray has already been populated, if parameters
exist
//---
//--- Sample C# code:
//---
//--- SqlService sql = new SqlService(myConnString);
//--- sql.AddParameter("@myID", SqlDbType.TinyInt, 1,
ParameterDirection.Input, myID);
//--- DataSet ds = sql.ExecSPRetDS("myStoredProc", "myTable");
//--- <your work goes here>
//--- ds.Dispose();
//--- sql.Reset(); or sql.Dispose();
//---
//------------------------------------------------------------------------------------------------------
public DataSet ExecSPRetDS(string sp_name, string name_table)
{
SqlCommand cmd = CreateCommand(sp_name);
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
if (name_table.Trim().Length > 0)
{
da.Fill(ds, name_table);
}
else
{
da.Fill(ds);
}
cmd.Dispose();
da.Dispose();
ds.Dispose();
return ds;
}
#endregion ExecSPRetDS - DataSet
#region ExecSPRetDR - DataReader
//------------------------------------------------------------------------------------------------------
//--- Executes a stored procedure and returns a DataReader
//--- requires the stored procedure name
//--- assumes the ParameterArray has already been populated, if parameters
exist
//--- We do not use CommandBehavior.CloseConnection so that Close() or
Dispose()
//--- can manage the state of the Connection and return it to the pool as
necessary
//--- NOTE: RecordsAffected returns a count for UPDATE, INSERT, and DELETE
//--- but returns a -1 for a SELECT statement.
//---
//--- Sample C# code:
//---
//--- SqlService sql = new SqlService(myConnString);
//--- sql.AddParameter("@myID", SqlDbType.TinyInt, 1,
ParameterDirection.Input, myID);
//--- SqlDataReader dr = sql.ExecSPRetDR("myStoredProc");
//--- <your code goes here>
//--- dr.Close();
//--- sql.Reset(); or sql.Dispose();
//---
//------------------------------------------------------------------------------------------------------
public SqlDataReader ExecSPRetDR(string sp_name)
{
SqlCommand cmd = CreateCommand(sp_name);
cmd.Dispose();
return cmd.ExecuteReader();
}
#endregion ExecSPRetDR - DataReader
#region ExecSPRetSC - Scalar
//------------------------------------------------------------------------------------------------------
//--- Executes a stored procedure and returns a Scalar Object
//--- requires the stored procedure name
//--- assumes the ParameterArray has already been populated, if parameters
exist
//--- assumes the developer will cast the object as necessary in the
calling code
//---
//--- Sample C# code:
//---
//--- SqlService sql = new SqlService(myConnString);
//--- sql.AddParameter("@myID", SqlDbType.TinyInt, 1,
ParameterDirection.Input, myID);
//--- string myVar = sql.ExecSPRetSC("myStoredProc").ToString();
//--- <your code goes here>
//--- sql.Reset(); or sql.Dispose();
//---
//------------------------------------------------------------------------------------------------------
public object ExecSPRetSC(string sp_name)
{
SqlCommand cmd = CreateCommand(sp_name);
cmd.Dispose();
return cmd.ExecuteScalar();
}
#endregion ExecSPRetSC - Scalar
#region ExecSPRetNQ - NonQuery
//------------------------------------------------------------------------------------------------------
//--- Executes a stored procedure and returns a RowsAffected count only
//--- requires the stored procedure name
//--- assumes the ParameterArray has already been populated, if parameters
exist
//---
//--- Sample C# code:
//---
//--- SqlService sql = new SqlService(myConnString);
//--- sql.AddParameter("@myID", SqlDbType.TinyInt, 1,
ParameterDirection.Input, myID);
//--- int myVar = sql.ExecSPRetNQ("myStoredProc");
//--- <your code goes here>
//--- sql.Reset(); or sql.Dispose();
//---
//------------------------------------------------------------------------------------------------------
public int ExecSPRetNQ(string sp_name)
{
SqlCommand cmd = CreateCommand(sp_name);
cmd.Dispose();
return cmd.ExecuteNonQuery();
}
#endregion ExecSPRetNQ - NonQuery
} // end SqlService Class
#endregion SqlService Class
#region Parameter Class
#region Class Header and Documentation
//------------------------------------------------------------------------------------------------------
//--- The Parameter class acts as a temporary container for a stored
procedure parameter
//------------------------------------------------------------------------------------------------------
#endregion Class Header and Documentation
public sealed class Parameter : IDisposable
{
#region Private Types and Variables
//------------------------------------------------------------------------------------------------------
//--- Private Class level variables:
//--- pParameterName - the name of the parameter (must include the "@"
symbol)
//--- pDataType - the SqlDbType of the parameter (System.Data.SqlDbType)
//--- pSize - the size of the parameter
//--- pDirection - the parameter direction, Input/Output
(System.Data.ParameterDirection)
//--- pValue - the value of the parameter
//------------------------------------------------------------------------------------------------------
private string pParameterName = "";
private SqlDbType pDataType = System.Data.SqlDbType.VarChar;
private int pSize = 0;
private ParameterDirection pDirection;
private string pValue = "";
#endregion Private Types and Variables
#region Public R/W Properties
//------------------------------------------------------------------------------------------------------
//--- Public R/W Properties for the Private Class level variables of the
Parameter object
//------------------------------------------------------------------------------------------------------
public string ParameterName
{
get { return pParameterName; }
set { pParameterName = value; }
}
public SqlDbType DataType
{
get { return pDataType; }
set { pDataType = value; }
}
public int Size
{
get { return pSize; }
set { pSize = value; }
}
public ParameterDirection Direction
{
get { return pDirection; }
set { pDirection = value; }
}
public string Value
{
get { return pValue; }
set { pValue = value; }
}
#endregion Public R/W Properties
#region Constructors, Destructors
//------------------------------------------------------------------------------------------------------
//--- Default Parameter class constructor, requires default SQLParameter
attributes
//------------------------------------------------------------------------------------------------------
public Parameter(string sParameterName, SqlDbType sDataType, int sSize,
ParameterDirection sDirection, string sValue)
{
ParameterName = sParameterName;
DataType = sDataType;
Size = sSize;
Direction = sDirection;
Value = sValue;
}
//------------------------------------------------------------------------------------------------------
//--- Destroys the Parameter object
//------------------------------------------------------------------------------------------------------
public void Dispose()
{
}
#endregion Constructors, Destructors
} // end Parameter Class
#endregion Parameter Class
} // end DataAccess Namespace
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
|
|
| |
| |
| Steven A Smith (VIP) |
Your attachment was 1 byte. I think it was a carriage return...
Steve
----- Original Message -----
From: "Francesco Sanfilippo" <Click here to reveal e-mail address>
To: "aspngcodegiveawayswap" <Click here to reveal e-mail address>
Sent: Friday, February 01, 2002 12:42 PM
Subject: [aspngcodegiveawayswap] Latest C# SQL Data Class v 3.65
[Original message clipped]
--------------------------
[Original message clipped]
--------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
------------------------
[Original message clipped]
--------------------------------------------------------------------------------
[Original message clipped]
|
|
| |
|
| |
| Francesco Sanfilippo |
No attachment necessary...the code was pasted below.
Francesco
[Original message clipped]
_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
|
|
| |
|
|
|
|
|
