View Single Post
  #1 (permalink)  
Old July 20th, 2008, 05:12 AM
edurazee
Guest
 
Posts: n/a
Default Please help me to improve this ADO.NET code.

Quote:
quote:This is the code which is intended to be used for connection to, and, manipulating database content.
Please try to improve the code.
Keep the core structural concept unchanged.
Code:
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;
        }        
    }
}
Reply With Quote