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;
}
}
}
Vijaya KadiyalaPosted Aug 5, 2008, 4:11 PM
Hi,
Check out the below link,
This will give you tight piece of information on how you should build generic layer.
http://www.devcity.net/Articles/279/1/article.aspx
Thanks -- Vj
http://dotnetvj.blogspot.com
AlexPosted Aug 5, 2008, 7:14 AM
Another thing I would add to this class, is some kind of ExecuteStoredProcedure method. Something like this: (I haven't tested this, I just wrote it out, but you get the idea)
private DataSet ExecuteStoredProcedure(string procName, string[] paramNames,
object[] paramValues)
{
if (paramNames.Length != paramValues.Length)
{
//raise error
throw new ArgumentOutOfRangeException();
}
SqlConnection connection = new SqlConnection("myconnectionstring");
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < paramNames.Length; i++)
{
cmd.Parameters.AddWithValue(paramNames[i], paramValues[i]);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception ex)
{
//either rethrow exception or whatever
}
finally
{
connection.Close();
connection.Dispose();
cmd.Dispose();
adapter.Dispose();
}
return ds;
}
The nice thing about doing it this way is that you can call this method simply by saying:
DataSet ds = ExecuteStoredProcedure("getOrders",new string[]{"@orderID,@orderDate"},new object[]{1,DateTime.Now});
Or something like that. You get the point.