How to implement the cdatabase without using constructor
I am using cdatabase to implement the project. In cdatabase i am using constructor to open the connection string that is correct working but i want to implement the cdatabase with out using constructor
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Resources;
using Microsoft.Win32;
using System.Windows.Forms;
namespace PayrollDemo
{
class CDatabase
{
public SqlDataAdapter myAdapter;
public SqlConnection conn;
public SqlCommand myCommand;
public DataTable dataTable;
public DataSet dataSet = new DataSet();
public SqlDataReader myReader;
public static string conStr = "Data Source=SYSTEM33;Initial Catalog=PayRollDemo;Integrated Security=True";
/// <constructor>
/// Initialise Connection
/// </constructor>
///
public CDatabase()
{
try
{
myAdapter = new SqlDataAdapter();
conn = new SqlConnection(conStr);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <method>
/// Open Database Connection if Closed or Broken
/// </method>
public static void setPath(string path)
{
conStr = path;
}
private SqlConnection openConnection()
{
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}
return conn;
}
/// <method>
/// Select Query
/// </method>
///
//public static void SetInRegistry(String key, String value)
//{
// RegistryKey regKey = Registry.CurrentUser;
// regKey = regKey.CreateSubKey("Software\\PayRoll\\PayRoll");
// regKey.SetValue(key ,value );
//}
//public static String GetInRegistry(String key, String defVal)
//{
// RegistryKey regKey = Registry.CurrentUser;
// regKey = regKey.CreateSubKey("Software\\PayRoll\\PayRoll");
// return regKey.GetValue(key, defVal).ToString();
//}
public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter)
{
SqlCommand myCommand = new SqlCommand();
DataTable dataTable = new DataTable();
dataTable = null;
DataSet ds = new DataSet();
try
{
myCommand.Connection = openConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myCommand.ExecuteNonQuery();
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(ds);
dataTable = ds.Tables[0];
}
catch (SqlException e)
{
Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
return null;
}
finally
{
}
return dataTable;
}
public DataTable executeSelectQueryNonParameter(String _query)
{
SqlCommand myCommand = new SqlCommand();
DataTable dataTable = new DataTable();
dataTable = null;
DataSet ds = new DataSet();
try
{
myCommand.Connection = openConnection();
myCommand.CommandText = _query;
myCommand.ExecuteNonQuery();
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(ds);
dataTable = ds.Tables[0];
}
catch (SqlException e)
{
Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
return null;
}
finally
{
}
return dataTable;
}
/// <method>
/// Insert Query
/// </method>
public bool executeInsertQuery(String _query, SqlParameter[] sqlParameter)
{
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.Connection = openConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myAdapter.InsertCommand = myCommand;
myCommand.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.Write("Error - Connection.executeInsertQuery - Query: " + _query + " \nException: \n" + e.StackTrace.ToString());
return false;
}
finally
{
}
return true;
}
/// <method>
/// Update Query
/// </method>
public bool executeUpdateQuery(String _query, SqlParameter[] sqlParameter)
{
SqlCommand myCommand = new SqlCommand();
try
{
myCommand.Connection = openConnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myAdapter.UpdateCommand = myCommand;
myCommand.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.Write("Error - Connection.executeUpdateQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
return false;
}
finally
{
}
return true;
}
public static bool AddInCombo(ComboBox cmb, String tblname, string colName)
{
try
{
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "select " + colName + " from " + tblname;
SqlDataReader dr = cmd.ExecuteReader();
cmb.Items.Clear(); // First clear the existing items
while (dr.Read())
{
cmb.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
cmd.Dispose(); // Clean memory (garbage collection)
con.Dispose();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return false;
}
public static void AddInCombo(String sql, ComboBox cmb)
{
try
{
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = sql;
SqlDataReader dr = cmd.ExecuteReader();
cmb.Items.Clear(); // First clear the existing items
while (dr.Read())
{
cmb.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
cmd.Dispose(); // Clean memory (garbage collection)
con.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static int GetNextNumber(String tblName, String colName)
{
int nextNum = 1;
try
{
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "select max(" + colName + ") from " + tblName;
Object ob = cmd.ExecuteScalar();
nextNum = (Int32.Parse(ob.ToString()) + 1);
con.Close();
cmd.Dispose();
con.Dispose();
return nextNum;
}
catch (Exception)
{
}
return nextNum;
}
public static int GetNextNumber(String sql)
{
int nextNum = 1;
try
{
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = sql;
Object ob = cmd.ExecuteScalar();
nextNum = (Int32.Parse(ob.ToString()) + 1);
con.Close();
cmd.Dispose();
con.Dispose();
return nextNum;
}
catch (Exception)
{
}
return nextNum;
}
public static bool ExecuteSQL(String sql)
{
try
{
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
con.Dispose();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return false;
}
public static String getEmployeeIdFormat()
{
SqlCommand cmd = new SqlCommand();
string sql = "select AutoEmployeeID,StartingNo,EmployeeIDFormat,StringSuffix from EmployeeIDFormat_Master";
string connectionstr = conStr;
String NewCode = "";
try
{
int startingNo = 0;
int AutoEmployID = 0;
String NumFormat = "";
String StringPrefix = "";
SqlConnection con = new SqlConnection(connectionstr);
con.Open();
cmd.Connection = con;
cmd.CommandText = sql;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
startingNo = Int32.Parse(dr["StartingNo"].ToString());
AutoEmployID = Int32.Parse(dr["AutoEmployeeID"].ToString());
NumFormat = dr["EmployeeIDFormat"].ToString();
StringPrefix = dr["StringSuffix"].ToString();
}
dr.Close();
if (AutoEmployID == 1)
{
cmd.CommandText = "select Max(SrlNo) from EmpPersonalDetails";
dr = cmd.ExecuteReader();
int nextNum = 0;
if (dr.Read())
{
nextNum = Int32.Parse(dr[0].ToString());
}
dr.Close();
nextNum = nextNum + startingNo;
if (nextNum < 10)
NewCode = "00000" + nextNum.ToString();
else if (nextNum < 100)
NewCode = "0000" + nextNum.ToString();
else if (nextNum < 1000)
NewCode = "000" + nextNum.ToString();
else if (nextNum < 10000)
NewCode = "00" + nextNum.ToString();
else
NewCode = "0" + nextNum.ToString();
if (NumFormat == "Employee ID")
{
}
else if (NumFormat == "Employee ID-Year")
{
NewCode = NewCode + "-" + DateTime.Now.ToString("yy");
}
else if (NumFormat == "Employee ID/Year")
{
NewCode = NewCode + "/" + DateTime.Now.ToString("yy");
}
else if (NumFormat == "Year-Employee ID")
{
NewCode = DateTime.Now.ToString("yy") + "-" + NewCode;
}
else if (NumFormat == "Year/Employee ID")
{
NewCode = DateTime.Now.ToString("yy") + "/" + NewCode;
}
else if (NumFormat == "Company Code")
{
NewCode = StringPrefix + "-" + NewCode;
}
}
con.Close();
}
catch (Exception)
{
return "";
}
return NewCode;
}
public SqlDataReader SelectIntoComboBoxQuery(String _tblName, String _colName)
{
myCommand = new SqlCommand();
dataTable = new DataTable();
dataTable = null;
myReader = null;
try
{
myCommand.Connection = openConnection();
myCommand.CommandText = "select " + _colName + " from " + _tblName + "";
myReader = myCommand.ExecuteReader();
}
catch (SqlException e)
{
Console.Write("Error - Connection.executeSelectQuery - Query: " + myCommand.CommandText + " \nException: " + e.StackTrace.ToString());
return null;
}
finally
{
if (myReader != null)
myReader.Close();
}
return myReader;
}
public SqlDataReader SqlReaderQuery(String _sql)
{
myCommand = new SqlCommand();
dataTable = new DataTable();
dataTable = null;
try
{
myCommand.Connection = openConnection();
myCommand.CommandText = _sql;
myReader = myCommand.ExecuteReader();
}
catch (SqlException e)
{
Console.Write("Error - Connection.executeSelectQuery - Query: " + myCommand.CommandText + " \nException: " + e.StackTrace.ToString());
return null;
}
finally
{
}
return myReader;
}
public static int getAge(String dob)
{
DateTime dnow = DateTime.Today;
int d, m, y;
String[] tempDate = dob.Split('-');
d = Int32.Parse(tempDate[0]);
m = Int32.Parse(tempDate[1]);
y = Int32.Parse(tempDate[2]);
DateTime dfrom = new DateTime(y, m, d);
TimeSpan span = dfrom.Subtract(dnow);
return span.Days;
}
public static int GetYears(String StartingDate)
{
DateTime dnow = DateTime.Today;
int d, m, y;
String[] tempDate = StartingDate.Split('-');
d = Int32.Parse(tempDate[0]);
m = Int32.Parse(tempDate[1]);
y = Int32.Parse(tempDate[2]);
DateTime dfrom = new DateTime(y, m, d);
TimeSpan span = dfrom.Subtract(dnow);
return span.Days / 365;
}
}
}
// calling this method
try
{
string str = "insert into EmployeeFamilyDetails(EmpId,DependentName,Occupation,DateOfBirth,Relation,Age) values ('" + txtEmployeeId.Text + "','" + txtdependentname.Text + "','" + cmboccupation.Text + "','" + dtpdateofbirth.Value + "','" + cmbrelation.Text + "','" + txtfamilyAge.Text + "') ";
CDatabase.ExecuteSQL(str);
MessageBox.Show("Save Sucessfully");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
}