Computer Programming

Thursday, September 19, 2013

September 19, 2013

How to Execute Parameterized SQL Statements: ADO.NET

As we know, SQL statements can be of simple type or may be parameterized. The parameterized query contains some parameters, which may be accepted through object of SqlParameter class. SqlParameter class is used to create the parameters used by the command object to execute the Sql queries.
Write the following code to select a record from the table groups which has a given name. The name is passed by the parameter i.e. Sql Parameter.

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source= (LocalDb)\\v11.0; Initial Catalog=StockDb; Integrated Security=True";
connection.Open();
SqlCommand command = new SqlCommand("select * from Groups where code=@code", connection);
command.Parameters.AddWithValue("@code", 04);
SqlDataReader dr = command.ExecuteReader();

Look out the command object which have a variable name @code. It is the syntax of parameter used. In the next line, the parameter is added by using the function AddWithValue(). This method takes two parameter i.e. one for variable name (same as used in command object) and the second one is its value.

Now when we execute this code, a single record having code = 04 will be returned and can be accessed by data reader object dr.

We can check the rows as the same procedure as in previous post.

c# database SQL-SERVER SqlDataReader

September 19, 2013

How to Execute SQL Statements: ADO.NET

We have learnt about ADO.NET object model. In that article we have created a SQL connection, open that connection and close that connection. To execute the SQL statements, we have to write them in between the open and close method of the above connection.

So to execute the SQL statement we have to, first create the connection, open that, write some code which will be executed and at the last execute it with ExecuteReader() method of SQLCommand class.

SqlCommand class is used to specify the Sql statement to be executed and the connection that need to be used. It can also be used to execute stored procedures. Following code will used to execute Sql statement in a command object:

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source=(LocalDb)\v11.0; Initial Catalog=StockDb; Integrated Security=True";
connection.Open();
SqlCommand command = new SqlCommand("select * from Groups", connection);
SqlDataReader dr = command.ExecuteReader();

In the above code, when SqlCommand object will create, an object of SqlDataReader have been also created. And it is initialized with the function ExecuteReader(). Now all the data can be accessed one by one through dr object, one by one.

When we check the dataReader object through the breakpoint then there are some rows, as shown in following image:

September 19, 2013

How to use AddWithValue Method in ASP.NET

Adds a value to the end of the System.Data.SqlClient.SqlParameterCollection. Syntax of the method is

variable of SqlCommand.Parameters .AddWithValue (String parameterName , Object value);
using Parameters collection you can insert values to the database.

Lets take an example how to insert item into database table.

Codebehind

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

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

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click1(object sender, EventArgs e)

{

using (SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"))

{

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.Parameters.AddWithValue("@nameprameter", TextBox1.Text);

cmd.Parameters.AddWithValue("@Addressparameter", TextBox2.Text);

cmd.CommandText = "insert into [mytab](name,address)values(@nameprameter,@Addressparameter)";

int a = cmd.ExecuteNonQuery();

if (a > 0)

{

Label1.Text = "insert data";

}

Output

How to use AddWithValue Method in ASP.NET

September 19, 2013

How to use DataRow class in ASP.NET

Lets take an example to bind DropDownList using DataRow class.

Codebehind

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

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

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

using (SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"))

{

con.Open();

SqlCommand cmd = new SqlCommand("select * from [mytab]", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

da.Fill(dt);

if (DropDownList1.Items.Capacity == 0)

{

foreach (DataRow dr in dt.Rows)

{

string name = dr["name"].ToString();

DropDownList1.Items.Add(name);

}

Output

Wednesday, September 18, 2013

September 18, 2013

How to use DataTable in ASP.NET

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title></title>
</head>
<body>
   <form id="form1" runat="server">
   <div>

   <asp:Button ID="Button1" runat="server" Height="37px" onclick="Button1_Click"
   Text="Bind Gridview" Width="130px" />
   <br />

   </div>
   <asp:GridView ID="GridView1" runat="server">
   </asp:GridView>
   </form>
</body>
</html>
Codebehind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class Default4 : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {

   }
   protected void Button1_Click(object sender, EventArgs e)
   {

   using (SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"))
   {
   con.Open();
   SqlCommand cmd = new SqlCommand("select * from [mytab]", con);
   SqlDataAdapter da = new SqlDataAdapter(cmd);
   DataTable dt = new DataTable();
   da.Fill(dt);
   GridView1.DataSource = dt;
   GridView1.DataBind();


   }
   }
}

Output

c# Controls WPF XAML

September 18, 2013

How to Place Items in TabControl: WPF

In our previous post, we have learnt about tab control and place some tabs into that control. In this post we will place a container, means we will place a simple entry form into a tab item. As we all know the entry form can contain name, age, address or some more basic information about a person.

So I am taking only name and age here as for example. Just write the following code in our XAML code window:

Just run the code and a tab control is placed on our window with an entry form. The form will look like the following image:

How to customize tab control in WPF and XAML

So we have simply placed an entry form into tab item. We can set anything as the content property of tab item, just as above code.

In the above code, i have modify only a single tab item for example. If you need more tabs then you can modify as many tabs as you want to.

c# Controls WPF XAML

September 18, 2013

How to Create Tabs using TabControl: WPF

When there is not much space on our screen then programmer uses a tab control. Tab control is used to organize multiple tabs on WPF window, which can performs individual function decided by the programmer. It can contains collection of objects of any type i.e. string, image or a container.

Through XAML, we can place a tab control easily using the element Tab Control. To place some tabs into tab control, we have to use TabItem elements. Write the following code in your XAML:

Now run the code and check out the tab control. It have three tabs with their own header as described above.

The same tab control can also be generated through the following code in C# code behind file:

TabControl tabcontrol = new TabControl();
tabcontrol.Name = "tabControl";
tabcontrol.Height = 400;
tabcontrol.Width = 300;
tabcontrol.Margin = new Thickness(5);

TabItem tabItem1 = new TabItem();
tabItem1.Header = "Tab 1";
tabcontrol.Items.Add(tabItem1);

TabItem tabItem2 = new TabItem();
tabItem2.Header = "Tab 2";
tabcontrol.Items.Add(tabItem2);

TabItem tabItem3 = new TabItem();
tabItem3.Header = "Tab 2";
tabcontrol.Items.Add(tabItem3);

this.Content = tabcontrol;

In next article, we will place some items/containers in these tab items. The containers could have other items/containers.