razee

razee

  • NA
  • 7
  • 0

Please help me to improve this ADO.NET code for generic Database operation.

Jul 20 2008 6:24 AM
This is the code which is intended to be used for connection to, manipulating database content.
Please try to improve the code.
Keep the core structural concept unchanged.

using System;
using System.Data;
using System.Data.SqlClient;

namespace MyDatabaseManupulationNamespace
{
    // static clss for manipulating Database
    public static class Database
    {
        private static string myConnectionString;
        private static string mySelectQueryString;
        private static string myGeneralQueryString;
        private static string myHost;
        private static string myDatabase;
        private static string myUser;
        private static string myPassword;
        private static string myTableName;
        
        private static SqlConnection myConnection;
        private static SqlCommand myCommand;
        private static SqlDataAdapter myDataAdapter;
        private static DataSet myDataSet;

        private static int rows = 0;

        private static bool successful = true;

        // property for setting new query
        public static string Query
        {
            set
            {
                myGeneralQueryString = value;
            }
            get
            {
                return myGeneralQueryString;
            }
        }
       
        // number of affected rows
        public static int AffectedRows
        {
            get
            {
                return rows;
            }
        }

        // gets the DataSet
        public static DataSet DataSetValue
        {
            get
            {
                return myDataSet;
            }
        }

        // gets the DataAdapter
        public static SqlDataAdapter DataAdapter
        {
            get
            {
                return myDataAdapter;
            }
        }

        // static constructor for initializing
        // static attributes

        static Database()
        {
            mySelectQueryString = @"SELECT * FROM ";

            myConnection = new SqlConnection();
            myCommand = new SqlCommand();
            myDataAdapter = new SqlDataAdapter();
            
            myDataSet = new DataSet();
        }

        // Methosd for establishing connection
        public static bool EstablishConnection(string host, string database, string user, string password, string table)
        {
            myHost = host;
            myDatabase = database;
            myUser = user;
            myPassword = password;
            myTableName = table;

            myConnectionString = "Data Source=" + myHost + ";Initial Catalog=" + myDatabase + ";User ID=" + myUser + ";Password=" + myPassword + ";Integrated Security=True";
            
            mySelectQueryString += myTableName;
            
            myGeneralQueryString = mySelectQueryString;

            try
            {
                myConnection.ConnectionString = myConnectionString;
                myConnection.Open();

                myCommand.Connection = myConnection;
                myCommand.CommandText = myGeneralQueryString;

                myDataAdapter.SelectCommand = myCommand;

                rows = myDataAdapter.Fill(myDataSet, myTableName);

                successful = true;
            }
            catch
            {
                successful = false;
                
                throw new DatabaseException("Connection Failed!");
            }
            finally
            {
                myConnection.Close();
            }

            return successful;
        }

        // Method for restoring select query
        public static void RestoreDefaultQuery()
        {
            myGeneralQueryString = mySelectQueryString;
            
            Console.WriteLine(myGeneralQueryString);
        }
       
        // Method for executing query

        public static bool ExecuteQuery()
        {
            try
            {
                myConnection.Open();

                myCommand.CommandText = myGeneralQueryString;

                myDataSet.Reset();
                
                rows = myDataAdapter.Fill(myDataSet, myTableName);

                successful = true;
            }
            catch
            {
                successful = false;
                
                throw new DatabaseException("SQL Query Failed!");
            }
            finally
            {
                myConnection.Close();
            }

            return successful;
        }       
    }
}



Answers (2)