Connecting to sql server in c#
Back to learning
Created: 07/11/2013
Connecting to sql server in c#
In this basic tutorial we will lern how to make simple Asp.Net application that retrieve sample data from database Sql Server using C#.
Is the same using WinForms
Basically we have main application (Asp.Net, Winforms or MvcAsp) that will consume another subproject that retrieve data from database.
1) Spet 1: Creat the main application using Asp.Net like this:
Just simple Asp.Net website with one masterpage, and Defaul.aspx file
2) Add label to the default page and call it like "lblData":
in this label we will write the result returned from database
3) At this time we have our main application. Now add new subproject to the solution:
C#->Class Library and call it "myData", so now we have the following:
You can see that i have add new class called Data.cs
this will be our main data access class where we will put all the code necessary to retrieve data
4) This step is very important!
Now we need to link our data acces project with main Asp web site:
Right click on "WebSite3" or the name of your application then click "Add reference"
in solutions->projects select "myData" and click ok.
5) To simplify our job convert the class Data.cs to static so we dont need to make any instance of it later in our WebSite project.
6) Create new method called for example GetSampleData() like this:
7) This is the main part of all the tutorial: Add the code to this method like this:
first of all add this namespaces:
using System.Data.SqlClient;
using System.Data;
this is the basic structure:
But you can see that we have "YYYYYYYYY" and "XXXXXXXX", in the first one we need to puth our connection string you can see how to do it just viewing this simple tutorial:
Basic database connection string C# and Sql Server using UDL file
the second one "XXXXXXX" is our simple sql query to retrieve data, i will use this one:
"select * from sampleData"
so now we have this final method:
8) Create some sample Sql Serve table and add some date like this:
Sql Query:
Data:
insert into sampleData select 'this is our first sql data'
9) Our summary at this point:
10) Now we will use our data project in the main WebSite:
Go to Default.aspx.cs
So this code will display the column "value" from the table "sampleData" from the first row that we have created above.
11)
Result:
########## ########## ################ ########### ########### ######## ############### ########## #########
There are huge amount of diferent ways and combinations to achive the same result, but basically this will give you an idea of how to make the all the cycle from 0.
Create main project (Web or Win or even Console), then add new project that will be responsable of the data access, then link this both project using References, and finally use your data access project in the main one.
This example give you good practice to work with layer separation.
We have 2 layers in this project, one is the Presentation (WebSite) and the second is DataAccess (myData project)
To achieve the same result you also can read about SqlDataReader instead of SqlDataAdapter.
Of course you can reed not only one row, but thousands of rows from the same table and display them looping the DataTable that we have used here:
For example to display 5 rows from our table:
make some inserts by yourself:
id value
1 this is our first sql data
2 this is second row
3 this is third row
4 this is 4 row
5 this is 5 row
and then modify Default.aspx.cs code:
Result:
If you have any question or suggestion leave me a comment.
Good luck :)
Full project: Download
Connecting to sql server in c#
In this basic tutorial we will lern how to make simple Asp.Net application that retrieve sample data from database Sql Server using C#.
Is the same using WinForms
Basically we have main application (Asp.Net, Winforms or MvcAsp) that will consume another subproject that retrieve data from database.
1) Spet 1: Creat the main application using Asp.Net like this:
Just simple Asp.Net website with one masterpage, and Defaul.aspx file
2) Add label to the default page and call it like "lblData":
<asp:Label ID="lblData" runat="server" Text=""></asp:Label>
in this label we will write the result returned from database
3) At this time we have our main application. Now add new subproject to the solution:
C#->Class Library and call it "myData", so now we have the following:
You can see that i have add new class called Data.cs
this will be our main data access class where we will put all the code necessary to retrieve data
4) This step is very important!
Now we need to link our data acces project with main Asp web site:
Right click on "WebSite3" or the name of your application then click "Add reference"
in solutions->projects select "myData" and click ok.
5) To simplify our job convert the class Data.cs to static so we dont need to make any instance of it later in our WebSite project.
namespace myData
{
public static class Data
{
}
}
6) Create new method called for example GetSampleData() like this:
namespace myData
{
public static class Data
{
public static string GetSampleData()
{
}
}
}
7) This is the main part of all the tutorial: Add the code to this method like this:
first of all add this namespaces:
using System.Data.SqlClient;
using System.Data;
this is the basic structure:
namespace myData
{
public static class Data
{
public static DataTable GetSampleData()
{
SqlConnection connection = new SqlConnection(@"YYYYYYYYY");
SqlCommand cmd = new SqlCommand("XXXXXXXXXX", connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
connection.Open();
da.Fill(ds);
}
catch (Exception e)
{
throw;
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
connection.Close();
}
return ds.Tables[0];
}
}
}
But you can see that we have "YYYYYYYYY" and "XXXXXXXX", in the first one we need to puth our connection string you can see how to do it just viewing this simple tutorial:
Basic database connection string C# and Sql Server using UDL file
the second one "XXXXXXX" is our simple sql query to retrieve data, i will use this one:
"select * from sampleData"
so now we have this final method:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace myData
{
public static class Data
{
public static DataTable GetSampleData()
{
SqlConnection connection = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=tests;Data Source=MAK-PC\SQLEXPRESS");
SqlCommand cmd = new SqlCommand("select * from sampleData", connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
connection.Open();
da.Fill(ds);
}
catch (Exception e)
{
throw;
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
connection.Close();
}
return ds.Tables[0];
}
}
}
8) Create some sample Sql Serve table and add some date like this:
Sql Query:
create table sampleData
(
id int identity,
value varchar(200)
)
Data:
insert into sampleData select 'this is our first sql data'
9) Our summary at this point:
10) Now we will use our data project in the main WebSite:
Go to Default.aspx.cs
add this namespaces:
using myData;
using System.Data;
System.Data just to use DataTable class and myData to use our new method to retrieve data.
and add this code:
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
DataTable data = myData.Data.GetSampleData();
lblData.Text = data.Rows[0][1].ToString();
}
catch(Exception ex)
{
lblData.Text = ex.ToString();
}
}
}
So this code will display the column "value" from the table "sampleData" from the first row that we have created above.
11)
Result:
########## ########## ################ ########### ########### ######## ############### ########## #########
There are huge amount of diferent ways and combinations to achive the same result, but basically this will give you an idea of how to make the all the cycle from 0.
Create main project (Web or Win or even Console), then add new project that will be responsable of the data access, then link this both project using References, and finally use your data access project in the main one.
This example give you good practice to work with layer separation.
We have 2 layers in this project, one is the Presentation (WebSite) and the second is DataAccess (myData project)
To achieve the same result you also can read about SqlDataReader instead of SqlDataAdapter.
Of course you can reed not only one row, but thousands of rows from the same table and display them looping the DataTable that we have used here:
try
{
DataTable data = myData.Data.GetSampleData();
lblData.Text = data.Rows[0][1].ToString();
}
For example to display 5 rows from our table:
make some inserts by yourself:
id value
1 this is our first sql data
2 this is second row
3 this is third row
4 this is 4 row
5 this is 5 row
and then modify Default.aspx.cs code:
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
DataTable data = myData.Data.GetSampleData();
foreach(DataRow row in data.Rows)
lblData.Text += row[1].ToString() + "<br>";
}
catch(Exception ex)
{
lblData.Text = ex.ToString();
}
}
}
Result:
If you have any question or suggestion leave me a comment.
Good luck :)
Full project: Download