Data access class

[PureDevelopers]

We currently have a class that does all database actions, but as it is now, each method is creating its own connection. I am trying to add a constructor to the class to allow all methods to use a single connection. When I try to call the Open method of Conn in
the DataTable GetDataTable method, I get the following error:
"The name 'Conn' does not exist in the current context"
How do I make Conn accessible?
I have tried using static, private and public for the constructor, but nothing works.



public class Database
{

public Database()
{
Database GetEnvmtName = new Database();
string strConnString = "Data Source=" + GetEnvmtName.CheckServer() + "; Initial Catalog=" + ConfigurationManager.AppSettings["DefaultDatabase"].ToString() + ";";
SqlConnection Conn = new SqlConnection(strConnString);
}
public DataTable GetDataTable(string SQL, string strDBaseName)
{
try
{
Conn.Open();
DataTable dtGetDataTable = new DataTable();
SqlDataAdapter daGetData = new SqlDataAdapter(SQL, Conn);
daGetData.Fill(dtGetDataTable);
Conn.Close();
daGetData.Dispose();
dtGetDataTable.Dispose();
if (dtGetDataTable != null)
return dtGetDataTable;
else
return null;
}
catch
{
//connGlobal.Dispose();
return null;
}
}
}

 

[JurkMonkey]

public class Database
{
Database GetEnvmtName = new Database();
string strConnString = "Data Source=" + GetEnvmtName.CheckServer() + "; Initial Catalog=" + ConfigurationManager.AppSettings["DefaultDatabase"].ToString() + ";";
SqlConnection Conn = new SqlConnection(strConnString);

public Database()
{

}

public DataTable GetDataTable(string SQL, string strDBaseName)
{
try
{
Conn.Open();
DataTable dtGetDataTable = new DataTable();
SqlDataAdapter daGetData = new SqlDataAdapter(SQL, Conn);
daGetData.Fill(dtGetDataTable);
Conn.Close();
daGetData.Dispose();
dtGetDataTable.Dispose();
if (dtGetDataTable != null)
return dtGetDataTable;
else
return null;
}
catch
{
//connGlobal.Dispose();
return null;
}
}
}

 

[PureDevelopers]

The GetEnvmtName.CheckServer()
code is failing. I am getting the following error:
A field initializer cannot reference the nonstatic field, method, or property 'Database.GetEnvmtName'

The method is grabbing the appropriate datasource?

 

[JurkMonkey]

Wait one sec...

Database GetEnvmtName = new Database();

why do you have this? You are creating an instance of your own class? This would make it a singleton - which you don't want here.

Take that part out to start and let me know how that goes.

 

[PureDevelopers]

Awesome, thanks for the help. I didn't write the class, I am just trying to make it better, but the class should not be calling itself. I was having difficulty with the SqlConnection being used globally, but I finally figured it out. here is the finished class:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public class Database
{
SqlConnection connGlobal = new SqlConnection();

public Database()
{
string strConnString = "Data Source=" + CheckServer() + "; Initial Catalog=" + ConfigurationManager.AppSettings["DefaultDatabase"].ToString() + ";";
connGlobal.ConnectionString = strConnString;
}

~ Database()
{
connGlobal.Dispose();
}

/// <summary>
/// Use a SQL Statement and return a Dataset
/// </summary>
/// <param name="strSQL"></param>
/// <param name="strTableName"></param>
/// <param name="intPageSize"></param>
/// <param name="intCurrentIndex"></param>
/// <returns></returns>
public DataSet GetData(string strSQL, string strTableName, int intPageSize, int intCurrentIndex)
{
try
{
connGlobal.Open();
DataSet dsGetData = new DataSet(strTableName);
SqlDataAdapter daGetData = new SqlDataAdapter(strSQL, connGlobal);
daGetData.Fill(dsGetData, intCurrentIndex, intPageSize, strTableName);
connGlobal.Close();
daGetData.Dispose();
dsGetData.Dispose();
if (dsGetData != null)
return dsGetData;
else
return null;
}
catch
{
connGlobal.Close();
return null;
}
}

/// <summary>
/// Use a SQLCommand and return a Dataset
/// </summary>
/// <param name="strSQL"></param>
/// <param name="strTableName"></param>
/// <param name="intPageSize"></param>
/// <param name="intCurrentIndex"></param>
/// <returns></returns>
public DataSet GetData(SqlCommand cmd, string strTableName, int intPageSize, int intCurrentIndex)
{
try
{
connGlobal.Open();
cmd.Connection = connGlobal;
DataSet dsGetData = new DataSet();
SqlDataAdapter daGetData = new SqlDataAdapter(cmd);
daGetData.Fill(dsGetData, intCurrentIndex, intPageSize, strTableName);
connGlobal.Close();
daGetData.Dispose();
dsGetData.Dispose();
if (dsGetData != null)
return dsGetData;
else
return null;
}
catch
{
connGlobal.Close();
return null;
}
}

/// <summary>
/// Run a sql command and return a DataTable
/// </summary>
/// <param name="strSQL"></param>
/// <param name="strTableName"></param>
/// <param name="intPageSize"></param>
/// <param name="intCurrentIndex"></param>
/// <returns></returns>
public DataTable GetDataTable(SqlCommand cmd)
{
try
{
connGlobal.Open();
cmd.Connection = connGlobal;
DataTable dtGetDataTable = new DataTable();
SqlDataAdapter daGetData = new SqlDataAdapter(cmd);
daGetData.Fill(dtGetDataTable);
connGlobal.Close();
daGetData.Dispose();
dtGetDataTable.Dispose();
if (dtGetDataTable != null)
return dtGetDataTable;
else
return null;
}
catch
{
connGlobal.Close();
return null;
}
}

/// <summary>
/// Run a sql command and return a DataTable
/// </summary>
/// <param name="strSQL"></param>
/// <param name="strTableName"></param>
/// <param name="intPageSize"></param>
/// <param name="intCurrentIndex"></param>
/// <returns></returns>
public DataTable GetDataTable(string SQL)
{
try
{
connGlobal.Open();
DataTable dtGetDataTable = new DataTable();
SqlDataAdapter daGetData = new SqlDataAdapter(SQL, connGlobal);
daGetData.Fill(dtGetDataTable);
connGlobal.Close();
daGetData.Dispose();
dtGetDataTable.Dispose();
if (dtGetDataTable != null)
return dtGetDataTable;
else
return null;
}
catch
{
connGlobal.Close();
return null;
}
}

/// <summary>
/// Method that Executes an insert or update by passing in a command and a database name
/// </summary>
/// <param name="cmd">Command Object</param>
/// <returns></returns>
public int InsertUpdate(SqlCommand cmd)
{
int RowsAffected;

connGlobal.Open();
cmd.Connection = connGlobal;
RowsAffected = cmd.ExecuteNonQuery();

connGlobal.Close();
connGlobal.Close();
return RowsAffected;
}

/// <summary>
/// Perform an insert or update by passing in a sql string and database name
/// </summary>
/// <param name="strSQL">string to execute</param>
/// <returns></returns>
public int InsertUpdate(string strSQL)
{
int RowsAffected;

connGlobal.Open();
SqlCommand sqlInsUpd = new SqlCommand(strSQL, connGlobal);
SqlDataAdapter daInsUpd = new SqlDataAdapter(strSQL, connGlobal);

daInsUpd.UpdateCommand = sqlInsUpd;

RowsAffected = sqlInsUpd.ExecuteNonQuery();

connGlobal.Close();
daInsUpd.Dispose();
sqlInsUpd.Dispose();

return RowsAffected;
}

/// <summary>
///
/// </summary>
/// <param name="strSQL">sql string to execute</param>
/// <returns></returns>
public DataSet Delete(string strSQL)
{
connGlobal.Open();
DataSet dsDelete = new DataSet("Delete");
SqlDataAdapter daDelete = new SqlDataAdapter(strSQL, connGlobal);
daDelete.Fill(dsDelete, "Delete");

connGlobal.Close();
daDelete.Dispose();
dsDelete.Dispose();
if (dsDelete != null)
return dsDelete;
else
return null;
}

/// <summary>
///
/// </summary>
/// <returns></returns>
private string CheckServer()
{
string strSQLEnvmt = "";

if (HttpContext.Current.Request.ServerVariables["SERVER_NAME"].ToString().IndexOf("devdotnet") + 1 > 0)
{
strSQLEnvmt = "Webdev01; User ID=sa; pwd=coleman;";
}
else if (HttpContext.Current.Request.ServerVariables["SERVER_NAME"].ToString().IndexOf("webdev01") + 1 > 0)
{
strSQLEnvmt = "Webdev01; User ID=sa; pwd=coleman;";
}
else if (HttpContext.Current.Request.ServerVariables["SERVER_NAME"].ToString().IndexOf("localhost") + 1 > 0)
{
strSQLEnvmt = "Webdev01; User ID=sa; pwd=coleman;";
}
else if (HttpContext.Current.Request.ServerVariables["SERVER_NAME"].ToString().IndexOf("webstage01") + 1 > 0)
{
strSQLEnvmt = "WebStageSQL01; User ID=sa; pwd=coleman;";
}
else if (HttpContext.Current.Request.ServerVariables["SERVER_NAME"].ToString().IndexOf(".com") + 1 > 0)
{
strSQLEnvmt = "HAL; User ID=sa; pwd=coleman;";
}
else if (HttpContext.Current.Request.ServerVariables["SERVER_NAME"].ToString().IndexOf("

http://www.")

+ 1 > 0)
{
strSQLEnvmt = "HAL; User ID=sa; pwd=coleman;";
}
return strSQLEnvmt;
}

public DataSet GetData()
{
throw new Exception("The method or operation is not implemented.");
}
}