using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
///
/// Summary description for DAL
///
public class DAL
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter sda;
String ConString;
public DAL()
{
ConString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
con = new SqlConnection(ConString);
}
public void ConnectionOpen()
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
}
public void ConnectionClose()
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
public DataTable GetDataTable(String query)
{
con.Open();
sda = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
public int PutData(String query)
{
cmd = new SqlCommand();
cmd.CommandText = query;
cmd.Connection = con;
con.Open();
int a = cmd.ExecuteNonQuery();
con.Close();
return a;
}
public string ExecuteScaler(String query)
{
cmd = new SqlCommand();
cmd.CommandText = query;
cmd.Connection = con;
con.Open();
string s = Convert.ToString(cmd.ExecuteScalar());
con.Close();
return s;
}
public void FillCombo(DropDownList ddl, String query, String item, String Value, String ZerothElemnt, int SelectedIndex)
{
DataTable dt = new DataTable();
dt = GetDataTable(query);
ddl.DataSource = dt;
ddl.DataTextField = item;
ddl.DataValueField = Value;
ddl.DataBind();
ddl.Items.Insert(0, ZerothElemnt);
ddl.SelectedIndex = SelectedIndex;
}
public void FillCombo(DropDownList ddl, String query, String item, String Value, int SelectedIndex)
{
DataTable dt = new DataTable();
dt = GetDataTable(query);
ddl.DataSource = dt;
ddl.DataBind();
ddl.SelectedIndex = SelectedIndex;
}
public void FillGrid(GridView grd, String query)
{
DataTable dt = new DataTable();
dt = GetDataTable(query);
grd.DataSource = dt;
grd.DataBind();
}
public void FillDetailView(DetailsView dv, String query)
{
DataTable dt = new DataTable();
dt = GetDataTable(query);
dv.DataSource = dt;
dv.DataBind();
}
}
Other DAL
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
///
/// Summary description for DAL
///
public class DAL
{
public SqlConnection con = new SqlConnection(@"Data Source=localhost\SQLEXPRESS;initial Catalog=Demo1;integrated security=true;");
public SqlCommand cmd = new SqlCommand();
public SqlDataAdapter adp = new SqlDataAdapter();
public DataSet ds = new DataSet();
public DataTable dt = new DataTable();
public string sqlstr;
public DAL()
{
//
// TODO: Add constructor logic here
//
}
public void conopen()
{
if (con.State == ConnectionState.Closed)
con.Open();
}
public void insertfun(string str)
{
conopen();
cmd.Connection = con;
cmd.CommandText = str;
adp.InsertCommand = cmd;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
public void Updatefun(string str)
{
conopen();
cmd.Connection = con;
cmd.CommandText = str;
cmd.CommandType = CommandType.Text;
adp.UpdateCommand = cmd;
cmd.ExecuteNonQuery();
}
public void Deletefun(string str)
{
conopen();
cmd.Connection = con;
cmd.CommandText = str;
cmd.CommandType = CommandType.Text;
adp.DeleteCommand = cmd;
cmd.ExecuteNonQuery();
}
public DataTable getdatafun(string str)
{
conopen();
cmd.Parameters.Clear();
cmd.Connection = con;
cmd.CommandText = str;
cmd.CommandType = CommandType.Text;
adp.SelectCommand = cmd;
try
{
dt.Clear();
dt.Columns.Clear();
}
catch (Exception ex)
{ }
adp.Fill(dt);
//dt.Clear();
return dt;
}
public static void Filldrowp(DropDownList ddl, DataTable dt, string datamember, string datavalue)
{
try
{
ddl.DataSource = dt;
ddl.DataTextField = datamember;
ddl.DataValueField = datavalue;
ddl.DataBind();
ddl.Items.Insert(0, "---Select---");
}
catch (Exception ex)
{
}
}
public void fillcombo(DropDownList drp, string display, string values, string qury)
{
conopen();
cmd.Connection = con;
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = qury;
adp.SelectCommand = cmd;
try
{
dt.Clear();
}
catch (Exception ex)
{ }
adp .Fill (dt);
if (dt.Rows .Count >0)
{
int i;
for (i=0;i
{
//drp.DataValueField = dt.Rows[0][values].ToString();
//drp.DataTextField = dt.Rows[0][display].ToString();
drp.DataValueField = dt.Rows[i][values].ToString();
drp.DataTextField = dt.Rows[i][values].ToString();
drp.Items.Add(dt.Rows[i][display].ToString());
}
{
drp .DataBind ();
}
}
}
public void FillCobobox(DropDownList DDL, string Dislay, string values, string quary)
{
conopen();
cmd.Parameters.Clear();
cmd.Connection = con;
cmd.CommandText = quary;
cmd.CommandType = CommandType.Text;
adp.SelectCommand = cmd;
try
{
dt.Clear();
}
catch (Exception ex)
{ }
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
DDL.DataValueField = dt.Rows[0][Dislay].ToString();
DDL.DataTextField = dt.Rows[0][values].ToString();
}
{
DDL.DataBind();
}
}
public void fillcombo(System.Windows.Forms.ComboBox cmbo, string display, string value, string sqlstr)
{
DataTable dtcombo = new DataTable();
conopen();
cmd.CommandText = sqlstr;
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
adp.SelectCommand = cmd;
adp.Fill(dtcombo);
cmbo.DataSource = dtcombo;
cmbo.ValueMember = dtcombo.Columns[value].ToString();
// cmbo.DisplayMember = dtcombo.Columns[display].ToString();
}
}
}
}
No comments:
Post a Comment