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;
}
}
}