Saturday, 22 September 2012

Database operation ASP.net using SQL Server 2000


The ADO.NET architecture is designed to make life easier for both the application developer and the database provider. To the developer, it presents a set of abstract classes that define a common set of methods and properties that can be used to access any data source. The data source is treated as an abstract entity, much like a drawing surface is to the GDI+ classes.


Controls :
  • Three Textbox
  • Three Button
  • One GridView
  • SqlDataSource




Setting Gridview : 
  • Add Gridview select auto format if you want to use available style for gridview select a style click ok
  • Choose data source select < New Data source.. >
  • Select Database from Configuration Wizard 
  • Click New Connection and change database type from Add Connection wizard
  • Choose SQL Server click ok
  • Enter Server name I have have used . for local client server
  • Select Database Click ok
  • Select check your connection string and click next
  • Select your table and fields which you want to show on Gridview
Coding :
 SqlConnection con;
    SqlCommand com;
    protected void Page_Load(object sender, EventArgs e)
    {
        con = new SqlConnection("initial catalog=bsw;data source=.;integrated security=true");
        com = com = new SqlCommand();
        com.Connection = con;
    }



    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (txtroll.Text == "" && txtname.Text == "" && txtcity.Text == "")
        {
            Label1.Text = "Please Fill all fields value";
        }
        else
        {
            try
            {
                con.Open();
                com.CommandText = "insert into stu values(" + txtroll.Text + ",'" + txtname.Text + "','" + txtcity.Text + "')";
                com.ExecuteNonQuery();
                Label1.Text = "";
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                Label1.Text = ex.Message;
            }
            finally
            {
                con.Close();
            }
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        try
        {
            if (txtroll.Text == "" && txtname.Text == "" && txtcity.Text == "")
            {
                Label1.Text = "Please provide all fields value";
            }
            else
            {
                con.Open();
                com.CommandText = "update stu set name='" + txtname.Text + "',city='" + txtcity.Text + "' where roll=" + txtroll.Text;
                com.ExecuteNonQuery();
                Label1.Text="";
                GridView1.DataBind();

            }
        }
        catch (Exception ex)
        {
            Label1.Text = ex.Message;
        }
        finally
        {
            con.Close();
        }
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        try
        {
            if (txtroll.Text == "" && txtname.Text == "" && txtcity.Text == "")
            {
                Label1.Text = "Please enter roll number";
                txtroll.Focus();
            }
            else
            {
                con.Open();
                com.CommandText = "delete from stu where roll=" + txtroll.Text;
                com.ExecuteNonQuery();
                GridView1.DataBind();
            }
        }
        catch (Exception ex)
        {
            Label1.Text = ex.Message;
        }
        finally
        {
            Label1.Text = con.Close();
        }

    }