Simple Stored Procedure in SQL Server

A stored procedure is a compiled program stored in a database for fast execution. It is ideal for repetitive tasks. In this example i will create a simple stored procedure, for those people who want to quickly start programming in Sql Server.

Lets get our hand Dirty.


Create procedure sp_empByID
@eid INT
AS
BEGIN
SELECT * from emp where emp_id=@eid;
END
GO

Execute this procedure. The procedure will compile and created.

Now execute the procedure by typing this in the Query Window

 sp_empByID 13

This will return the record for the Employee which has an ID of 13

Simple Way to Update Data to Sql-Server Database using Datagridview in C#

Here is a simple way to load and Update data using Datagridview in C#.

1) Drag a datagridview on your form.
2) Drag two buttons one button will be used to load data in the Datagridview and the second would be used to update Data to the Database.
3) In the Global section of the Class i.e After the class name declare the following classes.

SqlConnection connection;
SqlDataAdapter adapter;
 DataTable dt;
In the form Constructor add the following
public Form1()
        {
            InitializeComponent();
            connection = new SqlConnection(Properties.Settings.Default.connectionString);
            connection.Open();
        }

In the Button Load event which would load data into the Dataset Enter the following

private void Button1_Click(object sender, EventArgs e)
        {
             
            string commandText = "select * from blah";
           adapter = new SqlDataAdapter(commandText, connection);
                SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
                dataGridView1.AutoGenerateColumns = true;      
                dt = new DataTable();
                adapter.Fill(dt);
                BindingSource b1 = new BindingSource();
                b1.DataSource = dt;
                dataGridView1.AutoResizeColumns(
                DataGridViewAutoSizeColumnsMode.AllCells);
                dataGridView1.DataSource = b1;
           
        }

And finally in the Update Button Click Event add the following

private void btnUpdate_Click(object sender, EventArgs e)
        {
                adapter.Update(dt);
                dataGridView1.Refresh();
        } 
Caution: Remember that the Table Must consist of A PRIMARY KEY otherwise the code will not work.

Happy Programming
 

SImple way to Bind DatagridView to SQL statement in C#

Sometimes we need a simple way to bind datagridview component to an SQL query and refresh data when the SQL command changes.

1) Drag a DatagridView control to a form. Name it dataGridView1
2) Double click on the form

In the form load event make an SqlConnection
Declare SqlConnection in the global space

SqlConnection connection
 SqlDataAdapter adapter;
DataTable dt;

In the form load event open the connection

connection=new SqlConnection(<>>);
connection.open();
In the event handler to refresh the Datagrid write the following
string commandText = "select * from BLAH";
adapter = new SqlDataAdapter(commandText, connection);
dataGridView1.AutoGenerateColumns = true;
dt = new DataTable();
adapter.Fill(dt);
BindingSource b1 = new BindingSource();
b1.DataSource = dt;
dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCells);
dataGridView1.DataSource = dt;

And Voila..

SImple way to Bind DatagridView to SQL statement in C#

Sometimes we need a simple way to bind datagridview component to an SQL query and refresh data when the SQL command changes.

1) Drag a DatagridView control to a form. Name it dataGridView1
2) Double click on the form

In the form load event make an SqlConnection
Declare SqlConnection in the global space

SqlConnection connection
 SqlDataAdapter adapter;
DataTable dt;

In the form load event open the connection

connection=new SqlConnection(<>>);
connection.open();
In the event handler to refresh the Datagrid write the following
string commandText = "select * from BLAH";
adapter = new SqlDataAdapter(commandText, connection);
dataGridView1.AutoGenerateColumns = true;
dt = new DataTable();
adapter.Fill(dt);
BindingSource b1 = new BindingSource();
b1.DataSource = dt;
dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCells);
dataGridView1.DataSource = dt;

And Voila..