Code Snippet Details

<- Back to List


Language
C#.Net
Description
SqlServerDataProvider
Code
using System; using System.Data; using System.Data.SqlClient; namespace Alphabet.Framework.DAL { /// <summary> /// Data Provider for SQL Server databases. /// </summary> public class SqlServerDataProvider : IDataProvider { private const string NotNeeded = "This method is not needed in this data provider as the connection is opened and closed in the main methods."; #region Constants private const int CommandTimeout = 120; #endregion #region Member variables private string _ConnectionString; #endregion #region Constructor public SqlServerDataProvider(string connectionString) { _ConnectionString = connectionString; } #endregion #region Public Methods public void OpenConnection() { throw new NotImplementedException(NotNeeded); } public void CloseConnection() { throw new NotImplementedException(NotNeeded); } public System.Data.DataTable GetDataTable(string sql) { throw new NotImplementedException(NotNeeded); } /// <summary> /// Call a stored procedure and return a data table, the results cursor parameter value is added automatically. /// </summary> /// <param name="genericCommand">Generic command to run.</param> /// <returns>A data table.</returns> public DataTable GetDataTable(GenericCommand genericCommand) { try { using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(_ConnectionString)) { SqlCommand command = GetCommand(genericCommand, connection); connection.Open(); DataTable dataTable = GetDataTableUsingDataReader(command); connection.Close(); return dataTable; } } catch { throw; } } /// <summary> /// Returns the first value from the first row of a recordset. /// </summary> /// <param name="genericCommand">Generic command to run.</param> /// <returns>An object containing the returned value.</returns> public object GetScalarValue(GenericCommand genericCommand) { try { object returnValue = null; using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(_ConnectionString)) { SqlCommand command = GetCommand(genericCommand, connection); connection.Open(); returnValue = command.ExecuteScalar(); connection.Close(); } return returnValue; } catch { throw; } } /// <summary> /// Returns the first value from the first row of a recordset. /// </summary> /// <param name="sql">SQL command to run.</param> /// <returns>An object containing the returned value.</returns> public object GetScalarValue(string sql) { try { object returnValue = null; using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(_ConnectionString)) { SqlCommand command = GetCommand(sql, connection); connection.Open(); returnValue = command.ExecuteScalar(); connection.Close(); } return returnValue; } catch { throw; } } /// <summary> /// Run a stored procedure /// </summary> /// <param name="genericCommand">Generic command to run.</param> /// <returns>The number of rows affected</returns> public int ExecuteStoredProcedure(ref GenericCommand genericCommand) { try { int rowsAffected = 0; using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(_ConnectionString)) { SqlCommand command = GetCommand(genericCommand, connection); connection.Open(); rowsAffected = command.ExecuteNonQuery(); connection.Close(); } return rowsAffected; } catch { throw; } } public int ExecuteStoredProcedure(string sql) { try { int rowsAffected = 0; using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(_ConnectionString)) { SqlCommand command = GetCommand(sql, connection); connection.Open(); rowsAffected = command.ExecuteNonQuery(); connection.Close(); } return rowsAffected; } catch { throw; } } #endregion #region Private methods private SqlCommand GetCommand(string sql, SqlConnection _Connection) { try { SqlCommand command = new SqlCommand(sql); command.Connection = _Connection; command.CommandType = CommandType.Text; command.CommandTimeout = CommandTimeout; return command; } catch { throw; } } private SqlCommand GetCommand(GenericCommand genericCommand, SqlConnection connection) { try { SqlCommand command = new SqlCommand(genericCommand.CommandText); command.Connection = connection; command.CommandType = CommandType.StoredProcedure; command.CommandTimeout = CommandTimeout; foreach (GenericParameter genericParameter in genericCommand.GenericParameterValues()) { SqlParameter parameter = new SqlParameter(); parameter.ParameterName = genericParameter.Name; parameter.Value = genericParameter.Value; parameter.Direction = genericParameter.Direction; command.Parameters.Add(parameter); } return command; } catch { throw; } } /// <remarks>This is the fastest way to get a dataTable.</remarks> private DataTable GetDataTableUsingDataReader(SqlCommand command) { try { DataTable dataTable = new DataTable(); using (SqlDataReader reader = command.ExecuteReader()) { dataTable.Load(reader, LoadOption.OverwriteChanges); } return dataTable; } catch { throw; } } /// <remarks>This is another way to get a dataTable.</remarks> private DataTable GetDataTableUsingDataAdapter(SqlCommand command) { try { DataTable dataTable = new DataTable(); SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = command; dataAdapter.Fill(dataTable); return dataTable; } catch { throw; } } #endregion #region Transaction Procedures public void BeginTransaction() { throw new NotImplementedException(); } public void RollbackTransaction() { throw new NotImplementedException(); } public void CommitTransaction() { throw new NotImplementedException(); } #endregion } }