JUGGERNAUT: sql server

Bangalore: Here we are going to discuss some DDL queries which we are using frequently in our databse operations. Tools Required: SQL Server Management Studio. Create a Test database in your SQL server. normally we will create our tables columns all things through our SSMS, here we are discussing how we can create it through query.
so all these Create,Alter,Drop queries comes under DDL(Data definition Language) because its used to modify the db ojects in the databse.

1) Create Table

use
Test
go

Create Table deptmnt(
dept_id int primary key identity(1,1),
depart_name varchar(50)
)

It will create a table `deptmnt` in your `Test` database with two columns `dept_id` and `depart_name`

here department id is a primary key and it will auto increment by 1 when we add next row to the table.

because we have given identity(1,1).

if we have given identity(2,2) it will increment by two.

2) Drop Table

Drop Table deptmnt;

This sql query will drop your table deptmnt.

3) Alter Table

If we want to do any modifiaction in our already created table we will use alter key word.

Create Table employee(

employee_id int,

employee_name varchar(50)

)

So we have created a brand new table employee we want add one more column to this table so we will alter our table.

ALTER TABLE employee

Add dept_id int

It will add one more column to your table.

So here employee_id is not primary key and its not auto incrementing one so we are going to implement it through sql queries.

3.1) Altering column into primary key and adding identity

First we will drop the employee_id field

ALTER TABLE employee

drop column employee_id

Then we will add employee_id column as identity

ALTER TABLE employee

ADD employee_id INT IDENTITY(1,1)

Then we will include the primary key constarint to the Column

ALTER TABLE employee

ADD CONSTRAINT PK_employee_id

PRIMARY KEY(employee_id)

3.2) Altering Column Data Type

It will change data type employe_name varchar 50 to 100 or you can change to other data types also.

ALTER TABLE employee

ALTER COLUMN employee_name varchar(100)

4) Adding Foreign Key Reference

Here we have two tables department and employees so we need to add a foreign key relation ship between these two tables dept_id in employee table is related with department id in department table.

so foreign key relation in employee table with department table.

ALTER TABLE employee

ADD CONSTRAINT fk_employee_dept_id FOREIGN KEY(dept_id ) REFERENCES deptmnt(dept_id )

5) Renaming Table/Column in SQL

Here we want to rename our deptmnt table into department so here is the sql query.

old to new

sp_RENAME 'deptmnt ' , 'department '

Now we want to change the department_name column into d_name so here we go.

sp_RENAME 'department .department_name ' , 'd_name ', 'COLUMN'

Bangalore: In any web application development process you may face this situation as per your requirement, a web form which have four or five filter fields and a button to search. I am just writing this article for beginners those who starts developing in Asp.Net, C# feel free to comment in our blog if you have any doubt regarding this article.So first of all before writing any program think what you required to achieve your result.
Here we need Visual Studio 2010 and MS SQL Server 2008 as Development Tools.

First create an Empty Web Application in C#
And then we create a Asp.Net web form which is having 5 Fields and 1 Button to search and one to Clear one grid view to list all the Results.
and In code behind aspx.cs page you have to write Logic for your Button click actions
and In web.config file we will declare the connection string details.

In web.config Inside <configuration> tag after <system.web> tag add the connection string syntax.
<connectionStrings>
<add name="constr1" connectionString="Data Source=Tonz-PC; Initial Catalog=Search; Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

So Now we can check the code to create Aspx from.
Search.Aspx from

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

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

function Clear() {

document.getElementById("txtFirstName").value = "";

document.getElementById("txtLastName").value = "";

document.getElementById("txtUserName").value = "";

document.getElementById("txtCountry").value = "";

document.getElementById("txtState").value = "";

return true;

}

</script>

<title>Search Form</title>

</head>

<body>

<div>

<legend>Search Filter</legend>

<tr>

<td nowrap="nowrap">First Nmae</td>

<td>

<asp:TextBox runat="server" ID="txtFirstName"></asp:TextBox>

</td>

<td nowrap="nowrap"> UserName</td>

</tr>

<tr>

<td>Country

</td>

</td>

<td nowrap="nowrap">State </td>

</tr>

<tr>

<asp:Button ID="btnSearch" runat="server" Text="Search" onclick="btnSearch_Click" />

</td>

<td>

<asp:Button runat="server" ID="btnClear" Text="Clear" OnClientClick="javascript:Clear()" CausesValidation="false"/>

</td>

</tr>

<asp:Panel runat="server" ID="pnlgrid">

<tr>

<legend>Search Results</legend>

<asp:GridView runat="server" ID="gdvSearch" AllowPaging="true"

AutoGenerateColumns="false" PageSize="6" BackColor="White" Width="100%"

BorderColor="Black" BorderStyle="Ridge" BorderWidth="2px" CellPadding="8" CellSpacing="1"

ForeColor="#330199" GridLines="None" HeaderStyle-BorderColor="Black" RowStyle-BackColor="White">

<asp:BoundField DataField="firstname" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="First Name" >

</asp:BoundField>

<asp:BoundField DataField="lastname" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="Last Name">

</asp:BoundField>

<asp:BoundField DataField="username" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="User Name" ></asp:BoundField>

<asp:BoundField DataField="Country" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="Country" ></asp:BoundField>

<asp:BoundField DataField="State" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="Country" ></asp:BoundField>

</Columns>

</asp:GridView>

</fieldset>

</td>

</tr>

</asp:Panel>

</table>

</div>

</form>

</body>

</html>

code ends here
In code behind Search.Aspx.cs have all c sharp codes and we will Create seachbus.cs class there we will write the database logic and all.
If string is Empty we are passing "%" so it will take care of all null entries

Search.Aspx.cs code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

namespace search

{

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

{

#region PageLoad

protected void Page_Load(object sender, EventArgs e)

{

pnlgrid.Visible = false; /* make grid Invisible first */

}

#endregion

#region Button Click

protected void btnSearch_Click(object sender, EventArgs e)

{

DataTable dtsearch= getFilteredData();

gdvSearch.DataSource = dtsearch;

gdvSearch.DataBind();

gdvSearch.Visible = true;

pnlgrid.Visible = true;

ViewState["file"] = dtsearch;

}

#endregion

#region passing value to sp

public DataTable getFilteredData()

{

searchbus objSearch = new searchbus();

/* Creating object of searchbus class and passing value to there*/

if (txtFirstName.Text == "")

{

objSearch.Fname = "%"; /* It will take care of Null Entries */

}

else

{

objSearch.Fname = txtFirstName.Text;

}

if (txtLastName.Text == "")

{

objSearch.Lname = "%";

}

else

{

objSearch.Lname = txtLastName.Text;

}

if (txtUserName.Text == "")

{

objSearch.Uname = "%";

}

else

{

objSearch.Uname = txtUserName.Text;

}

if (txtCountry.Text == "")

{

objSearch.Cntry = "%";

}

else

{

objSearch.Cntry = txtCountry.Text;

}

if (txtState.Text == "")

{

objSearch.Stat = "%";

}

else

{

objSearch.Stat = txtState.Text;

}

DataTable dt = objSearch.GetUsers(); /* passing all values into your sp */

return dt;

}

#endregion

}

searchbus.cs class

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace search

{

public class searchbus

{

private string FirstName;

private string LastName;

private string UserName;

private string Country;

private string State;

/*assigning values using get and set */

public string Fname

{

get { return FirstName; }

set { FirstName = value; }

}

public string Lname

{

get { return LastName; }

set { LastName = value; }

}

public string Uname

{

get { return UserName; }

set { UserName = value; }

}

public string Cntry

{

get { return Country; }

set { Country = value; }

}

public string Stat

{

get { return State; }

set { State = value; }

}

#region method search

public DataTable GetUsers()

{

DataTable lrs;

SqlCommand cmd = new SqlCommand();

SqlParameter spm = null;

cmd.CommandText = "USP_SearchUsers";

cmd.CommandType = CommandType.StoredProcedure;

spm = new SqlParameter("@FirstName", SqlDbType.VarChar);

spm.Value = FirstName;

cmd.Parameters.Add(spm);

spm = new SqlParameter("@Lastname", SqlDbType.VarChar);

spm.Value = LastName;

cmd.Parameters.Add(spm);

spm = new SqlParameter("@UserName", SqlDbType.VarChar);

spm.Value = UserName;

cmd.Parameters.Add(spm);

spm = new SqlParameter("@Country", SqlDbType.VarChar);

spm.Value = Country;

cmd.Parameters.Add(spm);

spm = new SqlParameter("@State", SqlDbType.VarChar);

spm.Value = State;

cmd.Parameters.Add(spm);

lrs = ExecuteQuery(cmd);

return lrs;

}

#endregion

#region get connection string

public string getConstring()

{

string constr = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;

return constr;

}

#endregion

#region ExecutingQuery

public DataTable ExecuteQuery(SqlCommand SQLCommand)

{

string str = getConstring();

SqlConnection SQLConn = new SqlConnection(str);

if (SQLConn.State != ConnectionState.Open)

SQLConn.Open();

DataTable DataTable = null;

DataTable = new DataTable();

SQLCommand.Connection = SQLConn;

SqlDataAdapter SqlData= new SqlDataAdapter(SQLCommand);

SqlData.Fill(DataTable);

SQLConn.Close();

return DataTable;

}

#endregion

}

Search Stored Procedure from MSSQL Database

USE [Search]

/****** Object: StoredProcedure [dbo].[USP_SearchUsers] Script Date: 6/5/2013 2:08:25 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Tony Tom

-- Create date: 2-june-2013

-- =============================================

Create PROCEDURE [dbo].[USP_SearchUsers] --

@FirstName varchar(50)=null,

@Username varchar(50)=null,

@Lastname varchar(100)=null,

@country varchar(100)=null,

@state varchar(100)=null

Begin

SELECT

[firstname]

,[lastname]

,[username]

,[country]

,[state]

FROM [userdata]

where FirstName like @FirstName+'%' and username like @Username+'%' and LastName like @Lastname+'%' and country like @country+'%' and [state] like @state+'%'

End

So Finally while running your project you will get the result like this.

Bangalore: As per your requirement for looping purpose we use cursor in our stored procedures, stored procedures is nothing but a set of queries, If you wont have any other options then only you have to use cursor in your queries because it will affect the normalization of your sql query.

So consider you have 100 employees and you need to generate the payroll for these 100 employees, so you have write some complex payroll logic and there you want to pass employee id's and it will generate payroll and insert it into a table.
This kind of situations we need to do operations row by row or employee by employee one employees payroll has generated then next's like that so we will use cursor in that case.

SQL Syntax

/*----Our Code Starts Here ---*/

USE [Tonz] --database name
GO

-- =============================================
-- Author: Tony Tom
-- Create date: 03-06-2013
-- Description: SQL Cursor Example
-- =============================================
ALTER PROCEDURE [dbo].[USP_CursorTest] --Stored procedure name

AS
BEGIN
declare @userid varchar(50); --declare one variable to fetch userid's

declare db_cursor Cursor For -- declare cursor
select UserID from UserMain
open db_cursor --opening cursor

Fetch Next from db_cursor into @userid -- fetching first value

while @@FETCH_STATUS=0 -- checking loop exiting condition
begin
/* -- do your raw by row operation here--*/
insert into uname(usename) values(@userid);
fetch next from db_cursor into @userid --fetching next value

end
close db_cursor;
End
/*----Our Code Ends Here ---*/

Here we need only one variable in cursor as per your requirements you can take as many variables you want in that particular row to cursor

Banaglore: We need to create a Windows form application and on submit button click we need to insert data's into Sql server Db using sql server stored procedure.
So what all the things we need.
Software Requirements.
We need visual studio for form development,SQL Server Management studio for Database operations.
First we will create a Database with name Test. in sql server management studio.
create a table with 5 columns
You can create it Manually or you can run the below script in your SQL SMStudio.

/*Your Code Starts Here*/

USE [Test]
GO

/****** Object: Table [dbo].[tbl_login] Script Date: 4/16/2013 12:09:51 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_login1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[firstname] [varchar](50) NULL,
[lastname] [varchar](50) NULL,
[username] [varchar](50) NULL,
[password] [varchar](50) NULL,
CONSTRAINT [PK_tbl_login1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/* Code Ends Here */

Here Id is a primary key and its an auto incremental one.
Now we will create a stored procedure for Inserting data's into Table tbl_login.
How to create stored procedure in SQL Server.
Go to your database Inside programability>Storedprocedure> Right Click and create new procedure.

/* Your Code Starts Here */

USE [Test]
GO
/****** Object: StoredProcedure [dbo].[Registration] Script Date: 4/16/2013 12:15:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,TONY>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[Registration]
@FirstName as varchar(50),
@Lastname as varchar(50),
@UserName as varchar(50),
@password as varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Insert into tbl_login(firstname,lastname,username,password)
values(@FirstName,@Lastname,@UserName,@password)
END

/* Code Ends Here */

Here we have created a sp with name Registration and we are passing 4(firstname,lastname,username,password) Input parameters and Inserting it into tbl_login

and we will call this sp in our Windows Application.

Now our database operation over.

Open visual studio and create a Windows Form Project with c#.

Design your form like this.

Add a App.configuration file to your project.

and write this code in your app.config file.

Sql Server Connection String in App.config file.

/* Your code starts here */

<?xml version="1.0" encoding="utf-8" ?>

<add name="Connectionstring1"

connectionString="Data Source=Tonz-Pc; Initial Catalog=Test;Integrated Security=true" providerName="System.Data.SqlClient"/>

</connectionStrings>

</configuration>

/* Your code ends here */

here we have declare the connection string with name Connectionstring1, and database name Test

Double click on the submit button and write this code inside click event

/* Code starts here */

private void btnSubmit_Click(object sender, EventArgs e)

{

string username;

string password;

string firstname;

string lastname;

username = txtUserName.Text;

password = txtPassword.Text;

firstname = txtFirstName.Text;

lastname = txtLastname.Text;

}

/* code ends here */

We are receiving firstname, lastname, username, password from Form and setting it into declared variables.
and we are calling the Register Function and passing the 4 values into that

/* Code Starts Here */

public void Register(string Fname, string Lname, string Uname, string paswd)
{
/* Assigning coonection string to 'constring' variable by calling getConnection function */
string constring= getConnection();

/* Declaring Connection Variable */
SqlConnection con = new SqlConnection(constring);

/* Checking Connection is Opend or not If its not open the Opens */
if (con.State != ConnectionState.Open)
con.Open();

/* Calling Stored Procedure as SqlCommand */
SqlCommand cmd = new SqlCommand("Registration", con);
cmd.CommandType = CommandType.StoredProcedure;

/* Passing Input Parameters with Command */
cmd.Parameters.AddWithValue("@FirstName", Fname);
cmd.Parameters["@FirstName"].Direction=ParameterDirection.Input;

cmd.Parameters.AddWithValue("@Lastname",Lname);
cmd.Parameters["@Lastname"].Direction=ParameterDirection.Input;

cmd.Parameters.AddWithValue("@UserName",Uname);
cmd.Parameters["@UserName"].Direction=ParameterDirection.Input;

cmd.Parameters.AddWithValue("@password",paswd);
cmd.Parameters["@password"].Direction = ParameterDirection.Input;

/* Executing Stored Procedure */
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Data Inserted Succesfully");

}

/* Code Ends Here */

Get Connection function It will return connection String.

/* Code Starts here */

public static string getConnection()
{
/*Reading Connection string from App.config File */
string constr = ConfigurationManager.ConnectionStrings["Connectionstring1"].ConnectionString;
return constr;
//return true;
}

/* Code Ends here */

You need to Import these References on top of your code additional to the default Libraries

using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;

Compile your project and on submit button click you will get "Data Inserted Succesfully" message if its inserted successfully.

If you have any doubts commend to us