Posts

Showing posts with the label C#

Hey, I rewrote UNION by way of UNPIVOT

Yesterday a team mate of mine asked me to review a piece of SQL script that looked like a good candidate for UNPIVOT. Below is the amended version of that code (I obfuscated the data and added a simple data initialization using the WITH clause): ;with u as ( -- data setup select * from ( values(1, 1, 1, 1), (2, 1, 1, 0), (3, 1, 0, 0), (4, 0, 0, 0) ) t(UserID, HasDbSkills, HasBackendSkills, HasFrontEndSkills) ) select * from ( select UserID, 0 as SkillType, 1 as DevelopmentSkill from u where HasDbSkills = 1 union select UserID, 1 as SkillType, 1 as DevelopmentSkill from u where HasBackendSkills = 1 union select UserID, 2 as SkillType, 1 as DevelopmentSkill from u where HasFrontEndSkills = 1 ) t(UserID, SkillType, DevelopmentSkill) order by UserID, SkillType Indeed, after a quick analysis, this script turned out to be ripe for UNPIVOT:...
Post a Comment
Read more

CREATE DATABASE (SMO)

Create a SQL Server Database by using SMO In this small tutorial I will show you how to create a database with SMO and C# in Visual Studio 2008. I will use a Console application. Prerequisites: SQL Server 2005 is installed Visual Studio 2008 is installed Code Assemblies Your C# project must reference: System System.Data Micrososft.SqlServer.ConnectionInfo Micrososft.SqlServer.Management.Sdk.Sfc Micrososft.SqlServer.Smo Namespaces The source class file must use: System System.Data.SqlClient Micrososft.SqlServer.Management.Common Micrososft.SqlServer.Management.Smo class ConsoleSmo {    public static Main()   {      string connLocal = "Data Source=(local); Integrated Security=SSPI;"      SqlConnection connection = new SqlConnection (connLocal);      Server server = new Server ( new ServerConnection (connection));      Database db = new Database (server, “TestDb...
Post a Comment
Read more

CREATE DATABASE (ADO.NET and C#)

Create a SQL Server Database Programmatically You need to create a SQL Server database programmatically by using ADO.NET and C#. Here is a quick recipe how to do that. using System; using System.Data.SqlClient; SqlConnection localConn = new SqlConnection ( "(local); Integrated security=SSPI; database=master" ); string SQLCommand = "CREATE DATABASE TestDb" ; SqlCommand DbCreate = new SqlCommand (SQLCommand, localConn); try { localConn.Open(); DbCreate.ExecuteNonQuery(); Console .WriteLine("DataBase is Created Successfully"); } catch (System. Exception ex) { Console .WriteLine(ex.ToString()); } finally { if (localConn.State == ConnectionState .Open) { myConn.Close(); } } This code creates a database similar to the SQL Server Model database and places its files to the default location. If you want to create a database with custom properties, please check out MSDN .
Post a Comment
Read more