How to set Database Connection in C#.NET - ASP.NET for Excel, Access and Sql Server

// How to set connection string in c#.net /asp.net for 1)Excel , 2)MS-Acess and  3)Sql Server
// You can set connection string in your web.config file as describe below...

//Below Code describe the first step of ADO.Net , Set Connection string to connect .Net to database for communication between them.

<connectionStrings>
<add name="hhcsnot"  connectionString="Server=database_server_ip;Initial Catalog=databasename;User ID=username;Password=yourpassword"/>
</connectionStrings>



//1. EXCEL TO .NET-[Top]
// How Retrieve Data From Excel to Gridview in C#.NET , ASP.NET
//Database Connection String for Excel 2003 for C#.NET, ASP.NET HELP 
//
using System.Data.OleDb;
 protected void btn_showst_Click()
    {
        OleDbConnection con;
        try
        {
            string file = "book";
            string fn = "Sheet1";
            string fname2 = "[" + fn + "$]";
            con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\" + file + ".xls;Extended Properties=Excel 8.0;");
            con.Open();
            OleDbCommand cmd = new OleDbCommand("select * from " + fname2 + "", con);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            GridView1.DataSource = dr;
            GridView1.DataBind();
            con.Close();
        }
        catch (Exception ex)
        {
            Label2.Text = ex.Message;
        }
    }


/// 2. MS-ACCESS  TO .NET-[Top]
//Connection String for ms-access to C#.NET, ASP.NET 
// How Retrieve Data From Access to Gridview 


using System.Data.OleDb;
protected void btn_showst_Click()
    {
    OleDbConnection con;

    con = new OleDbConnection("Microsoft.Jet.OLEDB.4.0"      connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\databasename.mdb");

   // Here Access database should be in App_Data Folder..


  con.Open();
  OleDbCommand cmd = new OleDbCommand("select * from  tablename", con);
  OleDbDataReader dr;
  dr = cmd.ExecuteReader();
  GridView1.DataSource = dr;
  GridView1.DataBind();
  con.Close();
}

// 3. Sql Server To .NET -[Top]
// Connection String For Sql Server to C#.NET, ASP.NET
// How retrieve Data From Sql Server to Gridview


using System.Data.SqlClient;

protected void btn_showst_Click()
    {

SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database.mdf; Integrated Security=True;User Instance=True");
 // Here Sql Database file should be in App_Data Folder..

// or
con.Open();

SqlCommand com = new SqlCommand("select * from " + tablename + " ", con);

SqlDataReader dr = com.ExecuteReader();

GridView1.DataSource = dr;
GridView1.DataBind();
 con.Close();
}

NOTE : //////   Two Type of SqlConnection String First for sql file and Second for sql server you have to almost use 2nd type. Passing IP address of Database server , database name , User id & password.

SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database.mdf; Integrated Security=True;User Instance=True");

// In below Server name or ip address is come there , Initial Catalog for Database name; user id -password // is sql user id and password by defaul sql user is 'sa' and password you should be set it 
//
SqlConnection con = new SqlConnection( "Server=127.0.0.1;Initial Catalog=Database;User ID=sa;Password=123456" );

-[Top]