Wednesday 29 April 2015

What is Stored Procedure??


SQL Server Stored Procedure

(Introduction) 
 
Overview
A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again.  So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.
In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.
Take a look through each of these topics to learn how to get started with stored procedure development for SQL Server.
You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these topics.

how to get country Name using IP Address in asp.net

 public string GetCountryByIP(string ipAddress)
    {
        string strReturnVal;
        //string ipResponse = IPRequestHelper("http://api.ipinfodb.com/v3/ip-country/?key=(api key deleted for security purposes)&ip=" + ipAddress);
        //string ipResponse = IPRequestHelper("http://iplocationtools.com/ip_location_api.php?ip={0}" + ipAddress);
        string ipResponse = IPRequestHelper(" http://ip-api.com/xml/?ip=" + ipAddress);
 

        //return ipResponse;
        XmlDocument ipInfoXML = new XmlDocument();
        ipInfoXML.LoadXml(ipResponse);
        XmlNodeList responseXML = ipInfoXML.GetElementsByTagName("query");

        NameValueCollection dataXML = new NameValueCollection();

        dataXML.Add(responseXML.Item(0).ChildNodes[2].InnerText, responseXML.Item(0).ChildNodes[2].Value);

        strReturnVal = responseXML.Item(0).ChildNodes[1].InnerText.ToString(); // Contry
        strReturnVal += "(" + responseXML.Item(0).ChildNodes[2].InnerText.ToString() + ")";  // Contry Code
        return strReturnVal;
    }

    public string IPRequestHelper(string url)
    {

        HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create(url);
        HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse();

        StreamReader responseStream = new StreamReader(objResponse.GetResponseStream());
        string responseRead = responseStream.ReadToEnd();

        responseStream.Close();
        responseStream.Dispose();

        return responseRead;
    }

Get Data from Gridview into Datatable in Asp.net using C#, VB.NET

Categories: Asp.net , C#.Net , Gridview , VB.NET , XML
Introduction:

Here I will explain how to get data from gridview to datatable in asp.net using c#, vb.net or fetch or get gridview data and insert into datatable in asp.net or get gridview data into dataset using c#, vb.net with example.

Description:
 
In previous articles I explained insert selected gridview rows into database in asp.net, take database backup in sql server 2008, encrypt and decrypt passwords in sql server 2008, Google Map show info windows when click on marker in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how get data from  gridview to datatable in asp.net using c#, vb.net.

To get data from gridview and insert into datatable we need to write the code like as shown below

C# Code


DataTable dt = new DataTable();
dt.Columns.Add("userid", typeof(int));
dt.Columns.Add("username", typeof(string));
dt.Columns.Add("firstname", typeof(string));
dt.Columns.Add("lastname", typeof(string));
dt.Columns.Add("designation", typeof(string));
foreach (GridViewRow row in gvDetails.Rows)
{
int userid = int.Parse(row.Cells[0].Text);
string firstname = row.Cells[1].Text;
string lastname= row.Cells[2].Text;
string username = row.Cells[3].Text;
string designation = row.Cells[4].Text;
dt.Rows.Add(userid,username,firstname, lastname, designation);
}

VB.NET Code


Dim dt As New DataTable()
dt.Columns.Add("userid", GetType(Integer))
dt.Columns.Add("username", GetType(String))
dt.Columns.Add("firstname", GetType(String))
dt.Columns.Add("lastname", GetType(String))
dt.Columns.Add("designation", GetType(String))
For Each gvrow As GridViewRow In gvDetails.Rows
'Find checkbox control in gridview for particular row
Dim chkSelect As CheckBox = DirectCast(gvrow.FindControl("chkSelect"), CheckBox)
'Condition to check checkbox selected or not
If chkSelect.Checked Then
Dim userid As Integer = Integer.Parse(gvrow.Cells(1).Text)
Dim username As String = gvrow.Cells(2).Text
Dim firstname As String = gvrow.Cells(3).Text
Dim lastname As String = gvrow.Cells(4).Text
Dim designation As String = gvrow.Cells(5).Text
dt.Rows.Add(userid, username, firstname, lastname, designation)
End If
Next

If you want to check it in complete example first design one table EmployeeInfo in your database as shown below



Now create one new web application and open your aspx page and write the code like as shown below


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Insert Gridview Data to Database in Asp.net using SQLBulkCopy</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:gridview id="gvDetails" runat="server">
<headerstyle backcolor="#df5015" font-bold="true" forecolor="White">
</headerstyle></asp:gridview>
</div>
<br />
<asp:button font-bold="true" id="btnInsert" onclick="btnInsert_Click" runat="server" text="Insert Gridview Data"><br />
<asp:label id="lblMsg" runat="server">
</asp:label></asp:button></form>
</body>
</html>

Now right click on your application à Select Add New Item à Select XML file à Give name as “sample.xml” and click OK

Once we add xml file open it and write code like as shown below



<users>
<user>
<userid>1</userid>
<firstname>Suresh</firstname>
<lastname>Dasari</lastname>
<username>SureshDasari</username>
<designation>Team Leader</designation>
</user>
<user>
<userid>2</userid>
<firstname>Mahesh</firstname>
<lastname>Dasari</lastname>
<username>MaheshDasari</username>
<designation>Software Developer</designation>
</user>
<user>
<userid>3</userid>
<firstname>Madhav</firstname>
<lastname>Yemineni</lastname>
<username>MadhavYemineni</username>
<designation>Business Analyst</designation>
</user>
</users>

After completion of xml file now open aspx page codebehind behind file and add following namespaces

C# Code


using System;
using System.Data;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

After completion of adding namespaces you need to write the code like as shown below


DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//Bind Data to Gridview
GetXMLData();
}
}
// This method is used to get xml node values and bind to gridview
protected void GetXMLData()
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("Sample.xml"));
dt = ds.Tables[0];
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
// Insert data in database using SqlBulkCopy
protected void btnInsert_Click(object sender, EventArgs e)
{
String strConnection = "Data Source=Suresh;Initial Catalog=MySampleDB;Integrated Security=True";
DataTable dt = new DataTable();
dt.Columns.Add("userid", typeof(int));
dt.Columns.Add("username", typeof(string));
dt.Columns.Add("firstname", typeof(string));
dt.Columns.Add("lastname", typeof(string));
dt.Columns.Add("designation", typeof(string));
foreach (GridViewRow row in gvDetails.Rows)
{
int userid = int.Parse(row.Cells[0].Text);
string firstname = row.Cells[1].Text;
string lastname= row.Cells[2].Text;
string username = row.Cells[3].Text;
string designation = row.Cells[4].Text;
dt.Rows.Add(userid,username,firstname, lastname, designation);
}
using (SqlConnection con = new SqlConnection(strConnection))
{
con.Open();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "EmployeeInfo";
sqlBulk.WriteToServer(dt);
con.Close();
}
lblMsg.Text = "Details Inserted Successfully";
lblMsg.ForeColor = System.Drawing.Color.Green;
}

VB.NET Code


Imports System.Data
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Partial Class VBCode
Inherits System.Web.UI.Page
Private dt As New DataTable()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
'Bind Data to Gridview
GetXMLData()
End If
End Sub
' This method is used to get xml node values and bind to gridview
Protected Sub GetXMLData()
Dim ds As New DataSet()
ds.ReadXml(Server.MapPath("Sample.xml"))
dt = ds.Tables(0)
gvDetails.DataSource = dt
gvDetails.DataBind()
End Sub
' Insert data in database using SqlBulkCopy
Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim strConnection As [String] = "Data Source=Suresh;Initial Catalog=MySampleDB;Integrated Security=True"
Dim dt As New DataTable()
dt.Columns.Add("userid", GetType(Integer))
dt.Columns.Add("username", GetType(String))
dt.Columns.Add("firstname", GetType(String))
dt.Columns.Add("lastname", GetType(String))
dt.Columns.Add("designation", GetType(String))
For Each row As GridViewRow In gvDetails.Rows
Dim userid As Integer = Integer.Parse(row.Cells(0).Text)
Dim firstname As String = row.Cells(1).Text
Dim lastname As String = row.Cells(2).Text
Dim username As String = row.Cells(3).Text
Dim designation As String = row.Cells(4).Text
dt.Rows.Add(userid, username, firstname, lastname, designation)
Next
Using con As New SqlConnection(strConnection)
con.Open()
Dim sqlBulk As New SqlBulkCopy(strConnection)
'Give your Destination table name
sqlBulk.DestinationTableName = "EmployeeInfo"
sqlBulk.WriteToServer(dt)
con.Close()
End Using
lblMsg.Text = "Details Inserted Successfully"
lblMsg.ForeColor = System.Drawing.Color.Green
End Sub
End Class

</div>