Asp.net Solution | sql tips

Cursor in SQL

We can say that cursor is row pointer in a set of rows (result set).Which points a single row at time in result set. But can move to other rows of the (result set) when required.

To use cursor in SQL Store procedures. You need to follow the given steps.

Define a cursor
Open the cursor to establish the result set
Fetch the data into local parameter as needed from the cursor.(one row at a time)
Close the cursor when done.

Remove All User Defined Store procedures, Views and Triggers

interesting things in SQL if you have a list of Store Procedures ,Views and triggers in your database and reason being you want to delete all Store procedures means you want to delete all the thing from data base. In that condition what you will do? It quiet simple you will delete one by one all the things. But it is so boring and time wasting. Let see how to write a simple Script to delete all Store procedures, views and triggers from database.

Remove All User Defined Store procedures.

Create PROCEDURE [dbo].[usp_RemoveAllStoreProceduers]

BEGIN

declare @spName varchar(150);

--Step 1 : Define Cursor (Create a result set of all procedures in database).

declare cur cursor for select [name] from sys.objects where type='p'

--Step 2 : Open Cursor

open cur

--Step 3 : Fatch data into local parameter

fetch next from cur into @spName

while @@fetch_Status =0

begin

-- Drop current store procedure in Cursor

exec ('drop procedure '+ @spName)

fetch next from cur into @spName

end

close cur

--Close Cursor After done

deallocate cur

--Deallocate Cursor After done

END

Remove All User Defined View.

Create PROCEDURE [dbo].[usp_RemoveAllUserDefinedViews]

BEGIN

declare @viewName varchar(150);

--Step 1 : Define Cursor (Create a result set of all views in database).

declare cur cursor for select [name] from sys.objects where type='v'

--Step 2 : Open Cursor

open cur

--Step 3 : Fatch data into local parameter

fetch next from cur into @viewName

while @@fetch_Status =0

begin

-- Drop current view in Cursor

exec ('drop view '+ @viewName)

fetch next from cur into @ viewName

end

close cur

--Close Cursor After done

deallocate cur

--Deallocate Cursor After done

E

.net training in jaipur

Example DataAdapter : use DataAdapter in asp.net

DataAdapter Example : This is a example of dataadapter.

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>

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

<script runat="server">
    protected void Page_Load(object sender, System.EventArgs e) {
        if (!Page.IsPostBack) {
            SqlConnection MyConnection;
            SqlCommand MyCommand;
            SqlDataAdapter MyAdapter;
            DataTable MyTable;

            MyConnection = new SqlConnection();
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings

["AppConnectionString1"].ConnectionString;

            MyCommand = new SqlCommand();
            MyCommand.CommandText = "SELECT TOP 8 * FROM PRODUCTS";
            MyCommand.CommandType = CommandType.Text;
            MyCommand.Connection = MyConnection;

            MyTable = new DataTable();
            MyAdapter = new SqlDataAdapter();
            MyAdapter.SelectCommand = MyCommand;
            MyAdapter.Fill(MyTable);

            GridView1.DataSource = MyTable.DefaultView;
            GridView1.DataBind();

            MyAdapter.Dispose();
            MyCommand.Dispose();
            MyConnection.Dispose();

        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>DataAdapter example: how to use DataAdapter in asp.net</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    </form>
</body>

</html>

more info : live training in jaipur

Check UserName Email Availability In Asp.Net Ajax

For this create a table Users in sql server database with ID,Uname,emlAddress columns
and add some records in it.

Add ScriptManager,Ajax UpdatePanel on the page, and inside ContentTemplate place two textbox, two image control to display images and two label controls for related messages.

Set AutoPostBack property of textbox to true.

HTML SOURCE OF PAGE

<asp:ScriptManager ID="ScriptManager1" runat="server"/>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<ContentTemplate>

UserName:

<asp:TextBox ID="txtUName" runat="server"

ontextchanged="txtUName_TextChanged"

AutoPostBack="True"/>

<asp:Image ID="imgUsr" runat="server" Visible="false"/>

<asp:Label ID="lblUsr" runat="server"/>

Email ID:

<asp:TextBox ID="txtId" runat="server"

AutoPostBack="True"

ontextchanged="txtId_TextChanged"/>

<asp:Image ID="imgId" runat="server" Visible="false"/>

<asp:Label ID="lblId" runat="server"></asp:Label>

</ContentTemplate>

</asp:UpdatePanel>

Write below mentioned code in TextChanged Event of textbox

C# CODE




protected void txtUName_TextChanged(object sender, EventArgs e)

    {

        if (txtUName.Text != string.Empty)

        {

            string strConnection = ConfigurationManager.ConnectionStrings

            ["ConnectionString"].ConnectionString;

            string strSelect = "SELECT COUNT(*) FROM Users WHERE 

            Uname = @Username";

            SqlConnection con = new SqlConnection(strConnection);

            SqlCommand cmd = new SqlCommand(strSelect,con);



            SqlParameter user = new SqlParameter("@Username", SqlDbType.VarChar);

            user.Value = txtUName.Text.Trim().ToString();

            cmd.Parameters.Add(user);

            con.Open();

            int result = (Int32)cmd.ExecuteScalar();

            con.Close();



            if (result >= 1)

            {

                imgUsr.ImageUrl = "unavailable.png";

                imgUsr.Visible = true;

                lblUsr.Text = "Username not available";

                lblUsr.ForeColor = System.Drawing.Color.Red;

            }

            else

            {

                imgUsr.ImageUrl = "tick.png";

                imgUsr.Visible = true;

                lblUsr.Text = "Available";

                lblUsr.ForeColor = System.Drawing.Color.Green;

            }

        }




    }
Similarly we can check email availability by writing following code








protected void txtId_TextChanged(object sender, EventArgs e)

   {

        if (txtId.Text != string.Empty)

       {

           string strConnection = ConfigurationManager.ConnectionStrings

           ["ConnectionString"].ConnectionString;

            string strSelect = "SELECT COUNT(*) FROM Users WHERE emlAddress = @Email";

           SqlConnection con = new SqlConnection(strConnection);

            SqlCommand cmd = new SqlCommand(strSelect, con);

           cmd.Parameters.AddWithValue("@Email", txtId.Text.Trim().ToString());

 

            con.Open();

            int result = (Int32)cmd.ExecuteScalar();

           con.Close();

 

            if (result >= 1)

            {

               imgId.ImageUrl = "unavailable.png";

               imgId.Visible = true;

                lblId.Text = "Email already registered";

              lblId.ForeColor = System.Drawing.Color.Red;

            }

            else

           {

               imgId.ImageUrl = "tick.png";

               imgId.Visible = true;

                lblId.Text = "Available";

               lblId.ForeColor = System.Drawing.Color.Green;

            }

       }

    }

More Details : `Live Training in jaipur`

ExecuteScalar Example In Asp.Net C#

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  string strSelect = "SELECT COUNT(*) FROM Users WHERE Username =   @Username AND Password = @Password";

SqlConnection con = new SqlConnection(strConnection);

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = CommandType.Text;

cmd.CommandText = strSelect;

SqlParameter username = new SqlParameter("@Username",SqlDbType.VarChar   ,50);

username.Value = txtUserName.Text.Trim().ToString();

cmd.Parameters.Add(username);

SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);

password.Value = txtPassword.Text.Trim().ToString();

cmd.Parameters.Add(password);

con.Open();

int result = (Int32)cmd.ExecuteScalar();

con.Close();

if (result >= 1)

Response.Redirect("Default.aspx");

else

lblMsg.Text = "Incorrect Username or Password";

 More Details : 
Live Training in jaipur

Cursor in SQL

Remove All User Defined Store procedures, Views and Triggers

Example DataAdapter : use DataAdapter in asp.net

</html>

Check UserName Email Availability In Asp.Net Ajax

C# CODE

More Details : Live Training in jaipur

ExecuteScalar Example In Asp.Net C#

More Details : Live Training in jaipur

More Details : `Live Training in jaipur`

More Details : `Live Training in jaipur`