C# Tutorials and offshore development in India
    Tutorials   Resources   Forum   Communities   Interview   Jobs   Projects   Offshore Development    
Silverlight Tutorials | Mentor | Code Converter | Articles | Code Factory | Computer Jokes | Members | Peer Appraisal | IT Companies | Bookmarks | Revenue Sharing |


Prizes & Awards
My Profile



Active Members
TodayLast 7 Days more...
New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !




data access layer(DAL)


Posted Date: 06 May 2008    Resource Type: Code Snippets    Category: C# Syntax
Posted By: karthikeyan-The Great       Member Level: Gold
Rating:     Points: 15



using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.SessionState;
using System.Globalization;
using System.Web.Caching;
using System.Web;
using System.Web.Security;
using System.Collections;
using System.Text;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;


///
/// Summary description for DAL.
///
public class DAL
{
int inc;
public SqlConnection SqlConn;
SqlDataReader SqlDr;
DbConnection objConn;
public SqlCommand SqlComm;
SqlDataAdapter SqlDataAdr;
DataSet ds;
System.Web.UI.Page objPage;

///
/// Constructor
///
public DAL()
{
objConn = new DbConnection();

if(SqlConn == null || SqlConn.State == ConnectionState.Closed || SqlConn.State == ConnectionState.Broken)
SqlConn = objConn.OpenConnection();

objPage = new System.Web.UI.Page();
}

///
/// Closes Database Connection
///
public void CloseConnection()
{
objConn.CloseConnection(SqlConn);
GC.Collect();
GC.WaitForPendingFinalizers();
}

public DataSet GetDataSet(string strStoredProc, SqlParameter[] sqlParams)
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

foreach(SqlParameter sqlParam in sqlParams){SqlDataAdr.SelectCommand.Parameters.Add(sqlParam);}

ds = new DataSet();
SqlDataAdr.Fill(ds);

return ds;
}

///
/// Returns a Dataset (without input parameters)
///
/// Stored Procedure Name
/// DataSet
public DataSet GetDataSet(string strStoredProc)
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
SqlDataAdr.Fill(ds);

return ds;
}

///
/// Returns a Scalar
///
/// Stored Procedure Name
/// SqlParameters
///
public object GetScalar(string strStoredProc, SqlParameter[] sqlParams)
{
SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;

foreach(SqlParameter sqlParam in sqlParams){SqlComm.Parameters.Add(sqlParam);}

return SqlComm.ExecuteScalar();
}

public SqlDataReader GetReader(string strStoredProc, SqlParameter[] sqlParams)
{
SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;

foreach(SqlParameter sqlParam in sqlParams){SqlComm.Parameters.Add(sqlParam);}

return SqlComm.ExecuteReader();
}


public SqlDataReader GetReader(string strStoredProc)
{
SqlComm = new SqlCommand(strStoredProc, SqlConn);

return SqlComm.ExecuteReader();
}



///
/// Returns a Scalar (Without input parameters)
///
/// Stored Procedure Name
///
public object GetScalar(string strStoredProc)
{
SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;

return SqlComm.ExecuteScalar();
}

///
/// Executes a SP
///
/// Stored Procedure Name
/// SqlParameters
public void ExecuteSP(string strStoredProc, SqlParameter[] sqlParams)
{
SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;

foreach(SqlParameter sqlParam in sqlParams){SqlComm.Parameters.Add(sqlParam);}
DbConnection dbc = new DbConnection();
dbc.OpenConnection();
SqlComm.ExecuteNonQuery();


}
public void ExecuteSP(string strStoredProc)
{
SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;


DbConnection dbc = new DbConnection();
dbc.OpenConnection();
SqlComm.ExecuteNonQuery();

}
public void FillDropDown(string strStoredProc, SqlParameter[] sqlParams,DropDownList Ddl)
{
Ddl.Items.Clear();
Ddl.Items.Add("--Select--");
SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;

foreach(SqlParameter sqlParam in sqlParams){SqlComm.Parameters.Add(sqlParam);}

SqlDr=SqlComm.ExecuteReader();
while(SqlDr.Read())
{
Ddl.Items.Add(SqlDr[0].ToString());
}
SqlDr.Close();
Ddl.SelectedItem.Text="--Select--";

}
public void FillDropDown(string strStoredProc,DropDownList Ddl)
{
Ddl.Items.Clear();
Ddl.Items.Add("--Select--");
SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;

SqlDr=SqlComm.ExecuteReader();
while(SqlDr.Read())
{
Ddl.Items.Add(SqlDr[0].ToString());
}
SqlDr.Close();
Ddl.SelectedItem.Text="--Select--";

}
public void FillGrid(string strStoredProc, SqlParameter[] sqlParams,DataGrid DG)
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;

foreach(SqlParameter sqlParam in sqlParams){SqlDataAdr.SelectCommand.Parameters.Add(sqlParam);}

SqlDataAdr.Fill(ds);

DG.DataSource=ds.Tables[0];
DG.DataBind();
DG.Visible=true;

}
public void FillGrid(string strStoredProc, DataGrid DG)
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;

SqlDataAdr.Fill(ds);

DG.DataSource = ds.Tables[0];

DG.DataBind();
DG.Visible=true;
}
public void FillGridView(string strStoredProc, SqlParameter[] sqlParams, GridView GView)
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;

foreach (SqlParameter sqlParam in sqlParams) { SqlDataAdr.SelectCommand.Parameters.Add(sqlParam); }

SqlDataAdr.Fill(ds);
GView.DataSource = ds.Tables[0];
GView.DataBind();
GView.Visible = true;

}
public void FillGridView(string strStoredProc, GridView GView)
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;

SqlDataAdr.Fill(ds);
GView.DataSource = ds.Tables[0];
GView.DataBind();
GView.Visible = true;

}
public void FillReport(string strStoredProc,SqlParameter[] sqlParams,DataGrid DG)
{

SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;

foreach(SqlParameter sqlParam in sqlParams){SqlDataAdr.SelectCommand.Parameters.Add(sqlParam);}

SqlDataAdr.Fill(ds);

DG.DataSource=ds.Tables[0];
DG.DataBind();
DG.Visible=true;
}
public string ChechVal(string strStoredProc, SqlParameter[] sqlParams)
{
string res;
SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;

foreach(SqlParameter sqlParam in sqlParams){SqlComm.Parameters.Add(sqlParam);}

SqlDr=SqlComm.ExecuteReader();
if(SqlDr.Read())
{
res=SqlDr[0].ToString();
}
else
{
res="0";
}
SqlDr.Close();
return res;
}
public string FetchSingleValue(string strStoredProc, SqlParameter[] sqlParams)
{
string res = "0";
try
{

SqlComm = new SqlCommand(strStoredProc, SqlConn);
SqlComm.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter sqlParam in sqlParams) { SqlComm.Parameters.Add(sqlParam); }

SqlDr = SqlComm.ExecuteReader();
if (SqlDr.Read())
{
res = SqlDr[0].ToString();
}
else
{
res = "0";
}
SqlDr.Close();
}
catch(Exception ex)
{
msgboxcs.MessageBox.Show(ex.Message);
}
return res;

}
public string CheckVal(string strStoredProc)
{
string res;
SqlComm = new SqlCommand(strStoredProc, SqlConn);

SqlDr=SqlComm.ExecuteReader();
if(SqlDr.Read())
{
res=SqlDr[0].ToString();
}
else
{
res="0";
}
SqlDr.Close();
return res;
}
public int increment(string sp)
{
SqlDataReader dr;
dr= GetReader(sp);
if(dr.Read())
{
inc= Convert.ToInt16(dr[0])+1;
}
else
{
inc=1;
}
dr.Close();
return inc;
}
public void FillCrystalReports(string strStoredProc, SqlParameter[] sqlParams,string CRPPath,CrystalReportViewer CRPViewer)
{
CRPPath = System.Web.HttpContext.Current.Server.MapPath(CRPPath);

try
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;

foreach (SqlParameter sqlParam in sqlParams) { SqlDataAdr.SelectCommand.Parameters.Add(sqlParam); }


SqlDataAdr.Fill(ds);
ReportDocument Rep = new ReportDocument();
//Rep.SetDatabaseLogon("sa", "sa", ".", "SchoolDB");
Rep.Load(CRPPath);
Rep.SetDataSource(ds.Tables[0]);
//Rep.ParameterFields.Add(sqlparam);

CRPViewer.ReportSource = Rep;
if (ds.Tables[0].Rows.Count == 0)
{
CRPViewer.Visible = false;
msgboxcs.MessageBox.Show("No Records Available");
}

}
catch (Exception ex)
{
msgboxcs.MessageBox.Show(ex.Message);
}

}
public void FillCrystalReports(string strStoredProc, string CRPPath, CrystalReportViewer CRPViewer)
{
CRPPath=System.Web.HttpContext.Current.Server.MapPath(CRPPath);

try
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, SqlConn);
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;

ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;


SqlDataAdr.Fill(ds);
ReportDocument Rep = new ReportDocument();
//Rep.SetDatabaseLogon("sa", "sa", ".", "SchoolDB");
Rep.Load(CRPPath);
Rep.SetDataSource(ds.Tables[0]);
CRPViewer.PrintMode = CrystalDecisions.Web.PrintMode.ActiveX;
CRPViewer.ReportSource = Rep;
if (ds.Tables[0].Rows.Count == 0)
{
CRPViewer.Visible = false;
msgboxcs.MessageBox.Show("No Records Available");
}

}
catch (Exception ex)
{
msgboxcs.MessageBox.Show(ex.Message);
}
}
}



Responses
Author: aNiL    14 Nov 2008Member Level: Silver   Points : 1
hi ,
post is nice,

I want to know how to pass the paramater into
ExecuteSP(string strStoredProc, SqlParameter[] sqlParams) function . can u plz tell me any example
its urgent plz


Feedbacks      
Popular Tags   What are tags ?   Search Tags  
(No tags found.)

Post Feedback


This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.
Next Resource: Win32 Paused Services
Previous Resource: Read entries from Application log of system
Return to Discussion Resource Index
Post New Resource
Category: C# Syntax


Post resources and earn money! 		 
Related Resources


dotNet Slackers   BizTalk Adaptors    Web Design

SPOC

Contact Us    Privacy Policy    Terms Of Use