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]

19 comments:

  1. sir i need coadings for countdown timer in online examination project ...........

    ReplyDelete
    Replies
    1. Find it here...

      http://hemantrautela.blogspot.in/2012/09/timer-ajax-control-cnet-aspnet.html

      Delete
    2. This comment has been removed by a blog administrator.

      Delete
  2. Singh

    Here is a database compatible with .NET, Silverlight, Windows Phone, Mono, Monodroid, and Monotouch:
    http://www.kellermansoftware.com/p-43-ninja-net-database-pro.aspx

    ReplyDelete
  3. sir i need code of library managemnet,but not entire code i need only connectivity code,in which data may inserted,udated.

    ReplyDelete
    Replies
    1. Hello vikram,
      you can use ExecuteNonQuery()
      istead of
      SqlDataReader dr = com.ExecuteReader();
      for insert and update, change your query as required, and also remember one more thing sql injection.
      But I will suggest you study about stored procedure and use it for your project.

      Delete
  4. sir i have made an html form , and i want it to connect with an access database . can u please help me to do so?

    ReplyDelete
    Replies
    1. you need to use any language eg. c# , php etc...
      for this you have to know basics of these languages.

      Delete
  5. HelpFul Code...:)

    ReplyDelete
  6. Sir I created a project on C# with sql server database and create setup file in C# in our system, So when we install the setup on other system, so it is required to install sql server in that system or not.

    and Which and what are the procedure i have to follow? plese tell me sir.

    ReplyDelete
    Replies
    1. Hi Akaash,

      Are you know about deployment process in detail. Because here you can add dependency dll also{you can google it}. So that you have not require to install Sql server.

      If you create setup without these dependency then you have to Install Sql server and also need reconfig connection string in your project.

      Delete
    2. Sir, what is dependency dll, please explain me in detail and how can i add dependency or what i have to search in google. please explain me in detail.

      Delete
  7. This comment has been removed by a blog administrator.

    ReplyDelete
  8. Nice Article
    Here are also some articles with explanation and demo apps for connect c# with access and insert,delete operation with access database for beginners
    http://geeksprogrammings.blogspot.com/2013/10/connect-access-database-with-c.html
    http://geeksprogrammings.blogspot.com/2013/09/delete-record-from-access-database.html

    ReplyDelete
  9. sir i want ms access database connectivity code to insert information about employee so plz send me connectivity code

    ReplyDelete
  10. This comment has been removed by a blog administrator.

    ReplyDelete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. This comment has been removed by a blog administrator.

    ReplyDelete
  13. This comment has been removed by a blog administrator.

    ReplyDelete