Shera's DotNet: Insert Update Delete

STEP 1: Creating a Database Table

In this demo, I presumed that you already have a basic background on how to create a simple database table. In this example, I used my own database called SampleDB which has Customers Table and basically contains the following field columns:

CustomerID – PK

CompanyName

ContactName

ContactTitle

Address

Country

STEP 2: Setting Up the Connection String

STEP 3: Setting up the GUI

Just for the simplicity of this demo, I set up the GUI like this:

<title>GridView Data Manipulation</title>

</head>

<body>

<div>

<tr>

Company ID</td>

Company</td>

Name</td>

Title</td>

Address</td>

Country</td>

</tr>

<tr>

<asp:TextBox ID="TextBox1" runat="server"/></td>

<asp:TextBox ID="TextBox2" runat="server"/></td>

<asp:TextBox ID="TextBox3" runat="server"/></td>

<asp:TextBox ID="TextBox4" runat="server"/></td>

<asp:TextBox ID="TextBox5" runat="server"/></td>

<asp:TextBox ID="TextBox6" runat="server"/></td>

<asp:Button ID="Button1" runat="server" Text="Add New" OnClick="Button1_Click" /></td>

</tr>

</table>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ShowFooter="true">

<asp:BoundField DataField="CustomerID" HeaderText="ID" ReadOnly="true"/>

<asp:BoundField DataField="CompanyName" HeaderText="Company"/>

<asp:BoundField DataField="ContactName" HeaderText="Name"/>

<asp:BoundField DataField="ContactTitle" HeaderText="Title" />

<asp:BoundField DataField="Address" HeaderText="Address"/>

<asp:BoundField DataField="Country" HeaderText="Country"/>

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

Note: I have set the CustomerID field to ReadOnly so that the field cannot be edited.

STEP 4: Binding GridView with Data

I will not elaborate on this step because I already describe the details in my previous example about “Binding GridView with Data”. Here are the code blocks for binding the GridView.

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridView();

}

private string GetConnectionString()

{

return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

}

#region Bind GridView

private void BindGridView()

{

DataTable dt = new DataTable();

SqlConnection connection = new SqlConnection(GetConnectionString());

try

{

connection.Open();

string sqlStatement = "SELECT Top(10)* FROM Customers";

SqlCommand cmd = new SqlCommand(sqlStatement, connection);

SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);

sqlDa.Fill(dt);

if (dt.Rows.Count > 0)

{

GridView1.DataSource = dt;

GridView1.DataBind();

}

catch (System.Data.SqlClient.SqlException ex)

{

string msg = "Fetch Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally

{

connection.Close();

}

#endregion

}

Now, we already know how to bind our GridView with data from database. So let’s proceed on adding a new data in GridView.

STEP 5: Adding New Data in GridView

As you have noticed in STEP 2, we have added six TextBox and a Button in the web form in order for us to type the information there and Insert them to the database. Now let’s create a method for executing the Update or Insert.

Here are the code blocks for our Insert and Update method in the code behind:

#region Insert New or Update Record

private void UpdateOrAddNewRecord(string ID, string Company, string Name, string Title, string Address, string Country, bool isUpdate)

{

SqlConnection connection = new SqlConnection(GetConnectionString());

string sqlStatement = string.Empty;

if (!isUpdate)

{

sqlStatement = "INSERT INTO Customers"+

"(CustomerID,CompanyName,ContactName,ContactTitle,Address,Country)" +

"VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@Country)";

}

else

{

sqlStatement = "UPDATE Customers" +

"SET CompanyName = @CompanyName,

ContactName = @ContactName," +

"ContactTitle = @ContactTitle,Address =

@Address,Country = @Country" +

"WHERE CustomerID = @CustomerID,";

}

try

{

connection.Open();

SqlCommand cmd = new SqlCommand(sqlStatement, connection);

cmd.Parameters.AddWithValue("@CustomerID", ID);

cmd.Parameters.AddWithValue("@CompanyName", Company);

cmd.Parameters.AddWithValue("@ContactName", Name);

cmd.Parameters.AddWithValue("@ContactTitle", Title);

cmd.Parameters.AddWithValue("@Address", Address);

cmd.Parameters.AddWithValue("@Country", Country);

cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();

}

catch (System.Data.SqlClient.SqlException ex)

{

string msg = "Insert/Update Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally

{

connection.Close();

}

#endregion

The UpdateOrAddNewRecord is a method that takes seven parameters. Six of those parameters basically come from the TextBox values that were entered in the page. The last parameter is a boolean value which tells the method whether to execute an Insert (false) or Update (true). Default is true.

Here’s the code block for calling the method UpdateOrAddNewRecord on Button_Click event and pass the corresponding parameters needed:

protected void Button1_Click(object sender, EventArgs e)

{

UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, false);

//Re Bind GridView to reflect changes made

BindGridView();

}

OUTPUT

for search

<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <asp:Button ID="btnsearch" runat="server" onclick="btnsearch_Click"
        Text="search" />
    <p>
        <asp:TextBox ID="txtname" runat="server"></asp:TextBox>
    </p>
----------------------

protected void btnsearch_Click(object sender, EventArgs e)
    {
        DataSet ds1=new DataSet();
        SqlDataAdapter adp=new SqlDataAdapter();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        string cid = txtSearch.Text;
        string sqlStatement = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";
        try
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand(sqlStatement, connection);
            cmd.Parameters.AddWithValue("@CustomerID", cid);
            cmd.CommandType = CommandType.Text;

            adp = new SqlDataAdapter(cmd);
            //     adp = new SqlDataAdapter(cmd, con);

            adp.Fill(ds1);
            //return ds;
            txtname.Text=ds1.Tables[0].Rows[0]["ContactName"].ToString();
           // cmd.ExecuteNonQuery();
        }
        catch
        {
            //System.Data.SqlClient.SqlException ex
            //string msg = "Deletion Error:";
            //msg += ex.Message;
            //throw new Exception(msg);

        }
        finally
        {
            connection.Close();
        }
    }

Shera's DotNet

Friday, November 4, 2011

Insert Update Delete

No comments:

Post a Comment