How to connect SQL Server database using C#
It can work with different types of databases. It can work with the most common databases such as Oracle and Microsoft SQL Server.
Am use the Microsoft SQL Server Management 2008R2, which is a free database software provided by Microsoft.
>> SQL server Management 2008R2 Download Link
Database: Create database finacial;
use finacial;
and then
Database table created:
CREATE TABLE [recharge](
[id] [int] IDENTITY(1,1) NOT NULL primary key,
[mobilenetwork] [varchar](50) NULL,
[mobilenumber] [varchar](50) NULL,
[amount] [varchar](40) NULL,
[date] [varchar](60) NULL,
[status] [varchar](50) NULL );
and then open the Microsoft Visual Studio - > New project -> console Application
On Header Add Extention: using System.Data.SqlClient; using System.Data;
System.Data.SqlClient:
Connection – The first Main step is the connection. The connection to a database normally consists of the parameters.
The fundamental logic is the same you know you create a connection object prepare a command to execute that retrieve the results and then finally close the connection.
First it should be very clear you know how to create a connection object
how to execute the command you know we have seen those steps so let's quickly
sqlconnection con=new sqlconnection();
create the connection object source connection con is equal to new sql connection. obviously and then look the connection class
sqlconnection class there are three overloaded version of the constructor
1.sqlconnection.sqlconnection(); - initializes a new instance of the system.data.sqlclient .sqlconnection class.
2.sqlconnection.sqlconnection(string connectionstring); - intializes a new instance of the system.data.sqlclient .sqlconnection class. when given a string that contains the connection string . Connectionstring: The connection used to open the Sql server database
3. sqlconnection.sqlconnection(string connectionstring,sqlcredential credential) - intializes a new instance of the system.data.sqlclient .sqlconnection class given a connection string, that does not use integrated security= true and a system.data.sqlClient.SqlCredential object that contains the used id and password
one version is doesn't take any parameter. If we click the down arrow it shows the second overloaded version is it accepts a connection string parameter Let's pass the connection string parameter
If you have dotnet application to connect the SQL server still needs to provide the information about what is the name of the server what's the database that you want to connect to what's the user Id and password now it's just like for the example Let's I have a SQL server now I want to connect the SQL server as a user now I need to specify the name of the server to which I want to connect and I have to specify what type of the authentication do I want to use
There are two types:
1. windows Authentication
2. SQL server Authentication
for example: If I use SQL Server Authentication are now I have to provide the login Id and password to connect the SQL server whereas if I use windows Authentication I don't have to provide them.
Similarly even for a dot net Application if it has to connect to a database then we will have to specify what is the name sever. Name of the database what user Id and password are you using to depending on the type of authentication.
If it is windows authentication don't need to provide because whatever credentials that you have used to login to the computer will be used to the SQL server also
Sql Server connection string
connetionString="Data Source=ServerName; Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
If you have a named instance of SQL Server, you'll need to add that as well.
Connect via an IP address
so here data source which is nothing but the name of the server so here I am working with the local installation of SQL Server on my machine network, so I am just specifying the name of the computer or IP address of that computer and using two backslashes and type name of the SQL server name like have you put like SQL express or SQLEXPRESS and then semicolon and then database within that SQL server because if you look at the SQL server it has got several databases so which database do you want to connect to
so I want o connect the database as finacial so I specify that using database keyword equal to finacial and semicolon and some people call it database like initial catalog so you can either specify it Initial catalog or database and integrated security equal to true, and some other people specify SSPI
And then I want to create, my SQL connection object con, and this connection object does not know to which SQL server it has to connect because you didn't tell it which server which database, so I just pass the constructor of this SQL connection object because we have one of the overloaded version which takes the connection string and either does this or just create the connection object and then say connection dot connection equal to a connection string property
Sql server connection through windows Aunthentication:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace sqlwindowsam
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string connetionString = null;
SqlConnection cnn ;
connetionString = "Data Source=DESKTOP-19Q8E5J\\SQLEXPRESS;Initial Catalog=finacial;integrated security=true";
cnn = new SqlConnection(connetionString);
try
{
cnn.Open();
MessageBox.Show ("Connection Open..... ! ");
cnn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Cannot open connection... ! ");
}
}
}
}
and then create the sqlDataAdapter and SqlDataAdapter I explain detail in the next post and just create the
sqlDataAdapter sda= new SqlDataAdapter();
sql server connection through console Application:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace sqlsampl
{
class Program
{
static void Main(string[] args)
{
using( SqlConnection con = new SqlConnection("Data source=.\\SQLEXPRESS;database=finacial; Integrated security=true;")){
SqlDataAdapter sda= new SqlDataAdapter("select * from recharge",con);
DataTable dt = new DataTable();
sda.Fill(dt);
foreach(DataRow row in dt.Rows)
{
Console.WriteLine(row["mobilenumber"]);
}
Console.ReadKey();
}
}
}
}
Using statement - The purpose of the using statement in the C# language is to provide a simpler way to specify when the unmanaged resource is needed by your program, and when it is no longer needed.
and I want to do execute this query like "select * from recharge"
and SQL connection object within the brackets and then after that command, we need to open the connection called open method and finally close the connection close()
and then now I run the application and might expect the table will appear on the output
>>How to Uninstall a SQL Server Instance in SQL Server 2008R2 and 2008