How to Connect SQL Server Databse in ASP.NET Application

[RekaRajagopal]

hi
i am using visual studio 2008. and sql server 2005. now my doubt is how to connect the mdf in asp.net application with C#.???
am using design in web form in asp.net applicaton with C#
can any body ask ??/
how to connect the database and how to connect data in gridview???

[Kitten84]

String connectionString= "(Enter path to database here)";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand command = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "(sql statement here)";
SqlDataReader dr = cmd.ExecuteReader();
conn.Close();

Let me know if this helps.

[sknake]

SqlConnection , SqlCommand and SqlDataReader all implement IDisposable and should be cleaned up in code or wrapped in using() statements:

/// <summary>
    /// Concatenates the query string
    /// </summary>
    /// <param name="Sql"></param>
    /// <returns></returns>
    private static string GetText(List<string> Sql)
    {
      StringBuilder sb = new StringBuilder();
      for (int i1 = 0; i1 < Sql.Count; i1++)
        sb.AppendLine(Sql[i1]);
      return sb.ToString();
    }

    /// <summary>
    /// Builds a connection string
    /// </summary>
    /// <param name="server"></param>
    /// <param name="database"></param>
    /// <returns></returns>
    internal static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }

    /// <summary>
    /// Gets data after today using logic to determine what today is
    /// </summary>
    /// <returns></returns>
    internal static DataTable GetTable()
    {
      DataTable result = default(DataTable);

      List<string> Sql = new List<string>();
      Sql.Add("IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table");
      Sql.Add("Create Table #Table");
      Sql.Add("(");
      Sql.Add("  [Date] DateTime,");
      Sql.Add("  EventName varchar(100)");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-3, 'Event 1')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-2, 'Event 2')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-1, 'Event 3')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate(), 'Event 4')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+1, 'Event 5')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+2, 'Event 6')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+3, 'Event 7')");
      Sql.Add("");
      Sql.Add("Select *");
      Sql.Add("From #Table");
      Sql.Add("Where [Date] >= Cast(Floor(Cast(GetDate() as float)) as DateTime)");
      string query = GetText(Sql);
      string connStr = BuildSqlNativeConnStr("apex2006sql", "Scott");
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            result = new DataTable();
            result.Load(dr);
          }
        }
        conn.Close();
      }
      return result;
    }

[grovalmitch]

Better you place your connection string in web config

<configuration>
  <connectionStrings>
    <add name="SQLDbConnection"
         connectionString="Server=servername; Database=pubs; User Id=username; password=password"
         providerName="System.Data.SqlClient" ></add>
  </connectionStrings>
</configuration>

and connect the database

    string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
    SqlConnection connection = new SqlConnection(connectionString);
    connection.Open();

program source

http://asp.net-informations.com/data-providers/sql-connection.htm

mitch

[annaharris]

Thanks sknake for sharing the code for establishing a connection between SQL Server and Asp.Net.

[BMXDad]

A better option is to use the EntLib (Enterprise Library) collection.

EntLib Download Site