Database connectivity means when we have a database which is of some other source even on the same project first we have to create a connection with if so that we perform any CRUD operation in the database so for connecting SQL database with c# window application we use using System.Data.SqlClient; namespace library. We can also use it like using System.Data.*; or any other database i.e. Oledb or Odbc.
There are some components which should be kept in mind to get started with SQL connections in c#
- Connection String
- Open Connection
- SQL Command
- Run /Execute SQL Command
- Close Connection
To create connection sqlConnection is used.
1. Connection String:
SqlConnection conn = null;
conn=new SqlConnection(@"Data Source=localhost\SQLExpress;Initial Catalog=awais;Integrated Security=True;Integrated Security=True ");
Getting Connection String from .udl (Universal Data Link) file:
- Create a text file and change its extension (you can change the extension of the file by opening it and saving it as anyname.udl, click Save to save the file) there is no restriction for the name of the udl file.
- Open .udl file where you saved it. by double click on it.
- You will get a dialog box containing Data Link Properties.
- You can test the connection string from this dialog box. After you test the connection string close the dialog box by clicking OK.
- Open .udl file in any text editor. You can also open the file in the editor by right click -> Edit/ Edit in Notepad++.
- You will see something like
1<span class="typ">Integrated</span> <span class="typ">Security</span><span class="pun">=</span><span class="pln">SSPI</span><span class="pun">;</span><span class="typ">Initial</span> <span class="typ">Catalog</span><span class="pun">=</span><span class="pln">database name</span><span class="pun">;</span><span class="typ">Data</span> <span class="typ">Source</span><span class="pun">=</span><span class="pln">server name</span><span class="pun">;</span>
- This is your connection String. You can simply copy it and use it in your project.
2. Open Connection:
For opening the connection using sqlConnection object.open();
3. SQL Command:
SqlCommand is used for executing SQL query. It is the default constructor. It takes two parameters, SQL query, and SQL connection object.
And we pass connection string in it like:
SqlCommand cmd=new SqlCommand(query,conn);
4. Run /Execute SQL Command:
Now to execute the query different methods are used. Like ExecuteNonQuery() and to retrieve data from database SqlDataReader() is used.
ExecuteNonQuery() returns an integer value which tells how many rows are affected by the query.
cmd = new SqlCommand(query, conn);
int rowsEffected = cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
SqlDataReader reader = cmd.ExecuteReader();
Reader reads the data from database in it. ExecuteReader is used to execute the command in sqlCommand.
To check if there is any data in the database we check rows of the reader. Reader.hasRows check the availability of the data rows in the database table.
Reader.Read is used to read rows.
The collection of DataTables is called DataSet. DataSet type is used to store many DataTables in a single collection. DataSet acts as a set of DataTable instances. Datasets are used to store the complete database data in it and break the connection with the database.
DataSet name = new DataSet();
following are some common dataset members.
Accepts all changes to the DataSet
Removes all rows from all tables in the DataSet – that is, removes all data.
Creates a new DataSet with all tables having the same Table structure including any constraints and relationships. No Data is copied.
Same as for the DataSet Clone() but it includes all Data.
7. Close Connection:
Once you have opened the connection for database connectivity it should be closed by using Close() method
Hope that you like this tutorial. Stay tuned for more upcoming tutorials. Stay Blessed!