How Connect to the Database(SQL) using Visual Studio

How Connect to the Database(SQL) using Visual Studio

   June 15, 2015       Unknown

---

The are the questions asked by many of the .NET beginners (i am also one of them).But i don't found best explanation on internet . As a programmer it consumes more time in searching internet. So,to save your precious time i wanna show you how the connection between the C#.NET and SQL database is established (it also useful when you trying to connect with ASP.NET).I will explain this in step by step manner.


In this tutorial i gonna show you how to connect to the database and i also show you how to test the connection using a simple windows form

Step:1
Open Visual studio

Step:2
Create a simple form by simply by drag and draping the controls (controls are nothing but text boxes,radio buttons and text fields etc.)

Before going to start we have to know the basics of visual studio 
-->Server Explorer:    
                                Server explorer gives the organized view of the server objects like Databases, Tables,Views,etc
-->Form:
                  Here the form is GUI that helps us to design the windows applications by using the custom controls
-->Tool Box:
                     Tool box contains all the controls (like buttons,text boxes,check box,etc).By drag and drop we can place all these  in from.
-->Properties Window:
                      Properties window gives the organized view of all properties that are applicable to the Controls.

Step:3
Now it's time to do the real thing Goto the "TOOLS" menu and click on the "Connect to Database" then you will get a window like below

-->1)In the above step1(in above figure) we have to select the Data source as a Microsoft SQL  Server( Because here i am connecting to the SQL server)

-->2)In second step give the name of the server (ex:'USER19-PC' this is my name of the sql server in the similar way you have to fill with your sever name.)

-->3)Here you have two choices

            if you select the "Windows Authentication " there is no need to enter the password in               connection string.

            if you select the "SQL server authentication" we have to specify the username and password in the connection string.(just do not think very high nothing is happens if you select any option ).

-->4)choose your database and click on ok button then you will get(while you doing all these things you have to open the SQL server simultaneously)

Step4:

In this step use the following code to connect to the database.we should write this code in Form.cs file

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;

//these(below) two libraries are used to import the necessary files for SQL connection

using System.Data.SqlClient;

using System.Data.OleDb;

namespace WindowsDatabaseConnection{ 

   public partial class Form1 : Form    

   {        

public Form1()       

{     

InitializeComponent();     

}

        private void sqlconnect_Click(object sender, EventArgs e)      

{            

string constring = "Data Source=USER-PC;Initial Catalog=thebadboyDB;User ID=sa;Password=123";     //Connection string 

//here USER ID means the server username and passwords

//here we are creating the  object for SqlConnection using connection string as parameter

        SqlConnection cn = new SqlConnection(constring);     //connect to the database 

cn.Open();        

MessageBox.Show("This Connection established Successfully");   

cn.Close();

        }

        private void InputUser_TextChanged(object sender, EventArgs e)   

{

        }

        private void SubmitButton_Click(object sender, EventArgs e)    

{

           //string query1 = InputUser.Text;       

  //string query2 = InputPasswd.Text;                    

            //storing query in the string

            //string s = "create table student(ID int,name varchar(20))"; 

//creating object for the SqlCommand class         

string insert1 = "insert into employee values(@p1,@p2)";         

SqlCommand cmd1 = new SqlCommand(insert1,cn);         

//creating Sql datareader..

            cn.Open();      

cmd1.Parameters.AddWithValue("@p1 ", InputUser.Text);   

cmd1.Parameters.AddWithValue("@p2", InputPasswd.Text);     

SqlDataReader dr;                  

dr = cmd1.ExecuteReader();       

dr.Read();         

messageLabel.Text = "One row inserted successfully";

           dr.Close();       

  cn.Close();      

  }  

 }

}

In the above From application First button "SQLConnection Test " tests the server connection if the connection is true then it will shows a message like "Connection successful".

If you enter the values into the text fields they directly stored into the table in your SQL database under name of "Student(table name)".

You can check your details like this in sql server

using   select * from customer;

the output will be like this:

I hope you will learn how to connect to the database using this tutorial.If u find any difficulties in understanding this tutorial please leave a comment or send me feedback to ravi.swayampu123@gmail.com

                                                         .......... See you Soon..................