Simple Data Access Layer against SQL Server

Please support our C# advertiser: Intel Parallel Studio Home
Ramy Mahrous Ramy Mahrous is offline Offline Jul 14th, 2009, 6:28 pm |
0
If you need to execute SQL Statements\Stored Procedures\UDFs which return\don't return results, Here I've developed this class to be used in Data Access Layer.
Show Printable Version Show Printable Version
Quick reply to this message  
Code Snippet
•
•
•
•
C# Syntax
Toggle Plain Text
  1. //This code developed by Ramy Mahrous 
  2. //ramyamahrous@hotmail.com
  3. //Its contents is provided "as is", without warranty.
  4.  
  5.  
  6. /// <summary>
  7. /// Acts as Data Access Layer for SQL Server
  8. /// </summary>
  9. public class DataAccessLayer
  10. {
  11. private static string SqlConnectionString = "Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DummyDatabase;Data Source=.;Connect Timeout=200;";
  12. //your connection string I place mine for illustration.. DON'T HARDLY WRITE IT, pass it as argument or add it in application configuration 
  13.         /// <summary>
  14.         /// Replaces every parameter with its value from 2D array
  15.         /// </summary>
  16.         /// <param name="query">Query</param>
  17.         /// <param name="parameters">Query parameters</param>
  18.         /// <returns>Query with parameters value to be executed against SQL Server Database</returns>
  19.         private static string SetParametersValue(string query, string[,] parameters)
  20.         {
  21.             for (int i = 0; i < parameters.Length / 2; i++)
  22.             {
  23.                 if (!string.IsNullOrEmpty(parameters[i, 0]))
  24.                     query = query.Replace(parameters[i, 0], "'" + parameters[i, 1] + "'");
  25.             }
  26.             return query;
  27.         }
  28.  
  29.         /// <summary>
  30.         /// 
  31.         /// </summary>
  32.         /// <param name="sp"></param>
  33.         /// <returns></returns>
  34.         public static object ExecuteScalar(string sp, CommandType commandType)
  35.         {
  36.             SqlConnection con = new SqlConnection(SqlConnectionString);
  37.             SqlCommand com = new SqlCommand(sp, con);
  38.             object result = null;
  39.  
  40.             com.CommandType = commandType;
  41.  
  42.             try
  43.             {
  44.                 con.Open();
  45.                 result = com.ExecuteScalar();
  46.                 con.Close();
  47.             }
  48.             catch (System.Exception ex)
  49.             {
  50.                 //log the exception
  51.             }
  52.             return result;
  53.         }
  54.  
  55.         /// <summary>
  56.         /// 
  57.         /// </summary>
  58.         /// <param name="sp"></param>
  59.         /// <param name="parameters"></param>
  60.         /// <returns></returns>
  61.         public static object ExecuteScalar(string sp, string[,] parameters, CommandType commandType)
  62.         {
  63.             SqlConnection con = new SqlConnection(SqlConnectionString);
  64.             SqlCommand com = new SqlCommand(SetParametersValue(sp, parameters), con);
  65.             object result = null;
  66.  
  67.  
  68.             com.CommandType = commandType;
  69.  
  70.             for (int i = 0; i < parameters.Length / 2; i++)
  71.             {
  72.                 com.Parameters.AddWithValue(parameters[i, 0], parameters[i, 1]);
  73.             }
  74.  
  75.             try
  76.             {
  77.                 con.Open();
  78.                 result = com.ExecuteScalar();
  79.                 con.Close();
  80.             }
  81.             catch (System.Exception ex)
  82.             {
  83.                 //log the exception
  84.             }
  85.             return result;
  86.         }
  87.  
  88.         /// <summary>
  89.         /// 
  90.         /// </summary>
  91.         /// <param name="sp"></param>
  92.         /// <returns></returns>
  93.         public static SqlDataReader ExecuteQuery(string sp, CommandType commandType)
  94.         {
  95.             SqlConnection con = new SqlConnection(SqlConnectionString);
  96.             SqlCommand com = new SqlCommand(sp, con);
  97.             SqlDataReader reader = null;
  98.  
  99.             com.CommandType = commandType;
  100.  
  101.             try
  102.             {
  103.                 con.Open();
  104.                 reader = com.ExecuteReader(CommandBehavior.CloseConnection);
  105.             }
  106.             catch (System.Exception ex)
  107.             {
  108.                 //log the exception
  109.             }
  110.             return reader;
  111.         }
  112.  
  113.         /// <summary>
  114.         /// FOR SQL
  115.         /// </summary>
  116.         /// <param name="sp"></param>
  117.         /// <param name="parameters"></param>
  118.         /// <returns></returns>
  119.         public static SqlDataReader ExecuteQuery(string sp, string[,] parameters, CommandType commandType)
  120.         {
  121.             SqlConnection con = new SqlConnection(SqlConnectionString);
  122.             SqlCommand com = new SqlCommand(SetParametersValue(sp, parameters), con);
  123.             SqlDataReader reader = null;
  124.  
  125.  
  126.             com.CommandType = commandType;
  127.  
  128.             for (int i = 0; i < parameters.Length / 2; i++)
  129.             {
  130.                 com.Parameters.AddWithValue(parameters[i, 0], parameters[i, 1]);
  131.             }
  132.  
  133.             try
  134.             {
  135.                 con.Open();
  136.                 reader = com.ExecuteReader(CommandBehavior.CloseConnection);
  137.             }
  138.             catch (System.Exception ex)
  139.             {
  140.                 //log the exception
  141.             }
  142.             return reader;
  143.         }
  144.  
  145.         /// <summary>
  146.         /// FOR SQL
  147.         /// </summary>
  148.         /// <param name="sp"></param>
  149.         /// <param name="parameters"></param>
  150.         /// <returns></returns>
  151.         public static void ExecuteNonQuery(string sp, CommandType commandType)
  152.         {
  153.             SqlConnection con = new SqlConnection(SqlConnectionString);
  154.             SqlCommand com = new SqlCommand(sp, con);
  155.  
  156.  
  157.             com.CommandType = CommandType.StoredProcedure;
  158.  
  159.             try
  160.             {
  161.                 con.Open();
  162.                 com.ExecuteNonQuery();
  163.                 con.Close();
  164.             }
  165.             catch (System.Exception ex)
  166.             {
  167.                 //log the exception
  168.             }
  169.         }
  170.  
  171.         /// <summary>
  172.         /// FOR SQL
  173.         /// </summary>
  174.         /// <param name="sp"></param>
  175.         /// <param name="parameters"></param>
  176.         /// <returns></returns>
  177.         public static void ExecuteNonQuery(string sp, string[,] parameters, CommandType commandType)
  178.         {
  179.             SqlConnection con = new SqlConnection(SqlConnectionString);
  180.             SqlCommand com = new SqlCommand(SetParametersValue(sp, parameters), con);
  181.  
  182.  
  183.             com.CommandType = commandType;
  184.  
  185.             for (int i = 0; i < parameters.Length / 2; i++)
  186.             {
  187.                 com.Parameters.AddWithValue(parameters[i, 0], parameters[i, 1]);
  188.             }
  189.  
  190.             try
  191.             {
  192.                 con.Open();
  193.                 com.ExecuteNonQuery();
  194.                 con.Close();
  195.             }
  196.             catch (System.Exception ex)
  197.             {
  198.                 //log the exception
  199.             }
  200.         }
  201.     }

Quick Comment
•
•
•
•
•
•
•
•
Message:
•
•
•
•


Similar Threads
Thread Tools Search this Thread



Tag cloud for C#
.net access ado.net algorithm array barchart bitmap box broadcast c# chat check checkbox class client color combobox control conversion csharp custom database datagrid datagridview dataset datetime degrees development draganddrop drawing encryption enum event excel file files form format forms ftp function gdi+ httpwebrequest image index input install java label list listbox listener login math mouseclick mysql networking object operator oracle path photoshop picturebox pixelinversion post prime programming radians regex remote remoting resource save saving serialization server sleep socket sql statistics stream string table tcp text textbox thread time timer treeview update usercontrol validation view visualstudio webbrowser windows winforms wpf xml
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC