Recent twitter entries...

Save SQL Server login details in SQL Management Studio

Posted by Brad | Posted in Recommendations, SQL Server | Posted on 11-10-2011

Today I found out something that I found out years ago, if you like me need to use multiple SQL servers but can’t remember things by IP address or would prefer not having to type login information every time you want to connect then use registered servers.

Registered servers allow you to easily check if a server is running, create groups of servers, use user-friendly names for your servers, and import/export your registered server groups.

To view and add a registered server, start up SQL Server Management Studio go to View > Registered Servers. Then in the Registered Servers pane, right-click Local Server Groups and click New Server Registration. Alternatively if you already have them open right now you can just right click the instance and select register then enter your friendly name… super easy!

SaveSQLServerPasswords Save SQL Server login details in SQL Management Studio


Then just enter the server name, select the authentication, key in the User name and Password if necessary, and check Remember password.

Now each time you start up SQL Server Management Studio, your registered servers will be viewable in the Registered Servers pane. Simply double-click the server you wish to navigate and that server will be opened within the Object Explorer.

Now I need to find out how to make this the default view when opening SSMS..

Continue Reading

Databinding a GridView in C# with ADO.NET

Posted by Brad | Posted in C# | Posted on 13-07-2010

Something simple, you want to use ADO.NET to bind data from SQL Server to a GridView, here is your code behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
public partial class Default2 : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    string conn = ConfigurationManager.ConnectionStrings["STRINGNAME"].ConnectionString;
    SqlConnection con = new SqlConnection(conn);
 
    using (con)
    {
      SqlCommand cmd = new SqlCommand("SELECT * FROM Table", con);
      con.Open();
      SqlDataReader rdr = cmd.ExecuteReader();
      gv.DataSource = rdr;
      gv.DataBind();
    }
  }
}

Here is your control

<asp:GridView ID="gv" runat="server" />

Continue Reading

Programatically building an HTML table in C#

Posted by Brad | Posted in C#, Ramblings | Posted on 23-06-2009

These are the minimum using directives to add at the top of the code behind for building an HTML table from a SQL Server to a SqlDataReader concisely.

using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;

Your table in the aspx file can be as simple as

<table id="Products" runat="server"></table>

And the code behind that does the work,  taking the value from a dropdown list control called Category, and retrieving data from Northwind is

using(SqlConnection cnn = new SqlConnection("Data Source=YOURSERVER; Database=Northwind; UID=YOURDBUSERID; PWd=YOURDBPASSWORD"))
      {
        SqlCommand cmd;
        SqlDataReader rdr;
 
          HtmlTableRow row = new HtmlTableRow();
 
          HtmlTableCell cell = new HtmlTableCell("th");
          cell.InnerText = "Product ID";
          row.Cells.Add(cell);
 
          cell = new HtmlTableCell("th");
          cell.InnerText = "Product Name";
          row.Cells.Add(cell);
 
          cell = new HtmlTableCell("th");
          cell.InnerText = "Units in stock";
          row.Cells.Add(cell);
 
          Products.Rows.Add(row);
 
          cmd = new SqlCommand("SELECT ProductID, ProductName, UnitsInStock FROM Products WHERE CategoryID = "
            + Category.SelectedValue, cnn);
          cnn.Open();
          rdr = cmd.ExecuteReader();
 
          while (rdr.Read())
          {
            row = new HtmlTableRow();
 
            cell = new HtmlTableCell();
            cell.InnerText = rdr["ProductID"].ToString();
            row.Cells.Add(cell);
 
            cell = new HtmlTableCell();
            cell.InnerText = rdr["ProductName"].ToString();
            row.Cells.Add(cell);
 
            cell = new HtmlTableCell();
            cell.InnerText = rdr["UnitsInStock"].ToString();
            row.Cells.Add(cell);
 
            Products.Rows.Add(row);
 
          }
        }

Continue Reading