2
Answers

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

Photo of razee

razee

17y
3.2k
1 WCF
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)