How to remove all saved servername, username and password from Sql Server 2008.

How to remove all saved servername, username and password from Sql Server 2008.

Close your sql server. Go to the below file path

C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell

delete the SqlStudio.bin file and now when you open your Sql Server, all your saved server, username and passwords will be gone.

[image here]

Simple but useful !

SqlBulkCopy – Bulk insert using SqlBulkCopy with C# & ASP.NET

So there was this requirement I was working on recently, where I had to make inserts of around 5,000 to 10,000 into a table all at one go. As I am quite used to using ORMs – Entity framework 5 : I initially implemented this bulk insert using Entity Framework 5. When testing my code with around 5,000 records, it took around 5-6 minutes for my code to insert all the 5,000 records.

6 minutes !!! NOT ACCEPTABLE. Enter SqlBulkCopy…

SqlBulkCopy lets you efficiently bulk load a SQL Server table with data from another source. The code is pretty self-explanatory, questions if any can be asked below in the comment section. Cheers !

FYI : With SqlBulkCopy now it took only 1 to 2 seconds for the same 5,000 inserts :D

Table-BulkCopy

public class Employee 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public int BulkInsertIntoEmployees(List<Employee> employees)
{
    using (var scope = new TransactionScope())
    {
        string connectionString = "your-connection-string-here";
        var sqlConnection = new SqlConnection(connectionString);
        var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
        {
            DestinationTableName = "Tbl_Employees",
            BulkCopyTimeout = 6000
        };
        var dataTable = GetDataTableForEmployees(employees);
        sqlConnection.Open();

        sqlBulkCopy.WriteToServer(dataTable);

        scope.Complete();
        sqlBulkCopy.Close();
        sqlConnection.Close();
        sqlConnection.Dispose();
    }
    return employees.Count;
}

private DataTable GetDataTableForEmployees(List<Employee> employees)
{
    var table = new DataTable();
    table.Columns.Add("Id", typeof(int));    
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("Email", typeof(string));

    // note : the order of the field is very important
    // and should be same as the defined in table structure.
    employees.ForEach(data => table.Rows.Add(
                                        data.Id
                                        , data.Name
                                        , data.Email
                                        ));
    return table;
}

How to check the created date and last modified date of a stored procedure in SQL ?

One often has a need to check the last modified date or the created date of a stored procedure in SQL. So, below is an SQL script that allows you to view the stored procedure name, created date and last modified date of the stored procedure.

USE DatabaseName; 
GO SELECT name, create_date, modify_date 
FROM sys.objects 
WHERE type = 'P' AND name = 'StoredProcedureName'
GO

Hope this helps :)

How to change connection string of a dbml file ( LINQ 2 SQL ) dynamically using app.config.

Linq 2 Sql : INQ to SQL is an ORM (object relational mapping) implementation, which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it. LINQ to SQL fully supports transactions, views, and stored procedures.

Problem

I wanted my Linq 2 Sql’s connection string to be defined outside by compiled code, so that I could easily change between connection strings without having to recompile my code. There are many ways discussed on blogs, few of them are as listed below :

but none of them are as effective and easy to maintain as this one.

Solution

So, here is how you do it.

Step 1 : Set the connection property of your .dbml file to “none”.

Step 2 : Create a new separate partial class with the same name as that of the existing partial class for the .dbml file. And set the connectionString property by using the parameterless constructor.

public partial class DataClassesDataContext  
{  
public DataClassesDataContext() : base(ConfigurationManager.ConnectionStrings["Dev-connString"].ConnectionString)  
{  
OnCreated();  
}  
}  

Step 3 : Almost Done ! Lastly you need to define your connectionString in your app.config file, as shown below.

<?xml version="1.0" encoding="utf-8"?>  
<configuration>

<connectionStrings>

<add  
name="Dev-connString"  
connectionString="Data Source=yasser-home;Initial Catalog=pp;Persist Security Info=True;User ID=sa;Password=gogole"  
providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

You can now easily change the connectionString from the app.config file without having to re-compile your code, which would be the case otherwise.

Why did I create a seperate partial class ? Can’t I edit the existing Dbml.designer.cs file ?

Don’t modify Dbml.designer.cs file manually, because it will be rewritten when you add/edit/delete a table, stored proc etc.

Hope this helps :)

Advertisment

How to manually deploy a CLR Stored procedure to SQL Server Mangement Studio

Before we start this article, I have listed a few related post which you would like to check out…

There are many reasons why you would manually want to deploy a CLR stored procedure to SQL Server Management Studio. There are two parts to deploying any CLR Stored Procedure

  • Part I : Deploying Assembly
  • Part II : Deploying CLR Stored Procedure

1. Deploying Assembly

Once your CLR stored procedure is ready, build the project(Shift + F6). You should get the following in your output window(Ctrl + W, O).

Compile complete -- 0 errors, 1 warnings

CLR Stored Procedures -> C:\Yasser\MyClrDemo\bin\Debug\MyClrDemo.dll  

This is the path where your assembly is located, use this path to CREATE ASSEMBLY as follows, remember to set the PERMISSION_SET to ‘SAFE’

CREATE ASSEMBLY HelloWorldAssembly from 'C:\Yasser\MyClrDemo\bin\Debug\MyClrDemo.dll'

WITH PERMISSION_SET = SAFE  

2. Deploying CLR Stored Procedure

Now that we have our assembly created(HelloWorldAssembly), next step is to create a stored procedure which uses this assembly.

Now consider we have the following CLR Stored procedure

public class HelloWorldClass  
{  
[Microsoft.SqlServer.Server.SqlProcedure]  
public static void HelloWorldMethod()  
{  
SqlContext.Pipe.Send("Hello world!\n");  
}  
}  

So keeping the above classname, stored proc name and assembly name in mind below is how your ‘create procedure’ should be like.

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorldAssembly.HelloWorldClass.HelloWorldMethod

Hope this helps :)

Further Reading :

How to return data records from a CLR Stored Procedure

Before reading this article I recommend you please go through the previous two article, where I have already discussed what/why/how to use CLR Stored Procedures.

Lets get started on this one !

Below is a sample code where first I am fetching all products with price is less than 200$ using the

string commandText = "SELECT * FROM Products WHERE PRICE < " + price.ToString();

Using this result-set I am sending back only the following columns as a result-set

– Product Name (as string)

– Price (with a little formatting by adding a ‘$’ symbol to the price eg: $23)

CLR Stored Procedure C# Code

public partial class StoredProcedures  
{  
    [Microsoft.SqlServer.Server.SqlProcedure]  
    public static void GetProductsByPrice(int price)  
    {  
        SqlConnection connection = new SqlConnection("context connection=true");  
        connection.Open();

        string commandText = "SELECT * FROM Products WHERE PRICE < " + price.ToString();

        SqlCommand command = new SqlCommand(commandText, connection);  
        SqlDataReader reader = command.ExecuteReader();

        // Create the record and specify the metadata for the columns.  
        SqlDataRecord record = new SqlDataRecord(  
        new SqlMetaData("Product Name", SqlDbType.NVarChar, 100, 1033, SqlCompareOptions.None),  
        new SqlMetaData("Price", SqlDbType.NVarChar, 100, 1033, SqlCompareOptions.None));

        // Mark the begining of the result-set.  
        SqlContext.Pipe.SendResultsStart(record);

        while (reader.Read())  
        {  
            // Set values for each column in the row.  
            record.SetString(0, reader["ProductName"].ToString());  
            record.SetString(1, "$" +reader["Price"].ToString());

            // Send the row back to the client.  
            SqlContext.Pipe.SendResultsRow(record);  
        }

        // Mark the end of the result-set.  
        SqlContext.Pipe.SendResultsEnd();  
    }  
};

Output

How to create a CLR Stored Procedure using C# and Visual Studio

Below are the points I will be covering in this article

What exactly do I mean by CLR Stored Procedure?

Why do we use CLR Stored Procedure?

Can we write a CLR Stored Procedure using C# and Visual Studio?

Step by Step guide to creating your first CLR Stored Procedure

What exactly do I mean by CLR Stored Procedure?

CLR stands for Common Language Runtime. Stored Procedures, Functions and triggers can be coded in CLR.

Why do we use CLR Stored Procedure?

We use CLR stored procedure for a number of reasons, few of which are as listed below…

1. CLR is faster than T-SQL in many cases.

2. CLR is used to accomplish task that are not possible by T-SQL because of their complexity.

3. Convenient for programmers, as CLR stored procedures can be written in C# or VB or in any other language that .NET framework supports.

Can we write a CLR Stored Procedure using C# and Visual Studio?

Yes, we can. In this article I will show you how to.

Step by Step guide to creating your first CLR Stored Procedure

Now that we have a basic understanding on what CLR stored procedures are and when to use them, lets now get started with how to implement them.

Step 1 : Open Visual Studio (I have used visual studio 2010 and SQL server 2005 for this example). Create new project and select the project type as “Visual C# SQL CLR Database Project” set the .NET framework to 3.5 (why ?)

Step 2 : Once the project is loaded. You should get a prompt like the one below,

Select an existing database connection or you can create a new one.

Step 3 : Once the project loads, right click the project and Add > Stored Procedure.

Give a name to the Stored Procedure and select ‘ok’.

Once the stored procedure is added, you will see a file like the one below

Step 4: Code

Now below is a small code set to get you started. I am preparing a simple sql command SELECT * FROM Products and sending the data using the SqlContext.Pipe.Send()

public partial class StoredProcedures  
{  
    [Microsoft.SqlServer.Server.SqlProcedure]  
    public static void GetAllProducts()  
    {  
        SqlConnection connection = new SqlConnection("context connection=true");  
        connection.Open();

        SqlCommand command = new SqlCommand("SELECT * FROM Products", connection);  
        SqlDataReader reader = command.ExecuteReader();

        SqlContext.Pipe.Send(reader);  
    }  
};  

Step 5: Build and Deploy your CLR Stored Procedure

Once your CLR stored procedure is ready the next step is to build and then deploy the stored procedure.(For successful deployment you will require your database login to be on the sysadmin role group)

P.S : Deploying the stored procedure may take some time and may even cause your visual studio to be ‘not responding’, be prepared for this.

If for whatever reason, the deployment is not successful you can always manually deploy the stored procedure and assembly by referring this article : Manually Deploying a CLR Stored Procedure

Step 6: Deployment Complete !

Once the deployment is successful, open the Sql Server Management studio and now you will see a stored proc added with the same name as that of the above function/method. Also along with the stored procedure an assembly is also added.

Step 6: Execute and Test your CLR Stored Procedure !

This is the last step of this tutorial. Now after your CLR stored procedure is deployed successfully, now its time to test it.

USE [ProductsDB]  
GO  
EXEC[dbo].[GetAllProducts]  
GO  

and on execution of the above CLR stored proc, I should get all the rows of the Products table as seen below.

Phew ! that was long :)

I also plan to write two more related post to this which are

How to pass parameter/input to a CLR stored procedure ?

– How to manually deploy a CLR stored procedure ?

Cheers !

Further Reading :

SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure

CLR Stored Procedure and Creating It Step by Step

Writing CLR Stored Procedures in C# – Introduction to C# (Part 1)

How to keep a specific row as the first result in SQL using order by

Introduction
To understand this, I have prepared a user table with two columns: first_name and last_name. Below is the result of a simple select statement.

Sql query: Simple select and order by first_name

SELECT * FROM dbo.Users 
ORDER BY dbo.Users.first_name

Screenshot from Sql Server Management Studio:

Now I want to have the row with firstName as “Yasser” to be the first row, so using the following query I have managed to achieve this.

Sql query: Simple select and order by first_name and excluding ‘Yasser’

SELECT * FROM dbo.Users 
ORDER BY CASE dbo.Users.first_name WHEN 'Yasser' THEN 0 ELSE 1 END, dbo.Users.first_name

Screenshot from Sql Server Management Studio:

Hope this helps you. :)

How to Call Stored Procedure from Entity Framework in ASP.NET MVC

You can call Stored Procedure and Views using Entity Framework, I recently had to use one and didn’t find any good article describing this, so I am writing this one down.

Below is a Stored Procedure I have prepared for this example. As you can see in this stored procedure, there is 1 input parameter and 1 output parameter. I have tried to keep the stored proc as simple as possible :)

CREATE PROCEDURE SP_TEST_EF
@username varchar(50),
@total INT OUTPUT
AS
BEGIN
SELECT * FROM dbo.Users WHERE username = @username
SET @total = (SELECT COUNT(*) FROM dbo.Users)
END
GO

Now that we have defined our stored proc, let move towards our next step.

Open your .edmx file and right click on the designer and select the “Update Model From Database”

then select the new stored procedure added from the list and select “Finish”.

Now right click on the designer again, and select the “Add -> Function Import” option this time.

Select the stored procedure from the list of Stored Procedures, give a nice function name I have given mine “Test_EF” now as I have my stored proc return a value I have used Complex Type. To create a complex type simply click on the “Create a New Complex Type” and Visual Studio will create one for you. Click ok.

And so if all is ok, you should be able to see the function added to your model browser as shown in the screenshot below.

And now you can use the stored procedure like any other table call,

For example:

using(var context = new SimplePmEntities())
{
    var data = context.Test_EF("yasser", 0);
}

Hope you found this useful ! Cheers !