using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
namespace WebApplication2
{
public class Class1
{
private string _StrConnectionString;
public string ConnectionString { get { return _StrConnectionString; } set { _StrConnectionString = @"data source="servername"; initial catalog="dbname"; user id="username"; password="password""; } }
SqlConnection SqlConn = new SqlConnection();
private void OpenConnection(string connectionstring)
{
try
{
if (SqlConn.State == ConnectionState.Open)
{
SqlConn.ConnectionString = connectionstring;
SqlConn.Open();
}
}
catch (Exception ex)
{
throw ex;
}
}
private void OpenConnection()
{
try
{
if (SqlConn.State == ConnectionState.Open)
{
SqlConn.ConnectionString = _StrConnectionString;
SqlConn.Open();
}
}
catch (Exception ex)
{
throw ex;
}
}
private void CloseConnection()
{
try
{
if (!(SqlConn.State == ConnectionState.Closed))
{
SqlConn.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet FillDataSet(string query, string strConnectionString)
{
DataSet ds = null;
try
{
ds = new DataSet();
OpenConnection(strConnectionString);
SqlDataAdapter da = new SqlDataAdapter(query, strConnectionString);
da.Fill(ds);
da.Dispose();
CloseConnection();
}
catch
{
CloseConnection();
}
return ds;
}
public string ExecuteScalar(string query)
{
try
{
OpenConnection();
SqlCommand SqlCmd = new SqlCommand(query, SqlConn);
string str = (string)SqlCmd.ExecuteScalar();
return str;
}
catch (Exception ex)
{
return ex.Message.ToString();
}
finally
{
CloseConnection();
}
}
public string ExecuteQuery(string query)
{
try
{
OpenConnection();
SqlCommand SQlcmd = new SqlCommand(query, SqlConn);
SQlcmd.ExecuteNonQuery();
return "";
}
catch (Exception ex)
{
return ex.Message.ToString();
}
finally
{
CloseConnection();
}
}
public DataTable GetDataTable(string query)
{
DataTable ObjDataTable = null;
SqlCommand Sqlcmd;
try
{
OpenConnection();
Sqlcmd = new SqlCommand(query, SqlConn);
SqlDataAdapter Sqladp = new SqlDataAdapter(Sqlcmd);
ObjDataTable = new DataTable();
Sqladp.Fill(ObjDataTable);
CloseConnection();
}
catch
{
CloseConnection();
}
return ObjDataTable;
}
public string ExecuteStoredProc(string strProcedureName, EmployeeProp objEmployeeProp)
{
try
{
SqlCommand SQlcmd = new SqlCommand(strProcedureName, SqlConn);
SQlcmd.CommandType = CommandType.StoredProcedure;
//SQlcmd.Parameters.Add(new SqlParameter("@Emp_Id", SqlDbType.BigInt)).Value = objEmployeeProp.Emp_Id;
//SQlcmd.Parameters.Add(new SqlParameter("@Emp_Id", SqlDbType.BigInt)).Direction = ParameterDirection.InputOutput;
SQlcmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar)).Value = objEmployeeProp.FirstName;
SQlcmd.Parameters.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = objEmployeeProp.LastName;
SqlParameter ParamId = SQlcmd.Parameters.Add("@Emp_Id", SqlDbType.Int);
ParamId.Direction = ParameterDirection.InputOutput;
SQlcmd.Parameters.Add(ParamId);
OpenConnection();
// int ID = SQlcmd.ExecuteNonQuery();
CloseConnection();
int ID = (int)ParamId.Value;
return ID.ToString();
}
catch (Exception ex)
{
throw ex;
}
}
public void ExecuteStoredProc2(string strProcedureName, EmployeeProp objEmployeeProp)
{
try
{
SqlCommand SQlcmd = new SqlCommand(strProcedureName, SqlConn);
SQlcmd.CommandType = CommandType.StoredProcedure;
SQlcmd.Parameters.Add(new SqlParameter("@Emp_Id", SqlDbType.BigInt)).Value = objEmployeeProp.Emp_Id;
SQlcmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar)).Value = objEmployeeProp.FirstName;
SQlcmd.Parameters.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = objEmployeeProp.LastName;
OpenConnection();
SQlcmd.ExecuteNonQuery();
CloseConnection();
}
catch (Exception ex)
{
throw ex;
}
}
}
public class EmployeeProp
{
private int _StrEmp_Id;
private string _StrFirstName;
private string _StrLastName;
public string FirstName { get { return FirstName; } set { FirstName = _StrFirstName; } }
public string LastName { get { return LastName; } set { LastName = _StrLastName; } }
public int Emp_Id { get { return _StrEmp_Id; } set { Emp_Id = _StrEmp_Id; } }
}
}