sqlhelper/dbhelper/disposehelper/SafeDataReader/


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;
using Repositories.ManageSQL;

namespace Repositories.ManageSQL
{
///

/// Strongly typed DBHelper for MS SQL Server
///

public partial class SqlHelper : DbHelper
{
public static string BuildTrustedConnectionString(string serverName, string databaseName)
{
var s = new SqlConnectionStringBuilder();
s.DataSource = serverName;
s.InitialCatalog = databaseName;
s.IntegratedSecurity = true;
return s.ToString();
}
public static string BuildConnectionString(string serverName, string databaseName, string userName, string password)
{
var s = new SqlConnectionStringBuilder();
s.DataSource = serverName;
s.InitialCatalog = databaseName;
s.UserID = userName;
s.Password = password;
return s.ToString();
}
public static string GetConnectionString(string connectionStringKey)
{
return System.Configuration.ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString;
}

public int DefaultCommandTimeout { get; set; }

public SqlHelper(string connectionString)
: base(connectionString)
{
this.DefaultCommandTimeout = 30;
}

protected override SqlConnection CreateConnection(string connectionString)
{
return new SqlConnection(connectionString);
}

protected override SqlCommand CreateCommand(string cmdText, SqlConnection connection)
{
return new SqlCommand(cmdText, connection) { CommandTimeout = DefaultCommandTimeout };
}

protected override IDbDataAdapter CreateDataAdapter(SqlCommand cmd)
{
return new SqlDataAdapter(cmd);
}

public SafeDataReader ExecuteSafeReader(CommandType cmdType, string cmdText, Action paramAction)
{
var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
var cmd = CreateCommand(cmdText, conn);
cmd.CommandType = cmdType;
if (paramAction != null) paramAction(cmd.Parameters);

if (this.SharedConnection != conn) conn.Open();

var dr = cmd.ExecuteReader();

var d = new DisposeHelper(() =>
{
dr.Dispose();
cmd.Dispose();
if (this.SharedConnection != conn)
{
conn.Dispose();
}
});
return new SafeDataReader(dr, d);
}
// Use a SqlDataReader to stream results
protected SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, Action paramAction)
{
var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
var cmd = CreateCommand(cmdText, conn);
cmd.CommandType = cmdType;
if (paramAction != null) paramAction(cmd.Parameters);

if (this.SharedConnection != conn) conn.Open();
return cmd.ExecuteReader(); // WARNING: Leaks SqlCommand and SqlConnection IDisposables
}

// Bulk copy implementations
public void BulkCopy(SqlDataReader dr, string destinationTable, int batchSize, ColumnMatchingMode mode)
{
var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
try
{
using (var bc = new SqlBulkCopy(conn))
{
bc.DestinationTableName = destinationTable;
bc.BatchSize = batchSize;
bc.NotifyAfter = batchSize;

switch (mode)
{
case ColumnMatchingMode.Ordinal:
for (int i = 0; i < dr.FieldCount; i++)
{
bc.ColumnMappings.Add(i, i);
}
break;
case ColumnMatchingMode.CaseSensitiveName:
for (int i = 0; i
{
Trace.TraceInformation(“{0} rows copied…”, totalRows += batchSize);
};

if (this.SharedConnection != conn) conn.Open();
try
{
bc.WriteToServer(dr);
}
finally
{
if (this.SharedConnection != conn) conn.Close();
}
}
}
finally
{
if (this.SharedConnection != conn) conn.Dispose();
}
}
public void BulkCopy(DataTable dt, string destinationTable)
{
BulkCopy(dt, destinationTable, ColumnMatchingMode.Ordinal);
}
public void BulkCopy(DataTable dt, string destinationTable, ColumnMatchingMode mode)
{
var c = new List(dt.Columns.Count);
switch (mode)
{
case ColumnMatchingMode.Ordinal:
foreach (DataColumn dc in dt.Columns)
{
c.Add(new SqlBulkCopyColumnMapping(dc.Ordinal, dc.Ordinal));
}
break;
case ColumnMatchingMode.CaseSensitiveName:
foreach (DataColumn dc in dt.Columns)
{
c.Add(new SqlBulkCopyColumnMapping(dc.ColumnName, dc.ColumnName));
}
break;
default:
throw new ArgumentOutOfRangeException(“mode”);
}
BulkCopy(dt, destinationTable, c.ToArray());
}
public void BulkCopy(DataTable dt, string destinationTable, params string[] destinationColumnNames)
{
var columnMaps = destinationColumnNames.Select((s, i) => new SqlBulkCopyColumnMapping(i, s));
BulkCopy(dt, destinationTable, columnMaps.ToArray());
}
public void BulkCopy(DataTable dt, string destinationTable, params SqlBulkCopyColumnMapping[] columnMappings)
{
var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
try
{
using (var bc = new SqlBulkCopy(conn))
{
bc.DestinationTableName = destinationTable;
foreach (var columnMapping in columnMappings)
{
if (columnMapping != null)
{
bc.ColumnMappings.Add(columnMapping);
}
}

int totalRows = dt.Rows.Count;
bc.NotifyAfter = totalRows / 10;
bc.SqlRowsCopied += (s, e) =>
{
Trace.TraceInformation(“{0}/{1} rows copied…”, e.RowsCopied, totalRows);
};

if (this.SharedConnection != conn) conn.Open();

try
{
bc.WriteToServer(dt);
}
catch (Exception ex)
{
var e = new SqlBulkCopyExceptionHelper(this.ConnectionString, bc, ex, dt);
Exception newEx;
if (e.TryHandle(out newEx))
{
throw newEx;
}
else
{
throw;
}
}
finally
{
if (this.SharedConnection != conn) conn.Close();
}
}
}
finally
{
if (this.SharedConnection != conn) conn.Dispose();
}
}
}
}
————-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;

namespace Repositories.ManageSQL
{
partial class SqlBulkCopyExceptionHelper
{
private SqlBulkCopy SqlBulkCopy { get; set; }
private Exception InnerException { get; set; }
private string ConnectionString { get; set; }
private DataTable SourceTable { get; set; }

public SqlBulkCopyExceptionHelper(string connectionString, SqlBulkCopy bc, Exception ex, DataTable sourceTable)
{
this.ConnectionString = connectionString;
this.SqlBulkCopy = bc;
this.InnerException = ex;
this.SourceTable = sourceTable;
}

public bool TryHandle(out Exception ex)
{
var handlers = new Func[] { HandleInvalidColumnLength, HandleConversionError };
foreach (var h in handlers)
{
ex = h();
if (ex != null) return true;
}
ex = null;
return false;
}

private Exception HandleConversionError()
{
DataTable schemaDt = GetSchema();

foreach (DataRow dr in this.SourceTable.Rows)
{
foreach (DataColumn dc in schemaDt.Columns)
{
object o = dr[GetSourceColumnIndex(dc.Ordinal)];
if (o == null || o == DBNull.Value) continue;

try
{
Convert.ChangeType(o, dc.DataType);
}
catch (FormatException)
{
return new InvalidOperationException(
string.Format(“Cannot convert {0} to {1}. Row {2}, Column {3}:{4}”, o, dc.DataType, dr.Table.Rows.IndexOf(dr), dc.Ordinal, dc.ColumnName),
this.InnerException
);
}
}
}
return null;
}

private Exception HandleInvalidColumnLength()
{
const string INVALID_COL_LENGTH = “Received an invalid column length from the bcp client for colid”;
const int ERROR_CODE = unchecked((int)0x80131904);
var sqlEx = this.InnerException as SqlException;
if (sqlEx == null || sqlEx.ErrorCode != ERROR_CODE || sqlEx.Errors.Count < 1)
{
// Not correct SQL Exception
return null;
}
string msg = sqlEx.Errors[0].Message;
int columnId;
if (msg.Length < INVALID_COL_LENGTH.Length || !int.TryParse(msg.Substring(INVALID_COL_LENGTH.Length).TrimEnd('.'), out columnId))
{
// Incorrect message
return null;
}

// get length of Sql table column
DataTable schemaDt = GetSchema();
var destCol = schemaDt.Columns[columnId – 1]; // columnId is 1-based
var sourceCol = this.SourceTable.Columns[GetSourceColumnIndex(destCol.Ordinal)];
var destColLength = destCol.MaxLength;
if (destColLength destColLength)
{
return new InvalidOperationException(
string.Format(“Row {0}, Column index {1} is {2} chars, but {3}.{4} max length is {5} chars”,
this.SourceTable.Rows.IndexOf(dr), sourceCol.Ordinal, s.Length,
this.SqlBulkCopy.DestinationTableName, schemaDt.Columns[destCol.Ordinal].ColumnName, destColLength
),
this.InnerException
);
}
}

return null;
}

private DataTable GetSchema()
{
DataTable schemaDt = new DataTable();
using (var conn = new SqlConnection(this.ConnectionString))
using (var cmd = new SqlCommand(“SELECT * FROM ” + this.SqlBulkCopy.DestinationTableName, conn))
using (var da = new SqlDataAdapter(cmd))
{
da.FillSchema(schemaDt, SchemaType.Source);
}
return schemaDt;
}

private int GetSourceColumnIndex(int destinationIndex)
{
SqlBulkCopyColumnMapping cm = this.SqlBulkCopy.ColumnMappings.Cast()
.FirstOrDefault(c => c.DestinationOrdinal == destinationIndex);
if (cm == null)
{
cm = this.SqlBulkCopy.ColumnMappings[destinationIndex];
}
return cm.SourceOrdinal < 0 ? this.SourceTable.Columns.IndexOf(cm.SourceColumn) : cm.SourceOrdinal;
}
}
}
—————
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;

namespace Repositories.ManageSQL
{
/// This is a DataReader that 'fixes' any null values before
/// they are returned to our business code.
///

public class SafeDataReader : IDataReader
{
private IDataReader _dataReader;
private DisposeHelper _disposer;
///

/// Get a reference to the underlying data reader
/// object that actually contains the data from
/// the data source.
///

protected IDataReader DataReader
{
get { return _dataReader; }
}

///

/// Initializes the SafeDataReader object to use data from
/// the provided DataReader object.
///

/// The source DataReader object containing the data.
public SafeDataReader(IDataReader dataReader, DisposeHelper disposer)
{
_dataReader = dataReader;
_disposer = disposer;
}

///

/// Gets a string value from the datareader.
///

///
/// Returns empty string for null.
///
/// Ordinal column position of the value.
public string GetString(string name)
{
return GetString(_dataReader.GetOrdinal(name));
}

///

/// Gets a string value from the datareader.
///

///
/// Returns empty string for null.
///
/// Name of the column containing the value.
public virtual string GetString(int i)
{
if (_dataReader.IsDBNull(i))
return string.Empty;
else
return _dataReader.GetString(i);
}

///

/// Gets a value of type from the datareader.
///

/// Ordinal column position of the value.
public object GetValue(string name)
{
return GetValue(_dataReader.GetOrdinal(name));
}

///

/// Gets a value of type from the datareader.
///

/// Name of the column containing the value.
public virtual object GetValue(int i)
{
if (_dataReader.IsDBNull(i))
return null;
else
return _dataReader.GetValue(i);
}

///

/// Gets an integer from the datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public int GetInt32(string name)
{
return GetInt32(_dataReader.GetOrdinal(name));
}

///

/// Gets an integer from the datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public virtual int GetInt32(int i)
{
if (_dataReader.IsDBNull(i))
return -1;
else
return _dataReader.GetInt32(i);
}

///

/// Gets a double from the datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public double GetDouble(string name)
{
return GetDouble(_dataReader.GetOrdinal(name));
}

///

/// Gets a double from the datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public virtual double GetDouble(int i)
{
if (_dataReader.IsDBNull(i))
return 0;
else
return _dataReader.GetDouble(i);
}

///

/// Gets a from the datareader.
///

///
/// A null is converted into min possible date
/// See Chapter 5 for more details on the SmartDate class.
///
/// Name of the column containing the value.
//public Csla.SmartDate GetSmartDate(string name)
//{
// return GetSmartDate(_dataReader.GetOrdinal(name), true);
//}

/////

///// Gets a from the datareader.
/////

/////
///// A null is converted into the min possible date
///// See Chapter 5 for more details on the SmartDate class.
/////
///// Ordinal column position of the value.
//public virtual Csla.SmartDate GetSmartDate(int i)
//{
// return GetSmartDate(i, true);
//}

/////

///// Gets a from the datareader.
/////

/////
///// A null is converted into either the min or max possible date
///// depending on the MinIsEmpty parameter. See Chapter 5 for more
///// details on the SmartDate class.
/////
///// Name of the column containing the value.
/////
///// A flag indicating whether the min or max
///// value of a data means an empty date.
//public Csla.SmartDate GetSmartDate(string name, bool minIsEmpty)
//{
// return GetSmartDate(_dataReader.GetOrdinal(name), minIsEmpty);
//}

//public virtual Csla.SmartDate GetSmartDate(
// int i, bool minIsEmpty)
//{
// if (_dataReader.IsDBNull(i))
// return new Csla.SmartDate(minIsEmpty);
// else
// return new Csla.SmartDate(
// _dataReader.GetDateTime(i), minIsEmpty);
//}

///

/// Gets a Guid value from the datareader.
///

///
/// Returns Guid.Empty for null.
///
/// Ordinal column position of the value.
public System.Guid GetGuid(string name)
{
return GetGuid(_dataReader.GetOrdinal(name));
}

///

/// Gets a Guid value from the datareader.
///

///
/// Returns Guid.Empty for null.
///
/// Name of the column containing the value.
public virtual System.Guid GetGuid(int i)
{
if (_dataReader.IsDBNull(i))
return Guid.Empty;
else
return _dataReader.GetGuid(i);
}

///

/// Reads the next row of data from the datareader.
///

public bool Read()
{
return _dataReader.Read();
}

///

/// Moves to the next result set in the datareader.
///

public bool NextResult()
{
return _dataReader.NextResult();
}

///

/// Closes the datareader.
///

public void Close()
{
_dataReader.Close();
}

///

/// Returns the depth property value from the datareader.
///

public int Depth
{
get
{
return _dataReader.Depth;
}
}

///

/// Returns the FieldCount property from the datareader.
///

public int FieldCount
{
get
{
return _dataReader.FieldCount;
}
}

///

/// Gets a boolean value from the datareader.
///

///
/// Returns for null.
///
/// Ordinal column position of the value.
public bool GetBoolean(string name)
{
return GetBoolean(_dataReader.GetOrdinal(name));
}

///

/// Gets a boolean value from the datareader.
///

///
/// Returns for null.
///
/// Name of the column containing the value.
public virtual bool GetBoolean(int i)
{
if (_dataReader.IsDBNull(i))
return false;
else
return _dataReader.GetBoolean(i);
}

///

/// Gets a byte value from the datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public byte GetByte(string name)
{
return GetByte(_dataReader.GetOrdinal(name));
}

///

/// Gets a byte value from the datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public virtual byte GetByte(int i)
{
if (_dataReader.IsDBNull(i))
return 0;
else
return _dataReader.GetByte(i);
}

///

/// Invokes the GetBytes method of the underlying datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public Int64 GetBytes(string name, Int64 fieldOffset,
byte[] buffer, int bufferoffset, int length)
{
return GetBytes(_dataReader.GetOrdinal(name), fieldOffset, buffer, bufferoffset, length);
}

///

/// Invokes the GetBytes method of the underlying datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public virtual Int64 GetBytes(int i, Int64 fieldOffset,
byte[] buffer, int bufferoffset, int length)
{
if (_dataReader.IsDBNull(i))
return 0;
else
return _dataReader.GetBytes(i, fieldOffset, buffer, bufferoffset, length);
}

///

/// Gets a char value from the datareader.
///

///
/// Returns Char.MinValue for null.
///
/// Name of the column containing the value.
public char GetChar(string name)
{
return GetChar(_dataReader.GetOrdinal(name));
}

///

/// Gets a char value from the datareader.
///

///
/// Returns Char.MinValue for null.
///
/// Ordinal column position of the value.
public virtual char GetChar(int i)
{
if (_dataReader.IsDBNull(i))
return char.MinValue;
else
{
char[] myChar = new char[1];
_dataReader.GetChars(i, 0, myChar, 0, 1);
return myChar[0];
}
}

///

/// Invokes the GetChars method of the underlying datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public Int64 GetChars(string name, Int64 fieldoffset,
char[] buffer, int bufferoffset, int length)
{
return GetChars(_dataReader.GetOrdinal(name), fieldoffset, buffer, bufferoffset, length);
}

///

/// Invokes the GetChars method of the underlying datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public virtual Int64 GetChars(int i, Int64 fieldoffset,
char[] buffer, int bufferoffset, int length)
{
if (_dataReader.IsDBNull(i))
return 0;
else
return _dataReader.GetChars(i, fieldoffset, buffer, bufferoffset, length);
}

///

/// Invokes the GetData method of the underlying datareader.
///

/// Name of the column containing the value.
public IDataReader GetData(string name)
{
return GetData(_dataReader.GetOrdinal(name));
}

///

/// Invokes the GetData method of the underlying datareader.
///

/// Ordinal column position of the value.
public virtual IDataReader GetData(int i)
{
return _dataReader.GetData(i);
}

///

/// Invokes the GetDataTypeName method of the underlying datareader.
///

/// Name of the column containing the value.
public string GetDataTypeName(string name)
{
return GetDataTypeName(_dataReader.GetOrdinal(name));
}

///

/// Invokes the GetDataTypeName method of the underlying datareader.
///

/// Ordinal column position of the value.
public virtual string GetDataTypeName(int i)
{
return _dataReader.GetDataTypeName(i);
}

///

/// Gets a date value from the datareader.
///

///
/// Returns DateTime.MinValue for null.
///
/// Name of the column containing the value.
public virtual DateTime GetDateTime(string name)
{
return GetDateTime(_dataReader.GetOrdinal(name));
}

///

/// Gets a date value from the datareader.
///

///
/// Returns DateTime.MinValue for null.
///
/// Ordinal column position of the value.
public virtual DateTime GetDateTime(int i)
{
if (_dataReader.IsDBNull(i))
return DateTime.MinValue;
else
return _dataReader.GetDateTime(i);
}

///

/// Gets a decimal value from the datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public decimal GetDecimal(string name)
{
return GetDecimal(_dataReader.GetOrdinal(name));
}

///

/// Gets a decimal value from the datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public virtual decimal GetDecimal(int i)
{
if (_dataReader.IsDBNull(i))
return 0;
else
return _dataReader.GetDecimal(i);
}

///

/// Invokes the GetFieldType method of the underlying datareader.
///

/// Name of the column containing the value.
public Type GetFieldType(string name)
{
return GetFieldType(_dataReader.GetOrdinal(name));
}

///

/// Invokes the GetFieldType method of the underlying datareader.
///

/// Ordinal column position of the value.
public virtual Type GetFieldType(int i)
{
return _dataReader.GetFieldType(i);
}

///

/// Gets a Single value from the datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public float GetFloat(string name)
{
return GetFloat(_dataReader.GetOrdinal(name));
}

///

/// Gets a Single value from the datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public virtual float GetFloat(int i)
{
if (_dataReader.IsDBNull(i))
return 0;
else
return _dataReader.GetFloat(i);
}

///

/// Gets a Short value from the datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public short GetInt16(string name)
{
return GetInt16(_dataReader.GetOrdinal(name));
}

///

/// Gets a Short value from the datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public virtual short GetInt16(int i)
{
if (_dataReader.IsDBNull(i))
return 0;
else
return _dataReader.GetInt16(i);
}

///

/// Gets a Long value from the datareader.
///

///
/// Returns 0 for null.
///
/// Name of the column containing the value.
public Int64 GetInt64(string name)
{
return GetInt64(_dataReader.GetOrdinal(name));
}

///

/// Gets a Long value from the datareader.
///

///
/// Returns 0 for null.
///
/// Ordinal column position of the value.
public virtual Int64 GetInt64(int i)
{
if (_dataReader.IsDBNull(i))
return 0;
else
return _dataReader.GetInt64(i);
}

///

/// Invokes the GetName method of the underlying datareader.
///

/// Ordinal column position of the value.
public virtual string GetName(int i)
{
return _dataReader.GetName(i);
}

///

/// Gets an ordinal value from the datareader.
///

/// Name of the column containing the value.
public int GetOrdinal(string name)
{
return _dataReader.GetOrdinal(name);
}

///

/// Invokes the GetSchemaTable method of the underlying datareader.
///

public DataTable GetSchemaTable()
{
return _dataReader.GetSchemaTable();
}

///

/// Invokes the GetValues method of the underlying datareader.
///

public int GetValues(object[] values)
{
return _dataReader.GetValues(values);
}

///

/// Returns the IsClosed property value from the datareader.
///

public bool IsClosed
{
get
{
return _dataReader.IsClosed;
}
}

///

/// Invokes the IsDBNull method of the underlying datareader.
///

/// Name of the column containing the value.
public virtual bool IsDBNull(int i)
{
return _dataReader.IsDBNull(i);
}

///

/// Returns a value from the datareader.
///

/// Name of the column containing the value.
public object this[string name]
{
get
{
object val = _dataReader[name];
if (DBNull.Value.Equals(val))
return null;
else
return val;
}
}

///

/// Returns a value from the datareader.
///

/// Ordinal column position of the value.
public virtual object this[int i]
{
get
{
if (_dataReader.IsDBNull(i))
return null;
else
return _dataReader[i];
}
}
///

/// Returns the RecordsAffected property value from the underlying datareader.
///

public int RecordsAffected
{
get
{
return _dataReader.RecordsAffected;
}
}

#region IDisposable Support

private bool _disposedValue; // To detect redundant calls

// IDisposable
protected virtual void Dispose(bool disposing)
{
if (!_disposedValue)
{
if (disposing)
{
// free unmanaged resources when explicitly called
_dataReader.Dispose();
}

// free shared unmanaged resources
if (_disposer != null)
{
_disposer.Dispose();
}
}
_disposedValue = true;
}

// This code added by Visual Basic to correctly implement the disposable pattern.
public void Dispose()
{
// Do not change this code. Put cleanup code in Dispose(bool disposing) above.
Dispose(true);
GC.SuppressFinalize(this);
}

#endregion

}
}
————–
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Repositories.ManageSQL
{
///

/// Class to call an Action when disposing.
/// Useful for returning objects for use in using() clauses
///

public class DisposeHelper : IDisposable
{
private Action OnDispose { get; set; }

public DisposeHelper(Action onDispose)
{
this.OnDispose = onDispose;
}

public void Dispose()
{
this.OnDispose();
}
}
}
—————

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Repositories.ManageSQL
{
///

/// Base class for DbHelpers, with strongly typed Connection, Command, and ParameterCollection
/// clases.
/// Strong typing allows us to take advantage of convenience methods like SqlParameterCollection.AddWithValue
///

///
///
///
public abstract class DbHelper
where TConnection : class, IDbConnection
where TCommand : class, IDbCommand
where TParameterCollection : class, IDataParameterCollection
{
public string ConnectionString { get; protected set; }
protected TConnection SharedConnection { get; set; }

protected abstract TCommand CreateCommand(string cmdText, TConnection connection);
protected abstract TConnection CreateConnection(string connectionString);
protected abstract IDbDataAdapter CreateDataAdapter(TCommand cmd);

public DbHelper(string connectionString)
{
this.ConnectionString = connectionString;
}

///

/// Open a shared connection for use in transactions
///

///
public virtual IDisposable BeginSharedConnection()
{
if (this.SharedConnection != null)
{
throw new InvalidOperationException(“Shared connection already in progress!”);
}

this.SharedConnection = CreateConnection(this.ConnectionString);
this.SharedConnection.Open();

return new DisposeHelper(() =>
{
this.SharedConnection.Dispose();
this.SharedConnection = null;
});
}

// Execute command with no parameters, and no return
public void ExecuteNonQuery(CommandType cmdType, string cmdText)
{
ExecuteNonQuery(cmdType, cmdText, null);
}
// Execute command with parameters, and no return
public void ExecuteNonQuery(CommandType cmdType, string cmdText, Action paramAction)
{
ExecuteNonQuery(cmdType, cmdText, paramAction, c => true);
}
// Execute command with parameters, and a return value (from an output SqlParameter)
public T ExecuteNonQuery(CommandType cmdType, string cmdText, Action paramAction, Func returnValue)
{
return ExecuteNonQuery(cmdType, cmdText, null, paramAction, returnValue);
}
// Execute command with parameters, and a return value (from an output SqlParameter).
// Also gives full access to the SqlCommand object to set other properties, eg., timeouts
public virtual T ExecuteNonQuery(CommandType cmdType, string cmdText, Action cmdAction, Action paramAction, Func returnValue)
{
if (returnValue == null) throw new ArgumentNullException(“returnValue”);

var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
try
{
using (var cmd = CreateCommand(cmdText, conn))
{
cmd.CommandType = cmdType;
if (cmdAction != null) cmdAction(cmd);
if (paramAction != null) paramAction((TParameterCollection)cmd.Parameters);

if (this.SharedConnection != conn) conn.Open();
try
{
cmd.ExecuteNonQuery();
return returnValue(cmd);
}
finally
{
if (this.SharedConnection != conn) conn.Close();
}
}
}
finally
{
if (this.SharedConnection != conn) conn.Dispose();
}
}

// Execute a scalar command, with parameters (or null if no parameters).
// convertValue must handle converting from DBNull.Value
public virtual T ExecuteScalar(CommandType cmdType, string cmdText, Action paramAction, Func convertValue)
{
if (convertValue == null) throw new ArgumentNullException(“convertValue”);

var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
try
{
using (var cmd = CreateCommand(cmdText, conn))
{
cmd.CommandType = cmdType;
if (paramAction != null) paramAction((TParameterCollection)cmd.Parameters);

if (this.SharedConnection != conn) conn.Open();
try
{
return convertValue(cmd.ExecuteScalar());
}
finally
{
if (this.SharedConnection != conn) conn.Close();
}
}
}
finally
{
if (this.SharedConnection != conn) conn.Dispose();
}
}

// Execute a command returning a single row in resultset.
// map Func gets access to IDataReader to map values from IDataReader to a strongly typed (or anonymous) object
// If no rows are returned from resultset, returns default(T)
public virtual T ExecuteSingle(CommandType cmdType, string cmdText, Action paramAction, Func map)
{
var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
try
{
using (var cmd = CreateCommand(cmdText, conn))
{
cmd.CommandType = cmdType;
if (paramAction != null) paramAction((TParameterCollection)cmd.Parameters);

if (this.SharedConnection != conn) conn.Open();
try
{
using (var dr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (!dr.Read())
{
return default(T);
}
return map(dr);
}
}
finally
{
if (this.SharedConnection != conn) conn.Close();
}
}
}
finally
{
if (this.SharedConnection != conn) conn.Dispose();
}
}

// Execute a command returning multiple rows in resultset.
// map Func gets access to IDataReader to map values from IDataReader to a strongly typed (or anonymous) object
// If no rows are returned from resultset, returns empty set
public virtual IEnumerable ExecuteSet(CommandType cmdType, string cmdText, Action paramAction, Func map)
{
var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
try
{
using (var cmd = CreateCommand(cmdText, conn))
{
cmd.CommandType = cmdType;
if (paramAction != null) paramAction((TParameterCollection)cmd.Parameters);

if (this.SharedConnection != conn) conn.Open();
try
{
using ( var dr = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
while (dr.Read())
{
yield return map(dr);
}
}
}
finally
{
if (this.SharedConnection != conn) conn.Close();
}
}
}
finally
{
if (this.SharedConnection != conn) conn.Dispose();
}
}

public DataTable FillDataTable(CommandType cmdType, string cmdText)
{
return FillDataTable(cmdType, cmdText, null);
}
public DataTable FillDataTable(CommandType cmdType, string cmdText, Action paramAction)
{
return FillDataSet(cmdType, cmdText, paramAction).Tables[0];
}
public DataSet FillDataSet(CommandType cmdType, string cmdText)
{
return FillDataSet(cmdType, cmdText, null);
}
public virtual DataSet FillDataSet(CommandType cmdType, string cmdText, Action paramAction)
{
var conn = this.SharedConnection ?? CreateConnection(this.ConnectionString);
try
{
using (var cmd = CreateCommand(cmdText, conn))
using (var da = (IDisposable)CreateDataAdapter(cmd))
{
cmd.CommandType = cmdType;
if (paramAction != null) paramAction((TParameterCollection)cmd.Parameters);

var ds = new DataSet();
if (this.SharedConnection != conn) conn.Open();
try
{
((IDbDataAdapter)da).Fill(ds);
return ds;
}
finally
{
if (this.SharedConnection != conn) conn.Close();
}
}
}
finally
{
if (this.SharedConnection != conn) conn.Dispose();
}
}
}
}
———–
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;

namespace Repositories.ManageSQL
{
public enum ColumnMatchingMode
{
Ordinal = 0,
CaseSensitiveName = 1
}
}
————–
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using Repositories.ManageSQL;

namespace Repositories
{
public abstract class BaseRepository
{
protected SqlHelper sqlH { get; set; }

protected BaseRepository()
: this(SqlHelper.GetConnectionString(“Connection”))
{
}

protected BaseRepository(string connectionString)
: this(new SqlHelper(connectionString))
{
}

protected BaseRepository(SqlHelper sqlHelper)
{
this.sqlH = sqlHelper;
}

}
}

syn


pkverma
p@ssw0rd1111
pkverma@sampatti.com
SynapseIndia-
Uname : Emp0743
Password : p@ssword1111
Data Source = 103.246.249.54,35333
DataBase = KidTrackerRestore
User ID = NewKidTrackerLogin
Password = New!KidTracker32

 http://192.168.1.141/svn/MS/Robert
ProjectName : Kid Tracker
dbname : aspnet-OnlineRegistration-20140303145529

occfinance
conor@occfinance.com
floor62014