SP Class

using System.Data.SqlClient;
using System.Windows.Forms;


public class CustomerSP:DBConnection
{
public void AddDetails(CustomerInfo cinfo)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("AddCustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = cinfo.CustomerId;
par = cmd.Parameters.Add("@firstname", SqlDbType.VarChar);
par.Value = cinfo.FirstName;
par = cmd.Parameters.Add("@surname", SqlDbType.VarChar);
par.Value = cinfo.SurName;
par = cmd.Parameters.Add("@customeraddress", SqlDbType.VarChar);
par.Value = cinfo.CustomerAddrees;
par = cmd.Parameters.Add("@customertelephone", SqlDbType.VarChar);
par.Value = cinfo.CustomerTelephone;
par = cmd.Parameters.Add("@customermobile", SqlDbType.VarChar);
par.Value = cinfo.CustomerMobile;
par = cmd.Parameters.Add("@customeremail", SqlDbType.VarChar);
par.Value = cinfo.CustomerEmail;
par = cmd.Parameters.Add("@addressproofid", SqlDbType.Int);
par.Value = cinfo.AddressProofId;
par = cmd.Parameters.Add("@addressproofname", SqlDbType.VarChar);
par.Value = cinfo.AddressProofName;
par = cmd.Parameters.Add("@idproofid", SqlDbType.Int);
par.Value = cinfo.IdProofId;
par = cmd.Parameters.Add("@idproofname", SqlDbType.VarChar);
par.Value = cinfo.IdProofName;
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}

public DataTable ViewCustomerDetails(int customerid)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter("CustomerDetailsView", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = da.SelectCommand.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
da.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}

public DataTable ViewCustomerDetailsByIdInAdminSide(int customerid)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter("CustomerDetailsViewById", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = da.SelectCommand.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
da.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
public long CustomerIdGenerate()
{
long newnumber = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}

SqlCommand cmd = new SqlCommand("CustomerIdGenerate", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
newnumber = int.Parse(dr["Customer Id"].ToString());
}
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return newnumber;
}
public int CustomerCheck(int customerid)
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerCheck", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}


public int CustomerAddressProofNameCheck(string addressproofname)
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerAddressProofNameCheck", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@addressproofname", SqlDbType.VarChar);
par.Value = addressproofname;
count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}


public int CustomerIdProofNameCheck(string idproofname)
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerIdProofNameCheck", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@idproofname", SqlDbType.VarChar);
par.Value = idproofname;
count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}
public DataTable ViewAllCustomerDetails()
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter("CustomerDetailsViewAll", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
public DataTable CustomerDetailsPrint(int customerid)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter adapter = new SqlDataAdapter("CustomerDetailsPrint", con);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = adapter.SelectCommand.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
adapter.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
public void EditDetails(CustomerInfo cinfo)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("EditCustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = cinfo.CustomerId;
par = cmd.Parameters.Add("@firstname", SqlDbType.VarChar);
par.Value = cinfo.FirstName;
par = cmd.Parameters.Add("@surname", SqlDbType.VarChar);
par.Value = cinfo.SurName;
par = cmd.Parameters.Add("@customeraddress", SqlDbType.VarChar);
par.Value = cinfo.CustomerAddrees;
par = cmd.Parameters.Add("@customertelephone", SqlDbType.VarChar);
par.Value = cinfo.CustomerTelephone;
par = cmd.Parameters.Add("@customermobile", SqlDbType.VarChar);
par.Value = cinfo.CustomerMobile;
par = cmd.Parameters.Add("@customeremail", SqlDbType.VarChar);
par.Value = cinfo.CustomerEmail;
par = cmd.Parameters.Add("@addressproofid", SqlDbType.Int);
par.Value = cinfo.AddressProofId;
par = cmd.Parameters.Add("@addressproofname", SqlDbType.VarChar);
par.Value = cinfo.AddressProofName;
par = cmd.Parameters.Add("@idproofid", SqlDbType.Int);
par.Value = cinfo.IdProofId;
par = cmd.Parameters.Add("@idproofname", SqlDbType.VarChar);
par.Value = cinfo.IdProofName;
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
public int CustomerCheckByName(string customername)
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerCheckByName", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customername", SqlDbType.VarChar);
par.Value = customername;
count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}
public DataTable GetCustomerBySearch(string customername)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter("GetCustomerBySearch", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = da.SelectCommand.Parameters.Add("@customername", SqlDbType.VarChar);
par.Value = customername;
da.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
public int CustomerCount()
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}

SqlCommand cmd = new SqlCommand("CustomerCount", con);
cmd.CommandType = CommandType.StoredProcedure;

count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}
public void CustomerDelete(int customerid)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;

cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
public DataTable TransactionViewByCustomerId(int customerid)
{
DataTable dt=new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter adapter=new SqlDataAdapter("TransactionViewByCustomerId", con);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = adapter.SelectCommand.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
adapter.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
}