Easily Connect to a Database In C#

First of all we need to make a database file for our project. Here I'm using an OLEDB connection so I create my database in Microsoft Access; see:


1.jpg 
Enter the data and field names into the database like in this table:
 
Field Name Data Type Max Length
user text 20
pass text 20

Now Start Visual Studio and start a new project or open an existing project.

If you want to start a new project, please use the following instructions.

1. First start Visual Studio then click on "New Project" in the Sart Page, as in the following:

 2.jpg 

2. Select a Destination folder path for the new project and name.
 
3.jpg
 
3. Now we connect our database to the project. For that please review the following screen.

 Click On Server Explorer [Mostly placed on left hand side. or you can access it from here. {View -> Server Explorer} or press Ctrl+Alt+S]

Open Server Explorer
 
4.1.jpg

Right-click on "Data Connections" and then click on "Add Connections"; see:
 
4.2.jpg

Select your Data Source then click "Continue" [Here we use OLEDB so I select "Microsoft Access Database File", but you can select something else as per your need.]
 
4.3.jpg

Now the screen for Add Connection appears. Here we select our database file from where we save it.
 
4.4.jpg

Select your database file from your computer drive and click on "Open", as in:
|
4.5.jpg

Now to check whether our connection is successful or not. Click On "Test Connection". Now click on "OK" to close the test connection message box.

After that click on "Add Connection's 'OK' button for complete our database add operation.".

4.6.jpg
 
Now see in Server Explorer. Here you will see the database and its fields in "Data Connection" as in the following:
 
4.7.jpg

Now right-click on the database name (i.e. data.accdb) and select Properties (Alt+Enter).

4.8.jpg
 
Its opens a "Properties" sidebar. Click on the Connection String and copy that. We need it for out connection coding.

Now We Start Our Code

First of all take two textbox field and one button.

Text Box 1 txt_user for username
Text Box 2 txt_pass for password
Button 1 btn_ok for submite

Add References

using System.Data.OleDb;

Now for the button's Click Event (for that double-click on the "OK" button) place this code:
 

string str = "";

// Paste your connection string that you copy from your database Properties.OleDbConnection con = new OleDbConnection(str);

OleDbCommand cmd =new OleDbCommand("SELECT COUNT(*) FROM tablename WHERE user = '" + txt_user.text +"' AND pass = '" + txt_pass.text +"'", con);

con.Open();try{int i;

i =Convert.ToInt32(cmd.ExecuteScaler());

if (i == 1){MessageBox.Show("Success");

}

else{MessageBox.Show("Invalid User Name or Password");

}

}

Catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

Finaly

{

Con.Close();

}


You can also perform "Insert", "Delete", "Update" etc. operations using this code. For that just change the query and replace "ExecuteNonQuery()" with "ExecuteScaler()".

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now