Click here to Skip to main content
15,889,403 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a static class DBO which contains static method GetConnection for establishing
connection, static method IUD for insert, update, delete and static method GetTable for retrieving data.Is using static for Crud appropriate if not what might I use.

What I have tried:

public static class DBO
   {

       public static SqlConnection GetConnection()
       {
           SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["myconnection"].ConnectionString);
           if (con.State != ConnectionState.Open)
           {
               con.Open();
           }
           return con;
       }

       public static int IUD(string sql, SqlParameter[] param, CommandType cmdType)
       {
           using (SqlConnection con = GetConnection())
           {

               SqlCommand cmd = new SqlCommand(sql, con);
               cmd.CommandType = cmdType;

               if (param != null)
               {
                   cmd.Parameters.AddRange(param);
               }
               try
               {
                   return cmd.ExecuteNonQuery();
               }
               catch (Exception ex)
               {

                   throw ex;
               }

           }
       }
       public static DataTable GetTable(string sql, SqlParameter[] param, CommandType cmdType)
       {
           using (SqlConnection con = GetConnection())
           {
               using (SqlCommand cmd = new SqlCommand(sql, con))
               {
                   cmd.CommandType = cmdType;

                   if (param != null)
                   {
                       cmd.Parameters.AddRange(param);
                   }
                   SqlDataAdapter da = new SqlDataAdapter(cmd);

                   DataTable dt = new DataTable();
                   da.Fill(dt);

                   return dt;

               }
           }
       }
Posted
Updated 4-Jul-17 5:47am
Comments
PIEBALDconsult 4-Jul-17 12:40pm    
No, what you show is too inflexible and limiting. Similarly, use the ADO.net interfaces (e.g. IDbConnection) rather than hard-coded concrete classes (e.g. SqlConnection).
https://www.codeproject.com/Articles/753789/Simplified-Database-Access-via-ADO-NET-Interfaces
Having said that, I'm now using a lot of Extension Methods for calling the ADO.net classes -- and they have to be static, so it really depends on _how_ you are using static methods.

I will also point out that the only good thing (in my opinion) about DataAdapters is that they will open and close the Connection for you -- you don't need to do that yourself.
Richard Deeming 4-Jul-17 13:34pm    
catch (Exception ex)
{
    throw ex;
}


Don't do that! You've just thrown away the stack trace of the exception.

If you really want to re-throw an exception, use:
catch (Exception ex)
{
    throw;
}


But, since you're not doing anything with the exception, you can just remove the try..catch block.
PIEBALDconsult 4-Jul-17 14:22pm    
Some people might _want_ to throw away the stack trace. I, on the other hand, prefer to include the original Exception as the InnerException of a new custom Exception.
I also prefer to add the CommandText and parameter names and values.

1 solution

Static is fine, the only issue you might have is if you want to implement unit testing down the line as your current code will render anything that uses it untestable. Also it's probably better for the calling methods to open the connection themselves rather than having GetConnection do it. You should have connections as short-lived as possible so it's better for client code to open the connection just before it is actually used.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900