This article includes a datainteraction class which is used for establish connection between asp.net and sql server & retrieving data etc . it involves all the function for data interaction.
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 DataInteraction
///
public class DataInteraction
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adpt = new SqlDataAdapter();
SqlDataReader dr;
public DataInteraction()
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["nimbus"].ToString();
}
public DataSet FillDs(String query)
{
DataSet ds = new DataSet();
con.Open();
cmd.Connection = con;
cmd.CommandText = query;
cmd.ExecuteNonQuery();
adpt.SelectCommand = cmd;
adpt.Fill(ds);
con.Close();
return ds;
}
public void IDU(String query)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = query;
cmd.ExecuteNonQuery();
con.Close();
}
public String SelectSclar(String query)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = query;
string result = Convert.ToString(cmd.ExecuteScalar());
con.Close();
return result;
}
public String SelectScalarsingle(string qry)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = qry;
string rs = Convert.ToString(cmd.ExecuteScalar());
con.Close();
return rs;
}
public SqlDataReader FillDr(String query)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = query;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
con.Close();
return dr;
}
public SqlDataReader filldrdd(string qry)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = qry;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
public void fillddlbydr(string qry, string txtfield, string valuefield, DropDownList ddl)
{
ddl.Items.Clear();
dr = filldrdd(qry);
ddl.Items.Add(new ListItem("---Select---", "0"));
if (dr.HasRows)
{
while (dr.Read())
{
ddl.Items.Add(new ListItem(dr[txtfield].ToString(), dr[valuefield].ToString()));
}
}
dr.Dispose();
}
public void fillddlbydrnotselect(string qry, string txtfield, string valuefield, DropDownList ddl)
{
ddl.Items.Clear();
dr = filldrdd(qry);
// ddl.Items.Add(new ListItem("---Select---", "0"));
if (dr.HasRows)
{
while (dr.Read())
{
ddl.Items.Add(new ListItem(dr[txtfield].ToString(), dr[valuefield].ToString()));
}
}
dr.Dispose();
}
public void fillddlbyGarde(string qry, string txtfield, string valuefield, DropDownList ddl)
{
ddl.Items.Clear();
dr = filldrdd(qry);
ddl.Items.Add(new ListItem("Grade", "0"));
if (dr.HasRows)
{
while (dr.Read())
{
ddl.Items.Add(new ListItem(dr[txtfield].ToString(), dr[valuefield].ToString()));
}
}
dr.Dispose();
}
public void fillddlbydrpn(string qry, string txtfield, string valuefield, DropDownList ddl)
{
ddl.Items.Clear();
dr = filldrdd(qry);
ddl.Items.Add(new ListItem("PN", "0"));
if (dr.HasRows)
{
while (dr.Read())
{
ddl.Items.Add(new ListItem(dr[txtfield].ToString(), dr[valuefield].ToString()));
}
}
dr.Dispose();
}
public void fillddlbydrdn(string qry, string txtfield, string valuefield, DropDownList ddl)
{
ddl.Items.Clear();
dr = filldrdd(qry);
ddl.Items.Add(new ListItem("DN", "0"));
if (dr.HasRows)
{
while (dr.Read())
{
ddl.Items.Add(new ListItem(dr[txtfield].ToString(), dr[valuefield].ToString()));
}
}
dr.Dispose();
}
}
