|
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Xml;
using System.Collections.Specialized;
using System.Collections;
using System.ComponentModel;
using System.Diagnostics;
namespace JobMorning
{
/// <summary>
/// A dictionary with keys of type SqlDbType and values of type Type
/// </summary>
public class SqlDBTypeTOTypeAssociation: System.Collections.DictionaryBase
{
/// <summary>
/// Initializes a new empty instance of the SqlDBTypeTOTypeAssociation class
/// </summary>
public SqlDBTypeTOTypeAssociation()
{
// empty
}
/// <summary>
/// Gets or sets the Type associated with the given SqlDbType
/// </summary>
/// <param name="key">
/// The SqlDbType whose value to get or set.
/// </param>
public virtual Type this[SqlDbType key]
{
get
{
return (Type) this.Dictionary[key];
}
set
{
this.Dictionary[key] = value;
}
}
/// <summary>
/// Adds an element with the specified key and value to this SqlDBTypeTOTypeAssociation.
/// </summary>
/// <param name="key">
/// The SqlDbType key of the element to add.
/// </param>
/// <param name="value">
/// The Type value of the element to add.
/// </param>
public virtual void Add(SqlDbType key, Type value)
{
this.Dictionary.Add(key, value);
}
/// <summary>
/// Determines whether this SqlDBTypeTOTypeAssociation contains a specific key.
/// </summary>
/// <param name="key">
/// The SqlDbType key to locate in this SqlDBTypeTOTypeAssociation.
/// </param>
/// <returns>
/// true if this SqlDBTypeTOTypeAssociation contains an element with the specified key;
/// otherwise, false.
/// </returns>
public virtual bool Contains(SqlDbType key)
{
return this.Dictionary.Contains(key);
}
/// <summary>
/// Determines whether this SqlDBTypeTOTypeAssociation contains a specific key.
/// </summary>
/// <param name="key">
/// The SqlDbType key to locate in this SqlDBTypeTOTypeAssociation.
/// </param>
/// <returns>
/// true if this SqlDBTypeTOTypeAssociation contains an element with the specified key;
/// otherwise, false.
/// </returns>
public virtual bool ContainsKey(SqlDbType key)
{
return this.Dictionary.Contains(key);
}
/// <summary>
/// Determines whether this SqlDBTypeTOTypeAssociation contains a specific value.
/// </summary>
/// <param name="value">
/// The Type value to locate in this SqlDBTypeTOTypeAssociation.
/// </param>
/// <returns>
/// true if this SqlDBTypeTOTypeAssociation contains an element with the specified value;
/// otherwise, false.
/// </returns>
public virtual bool ContainsValue(Type value)
{
foreach (Type item in this.Dictionary.Values)
{
if (item == value)
return true;
}
return false;
}
/// <summary>
/// Removes the element with the specified key from this SqlDBTypeTOTypeAssociation.
/// </summary>
/// <param name="key">
/// The SqlDbType key of the element to remove.
/// </param>
public virtual void Remove(SqlDbType key)
{
this.Dictionary.Remove(key);
}
/// <summary>
/// Gets a collection containing the keys in this SqlDBTypeTOTypeAssociation.
/// </summary>
public virtual System.Collections.ICollection Keys
{
get
{
return this.Dictionary.Keys;
}
}
/// <summary>
/// Gets a collection containing the values in this SqlDBTypeTOTypeAssociation.
/// </summary>
public virtual System.Collections.ICollection Values
{
get
{
return this.Dictionary.Values;
}
}
}
public class TypeMapping
{
static SqlDBTypeTOTypeAssociation map;
public static SqlDBTypeTOTypeAssociation Mapping
{
get {return map;}
}
static TypeMapping()
{
map = new SqlDBTypeTOTypeAssociation();
map.Add(SqlDbType.BigInt, typeof(Int64));
map.Add(SqlDbType.Binary, typeof(byte[]));
map.Add(SqlDbType.Bit, typeof(Boolean ));
map.Add(SqlDbType.Char, typeof( String ));
map.Add(SqlDbType.DateTime, typeof( DateTime ));
map.Add(SqlDbType.Decimal, typeof( Decimal ));
map.Add(SqlDbType.Float, typeof( Double ));
map.Add(SqlDbType.Image, typeof( byte[]));
map.Add(SqlDbType.Int, typeof( Int32 ));
map.Add(SqlDbType.Money, typeof( Decimal));
map.Add(SqlDbType.NChar, typeof( String ));
map.Add(SqlDbType.NText, typeof( String ));
map.Add(SqlDbType.NVarChar, typeof( String ));
map.Add(SqlDbType.Real, typeof( Single ));
map.Add(SqlDbType.SmallDateTime, typeof( DateTime ));
map.Add(SqlDbType.SmallInt, typeof( Int16 ));
map.Add(SqlDbType.SmallMoney, typeof( Decimal));
map.Add(SqlDbType.Text, typeof( String ));
map.Add(SqlDbType.Timestamp, typeof( DateTime ));
map.Add(SqlDbType.TinyInt, typeof( Byte ));
map.Add(SqlDbType.UniqueIdentifier, typeof( Guid ));
map.Add(SqlDbType.VarBinary, typeof( byte[] ));
map.Add(SqlDbType.VarChar, typeof( String ));
map.Add(SqlDbType.Variant, typeof( Object ));
}
}
/// <summary>
/// SPGenerator is a class that will call the stored procedures defined in the XML configuration files.
/// </summary>
public class SPGenerator
{
string _configPath;
/// <summary>
/// Constructor of the class SPGenerator
/// </summary>
/// <param name="configPath">The configPath is the content of the file defined
/// in the web.config key APP_STOREDPROCEDURES</param>
public SPGenerator(string configPath)
{
if (configPath == null)
{
throw new Exception("Stored Proc Config Path not specified");
}
else
{
_configPath = configPath;
}
}
/// <summary>
/// GenerateSqlAutomatic works only for SQL servers and doesn't use the file defined
/// in APP_STOREDPROCEDURES to find stored procedures names: it asks directly the SQL server.
/// </summary>
/// <param name="sprocName">Sproc name. The exsitence of this stored procedure will be checked</param>
/// <param name="nameValuePairs">a NameValueCollections that contains all the input parameters
/// values for the stored procedure to call</param>
/// <returns>a SqlCommand object</returns>
public static SqlCommand GenerateSqlAutomatic(SqlConnection con, string strProcName, NameValueCollection nameValuePairs)
{
SqlCommand com = new SqlCommand(strProcName, con);
com.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
SqlCommandBuilder.DeriveParameters(com);
foreach (SqlParameter par in com.Parameters)
{
if(par.ParameterName.Equals("@RETURN_VALUE")) continue;
if(nameValuePairs.Get(par.ParameterName)==null)
{
par.Value="";
continue;
}
Type type = TypeMapping.Mapping[par.SqlDbType];
if (type == typeof(string))
{
if ((nameValuePairs.Get(par.ParameterName).Length==0)||(nameValuePairs.Get(par.ParameterName)==null))
{
par.Value = "";
}
else
{
par.Value = nameValuePairs.Get(par.ParameterName);
}
}
else if (type == typeof(Guid))
{
if ((nameValuePairs.Get(par.ParameterName).Length==0)||(nameValuePairs.Get(par.ParameterName)==null))
{
par.Value = Guid.Empty;
}
}
else if (type == typeof(DateTime))
{
if ((nameValuePairs.Get(par.ParameterName).Length==0)||(nameValuePairs.Get(par.ParameterName)==null))
{
par.Value = DateTime.Now;
}
}
else if (type == typeof(byte[]))
{
if ((nameValuePairs.Get(par.ParameterName).Length==0)||(nameValuePairs.Get(par.ParameterName)==null))
{
par.Value = new byte[0];
}
}
else
{
if ((nameValuePairs.Get(par.ParameterName).Length==0)||(nameValuePairs.Get(par.ParameterName)==null))
{
par.Value = 0;
}
else
{
par.Value = Convert.ToInt32(nameValuePairs.Get(par.ParameterName));
}
}
}
}
finally
{
con.Close();
}
return com;
}
/// <summary>
/// Generate a SqlCommand object
/// </summary>
/// <param name="dbName">The name of the database</param>
/// <param name="sprocName">The name of the stored procedure as defined in the XML file
/// identified by the key APP_STOREDPROCEDURES</param>
/// <param name="nameValuePairs">a NameValueCollections that contains all the input parameters
/// values for the stored procedure to call</param>
/// <returns>a SqlCommand object</returns>
public SqlCommand GenerateSqlCommandandParams(string dbName,
string sprocName,
NameValueCollection nameValuePairs)
{
XmlTextReader reader = null;
bool inDB = false;
bool inSproc = false;
bool requiresValue;
string paramName;
string paramValue;
string paramDBType;
string directionString;
SqlParameter param;
SqlDbType dbType;
SqlCommand cmd;
try
{
cmd = new SqlCommand();
cmd.CommandText = sprocName;
cmd.CommandType = CommandType.StoredProcedure;
reader = new XmlTextReader(_configPath);
//Add to nametable so we can do object --> object comparisons
object ntDB = reader.NameTable.Add("database");
object ntSP = reader.NameTable.Add("sproc");
object ntParam = reader.NameTable.Add("param");
//Parse XML
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name.Equals(ntDB))
{ //In database node
if (reader.GetAttribute("name").ToUpper() == dbName.ToUpper())
{
inDB = true;
}
}
if (inDB && reader.Name.Equals(ntSP))
{ //In sproc node
if (reader.GetAttribute("name").ToLower() == sprocName.ToLower())
{
inSproc = true;
}
}
if (inSproc && reader.Name.Equals(ntParam))
{
paramName = reader.GetAttribute("name");
paramValue = nameValuePairs.Get(paramName);
paramDBType = reader.GetAttribute("SqlDbType");
directionString = reader.GetAttribute("direction");
if (directionString.ToLower() == "returnvalue" || directionString.ToLower() == "output")
{
requiresValue = false;
}
else
{
requiresValue = true;
}
if (requiresValue && paramValue == null)
{
// throw new Exception("Parameter " + paramName + ", type= "+paramDBType+" has no associated value.");
// Don't forget to update that list to add all cases
if (paramDBType.Equals("Int"))
{
paramValue = "0";
}
if (paramDBType.Equals("Decimal"))
{
paramValue = "0";
}
if (paramDBType.Equals("Float"))
{
paramValue = "0";
}
if (paramDBType.Equals("Real"))
{
paramValue = "0";
}
if (paramDBType.Equals("NVarChar"))
{
paramValue = "";
}
if (paramDBType.Equals("VarChar"))
{
paramValue = "";
}
if (paramDBType.Equals("Char"))
{
paramValue = "";
}
if (paramDBType.Equals("NChar"))
{
paramValue = "";
}
}
dbType =(SqlDbType)TypeDescriptor.GetConverter(typeof(SqlDbType)).ConvertFromString(paramDBType);
param = new SqlParameter(paramName,dbType);
param.Direction = (ParameterDirection)TypeDescriptor.GetConverter(typeof(ParameterDirection)).ConvertFromString(directionString);
if (requiresValue)
{
param.Value = paramValue;
}
if (reader.GetAttribute("size") != null)
{
param.Size = Int32.Parse(reader.GetAttribute("size"));
// to avoid database return code with errors,
// truncate the param.Value to its attribute size
if (requiresValue)
{
string strCutValue;
string strDebugValue;
strDebugValue = paramValue;
strCutValue = paramValue.Substring(0, Math.Min(paramValue.Length, param.Size));
param.Value = strCutValue;
}
}
cmd.Parameters.Add(param);
}
}
if (reader.NodeType == XmlNodeType.EndElement)
{
if (inSproc && reader.Name.Equals(ntSP))
{ //Stop parsing when end element for sproc is reached
break;
}
}
}
return cmd;
}
finally
{
reader.Close();
}
}
/// <summary>
/// Generate a OleDbCommand object
/// </summary>
/// <param name="dbName">The name of the database</param>
/// <param name="sprocName">The name of the stored procedure as defined in the XML file
/// identified by the key APP_STOREDPROCEDURES</param>
/// <param name="nameValuePairs">a NameValueCollections that contains all the input parameters
/// values for the stored procedure to call</param>
/// <returns>a OleDbCommand object</returns>
public OleDbCommand GenerateOleDbCommandandParams(string dbName,
string sprocName,
NameValueCollection nameValuePairs)
{
XmlTextReader reader = null;
bool inDB = false;
bool inSproc = false;
bool requiresValue;
string paramName;
string paramValue;
string paramDBType;
string directionString;
OleDbParameter param;
OleDbType dbType;
OleDbCommand cmd;
try
{
cmd = new OleDbCommand();
cmd.CommandText = sprocName;
cmd.CommandType = CommandType.StoredProcedure;
reader = new XmlTextReader(_configPath);
//Add to nametable so we can do object --> object comparisons
object ntDB = reader.NameTable.Add("database");
object ntSP = reader.NameTable.Add("sproc");
object ntParam = reader.NameTable.Add("param");
//Parse XML
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name.Equals(ntDB))
{ //In database node
if (reader.GetAttribute("name") == dbName)
{
inDB = true;
}
}
if (inDB && reader.Name.Equals(ntSP))
{ //In sproc node
if (reader.GetAttribute("name").ToLower() == sprocName.ToLower())
{
inSproc = true;
}
}
if (inSproc && reader.Name.Equals(ntParam))
{
paramName = reader.GetAttribute("name");
paramValue = nameValuePairs.Get(paramName);
paramDBType = reader.GetAttribute("OleDbType");
directionString = reader.GetAttribute("direction");
if (directionString.ToLower() == "returnvalue" || directionString.ToLower() == "output")
{
requiresValue = false;
}
else
{
requiresValue = true;
}
if (requiresValue && paramValue == null)
{
// Don't forget to update that list to add all cases
if (paramDBType.Equals("Int"))
{
paramValue = "0";
}
if (paramDBType.Equals("Decimal"))
{
paramValue = "0";
}
if (paramDBType.Equals("Double"))
{
paramValue = "0";
}
if (paramDBType.Equals("Single"))
{
paramValue = "0";
}
if (paramDBType.Equals("VarWChar"))
{
paramValue = "";
}
if (paramDBType.Equals("VarChar"))
{
paramValue = "";
}
if (paramDBType.Equals("Char"))
{
paramValue = "";
}
if (paramDBType.Equals("WChar"))
{
paramValue = "";
}
}
else
{
dbType =(OleDbType)TypeDescriptor.GetConverter(typeof(OleDbType)).ConvertFromString(reader.GetAttribute("OleDbType"));
param = new OleDbParameter(paramName,dbType);
param.Direction = (ParameterDirection)TypeDescriptor.GetConverter(typeof(ParameterDirection)).ConvertFromString(directionString);
if (requiresValue)
{
param.Value = paramValue;
}
if (reader.GetAttribute("size") != null)
{
param.Size = Int32.Parse(reader.GetAttribute("size"));
}
cmd.Parameters.Add(param);
}
}
}
if (reader.NodeType == XmlNodeType.EndElement)
{
if (inSproc && reader.Name.Equals(ntSP))
{ //Stop parsing when end element for sproc is reached
break;
}
}
}
return cmd;
}
finally
{
reader.Close();
}
}
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip