Tuesday, January 20, 2015

Program on SQL Parameterized Queries

11:57 AM - By ajay desai 0

Explanation: - Parameterized queries are used to filter data based on the input given by the user at run-time.
Example for parameterized SQL query: -
Select *from student where course=@course (this is used when working with SQL server)

In the above given query, @course is a parameter passed to the query. The value of this parameter is given at run-time. To enable ADO.NET to populate (assign value to) the @course parameter, we need to call a SqlParameter constructor in the SqlParameter class for the command. This object is used to assign parameterized values to SQL queries. We then execute the command on our desired connection and ADO.NET takes care of setting up the command for execution on the respective database server.

A dataset is a class which holds data in the form of multiple records (i.e. rows and columns) in its data table which represents a table present in the database. This data table is present in the application memory area even when there is no connection between the application and database. So, it provides both read and write access to data. A dataset follows disconnected architecture i.e. a dataset can hold the selected records in its data table even after the connection with the database is closed therefore, we can select any record from a data table randomly and we can also insert, update or delete a record in a data table and then update these changes in the database. The contents of a dataset can be displayed on a GridView as shown below.         

                                                                                                                                                              
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace ParametrizedQueries
{
    public partial class gridview : Form
    {
        SqlConnection con;
        SqlCommand cmd;
        DataSet ds;
        SqlDataAdapter da;
        public gridview()
        {
            InitializeComponent();
        }

        private void view()
        {
            con = new SqlConnection("Data Source=IFMRJPAL-PC\\TESTDB; Initial Catalog=LIB_MGMT; Integrated Security=True");
            string q = "select bookid,bookname,authname,publication,edition from books";
            cmd = new SqlCommand(q, con);


            da = new SqlDataAdapter();
            da.SelectCommand = cmd;//this select command property is used to retrieve data from the database table:books

            ds = new DataSet();
            da.Fill(ds, "books");// Fill method of SqlDataAdapter class is used to fill the dataset table with the contents retrieved from the database table.
            bookstable.DataSource = ds.Tables[0];// here, we are binding the gridview:bookstable with the table present in the dataset.
        }

        private void search_Click(object sender, EventArgs e)
        {
            con = new SqlConnection("Data Source=IFMRJPAL-PC\\TESTDB; Initial Catalog=LIB_MGMT; Integrated Security=True");
            con.Open();
            string s = "select count(*) from books where bookname=@bookname";
            cmd = new SqlCommand(s, con);
            cmd.Parameters.AddWithValue("@bookname", bkname.Text);
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();

            if (i >= 1)
            {
                con = new SqlConnection("Data Source=IFMRJPAL-PC\\TESTDB; Initial Catalog=LIB_MGMT; Integrated Security=True");
                string q = "select bookid,bookname,authname,publication,edition from books where bookname=@bookname";
                cmd = new SqlCommand(q, con);
                cmd.Parameters.AddWithValue("@bookname", bkname.Text);

                da = new SqlDataAdapter();
                da.SelectCommand = cmd;

                ds = new DataSet();
                da.Fill(ds, "books");
                bookstable.DataSource = ds.Tables[0];
            }
                else MessageBox.Show("this book does not exist");
            
        }

        private void gridview_Load(object sender, EventArgs e)
        {
            view();
        }

        private void show_Click(object sender, EventArgs e)
        {
            view();
            bkname.Text = "";
        }
    }
}

                                              

Output: -





Tags:
About the Author

I am Azeheruddin Khan having more than 6 year experience in c#, Asp.net and ms sql.My work comprise of medium and enterprise level projects using asp.net and other Microsoft .net technologies. Please feel free to contact me for any queries via posting comments on my blog,i will try to reply as early as possible. Follow me @fresher2programmer
View all posts by admin →

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

0 comments:

adsense

© 2014 Fresher2Programmer. WP Theme-junkie converted by Bloggertheme9
Powered by Blogger.
back to top