Get Table Schema Programatically

August 18th, 2006 Comments

In order to retreive table schema programatically, we can use the GetSchemaTable method of the DataReader as follows:

using (OleDbConnection cn = new OleDbConnection())
{
    OleDbCommand cmd;
    DataTable schemaTable;
    OleDbDataReader reader;

    cn.ConnectionString = @"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local);";
    cn.Open();

    cmd = new OleDbCommand("Employees", cn);
    cmd.CommandType = CommandType.TableDirect;

    using (reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
    {
        schemaTable = reader.GetSchemaTable();

        foreach (DataRow row in schemaTable.Rows)
        {
            foreach (DataColumn col in schemaTable.Columns)
                Console.WriteLine(col.ColumnName + " = " + row[col].ToString());

            Console.WriteLine();
        }

        reader.Close();
    }

    cn.Close();
}

Client Side Reports in Visual Studio 2005

August 17th, 2006 Comments

Perhaps the best kept secret (or at least the least discussed feature) of Visual Studio 2005 is the client-side reports. Client-side reports consists of the Report Viewer Control and it’s accompanying Report Designer that comes standard with Visual Studio 2005 Professional and up.

This feature can be used to develop ASP.NET or WinForms solutions that sport SQL Server Reporting Services style reports, without having to deploy those reports to a Reporting Server. Reports are deployed as RDLC files with your solutions. In fact one doesn’t even need SQL Server, since these reports can be programatically bound to objects such as DataSets, a huge plus for ditributed n-tier designs where the UI layer does not have direct access to the data store. This also means that one can use any imaginable back-end data store including XML and CSV files as long as data can be loaded into binable objects.

The report viewer control is similar to Reporting Services report viewer, with nifty features such as paging, searching, and export (PDF, Excel, CSV) features. My only complain with the ASP.NET version of the report viewer is that it does not directly support printing. Reports have to be exported to PDF in order for one to print. This was a gotcha with the first versions of Reporting Services report viewer as well, but later they added printing support to the control (perhaps through ActiveX) in Reporting Services SP1.

You can find more information about this feature at GotReportViewer.

Securing ASP.NET 2.0 Apps Using Membership and Role Providers

August 17th, 2006 Comments

I recently designed an intranet application using ASP.NET 2.0 and really loved the membership and role providers that tremendously simplifified our implementation of security features in the application. Membership and role information can be stored in a SQL Server database or another repository such as Active Directory. For our intranet application it made sense to use Active Directory as the membership provider and SQL Server as the role provider.

Configuring Role and Membership Providers:

Essentially everything is configured declaritively using Web.config:

In the above configuration file, note that we first specify our connection strings starting at line 2. LocalSqlServer points to a SQL Server database which has been configured using aspnet_regsql. The second connection string points to the domain controller for membership authentication.

In the authentication section we specify that we are using forms authentication, and provide the URL for our logon page.

In the roleManager section we configure our role provider, pointing back to LocalSqlServer as the role repository.

In the membership section we configure our membership provider pointing back to ADConnectionString (domain controller) specified in the connectionStrings section.

Finally we restrict users from accessing certain folders based on their roles using location sections (role-based security).

Copy Virtual Machine With SQL Server Installed

August 16th, 2006 1 comment

I copied the VHD file of a virtual machine that had SQL Server 2000 installed on it. After running NEWSID from Sysinternals and giving my virtual machine a new name, I had to deal with the fact that SQL Server would not recognize my new machine name. Luckily I found this FAQ on Vyas’s web site that solved my problem. Essentially I had to drop and add my SQL Server as follows in order for things to start working correctly:

EXEC sp_dropserver 'Your_OLD_Computer_Name'
GO

EXEC sp_addserver 'Your_NEW_Computer_Name', 'local'
GO

Restart your SQL Server service. Connect using Query Analyzer and run the following command (It should output the new server name):

SELECT @@SERVERNAME
GO

 

Other Articles