Database connection in sqlserver 2005 in c#

[tiwari.vinay96]

how to make database connection in sqlserver 2005 in c#

[sknake]

Here is an example using the SQL Native driver:

using System;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;

namespace daniweb
{
  public partial class frmSql : Form
  {
    public frmSql()
    {
      InitializeComponent();
    }

    private void button3_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=ServManLeather;Integrated Security=True;";
      const string query = "Select GetDate() As [Today], @@SERVERNAME as [ServerName]";
      DataTable dt;
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            dt = new DataTable();
            dt.Load(dr);
          }
        }
        conn.Close();
      }
      MessageBox.Show(string.Format("Rows {0:F0}", dt.Rows.Count));
    }

Here is an example using the OleDb driver for SQL server:

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Linq;
using System.Data.SqlClient;
using System.Threading;
using System.Runtime.Remoting.Messaging;
using System.Diagnostics;

namespace daniweb
{
  public partial class frmOleDbGrid : Form
  {
    private DataTable dt;

    public frmOleDbGrid()
    {
      InitializeComponent();
    }

    private void BuildSql()
    {
      const string connStr = @"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Scott;Data Source=apex2006sql";
      const string query = "Select * From Experiment";
      using (OleDbConnection conn = new OleDbConnection(connStr))
      {
        conn.Open();
        using (OleDbCommand cmd = new OleDbCommand(query, conn))
        {
          using (OleDbDataReader dr = cmd.ExecuteReader())
          {
            if (dt != null)
              dt.Dispose();
            dt = new DataTable();
            dt.Load(dr);
            dataGridView1.DataSource = dt;
          }
        }
        conn.Close();
      }
    }

I would recommend using the first piece of code / sql native.

[Ionelul]

If you use Visual Studio 2008, you can consider using Linq2Sql.
Here is a very good tutorial to start with:
http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx
Microsoft's tutorial: http://msdn.microsoft.com/en-us/library/bb425822.aspx

Ionut

[maxwik]

There are different options and differnet providers when connecting to SQL Server. To have an overview you can go to http://www.connectionstrings.com/sql-server-2005 and see the different options. Good luck!