Create a JSON string in C#

{
    "Name" : "22", 
    "Age" : "0", 
    "FullAddress" : "-5", 
    "Interest" : "0"
}

to create a json string as shown below, here is what you could use.

string myJsonString =   "{"
                        + "\"Name\": \"" + model.FirstName + "\", "
                        + "\"Age\": \"" + model.Age + "\", "
                        + "\"Address\": \"" + model.FullAddress + "\", "
                        + "\"Interests\": \"" + model.Interests +"\" "
                        + "}";

How to Pimp my Visual Studio – Change style / font / colors and more..

I recently bumped into this very-awesome site called : Studio Styles. Here is a link to what exactly it’s all about. It basically allows you to change your VS color scheme.

You can browse through the various color schemes submitted by other users and download it. You can even submit/create your own VS color scheme. Once downloaded the installation is pretty straight forward, you just need to import the settings you have downloaded using Visual Studio > Tools > Import and Export Settings.... Read faq for more info.

So, here’s what I created :)
Download this color scheme. (* Uses Droid Sans Mono font)

Preview :

This setting works great with DROID SANS MONO font. You can download and install the font (DROID SANS MONO) from here.

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 pass parameters to CLR Stored Procedures using C#

This article is in continuation to my previous article : How to create a CLR Stored Procedure using C# and Visual Studio where I have discussed on how to write your first CLR Stored procedure and what/why/how to use CLR stored procedure, how to deploy and lots more.

Now coming back to this post. In this article I will show you how to pass an input parameter to the CLR stored procedure. For the example I am using a Product table and the input parameter is an integer value.

So, below is how the CLR stored procedure should look like

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();

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

now build and deploy your CLR stored procedure. And test your stored procedure as shown below

USE [ProductsDB]  
GO  
EXEC [dbo].[GetProductsByPrice]  
@price = 200  
GO

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 move an item to top of list using Linq

Say I have a list of students like the one shown below, and I want a specific item to be on the top of the list.

Here in the example below I want the Student with “Top” as his FirstName to be on the top of the list. So here is how you do it.

var students = new List<Students> { 
    new Students{Id = 1, FirstName = "Yasser", LastName = "Shaikh"},
    new Students{Id = 2, FirstName = "Jayesh", LastName = "Mandal"},
    new Students{Id = 3, FirstName = "Siddhesh", LastName = "Vasani"},
    new Students{Id = 4, FirstName = "Top", LastName = "Top"},
    new Students{Id = 5, FirstName = "Roshan", LastName = "Punwani"}
    }; 

Now there are two methods to do this, I’m gonna show both of them, so here goes..

Method 1:

This method does not work on Linq to Sql.

var students = students.OrderBy(m => m.FirstName != "Top").ToList();

Method 2:

This works on both Linq and Linq to Sql.

var index = students.FindIndex(x => x.FirstName == "Top");
var topItem = students[index];
students.RemoveAt(index);
students.Insert(0, topItem);

Hope you found this useful. Cheers !

“The remote server returned an error: (417) Expectation Failed” error while data crawling – [SOLVED]

While working with HttpWebRequest for data crawling, I came across a very weird error,

The remote server returned an error: (417) Expectation Failed

Solution : The System.Net.ServicePointManager class has a static property named Expect100Continue. After setting this value to false, the error stopped.

So, here is what you should set to false,

System.Net.ServicePointManager.Expect100Continue = False  

MSDN Link

How to send email in ASP.NET C# MVC 3 (with sample working code)

Below is a sample working code for sending in a mail using C#, in the below example I am using google’s smtp server. The code is pretty self explanatory, replace email and password with your email and password values,

In case of any queries feel free to write in using the comment box. CHEERS !

public void SendEmail(string address, string subject, string message)  
{  
    string email = "yrshaikh.mail@gmail.com";  
    string password = "put-your-GMAIL-password-here";

    var loginInfo = new NetworkCredential(email, password);  
    var msg = new MailMessage();  
    var smtpClient = new SmtpClient("smtp.gmail.com", 587);

    msg.From = new MailAddress(email);  
    msg.To.Add(new MailAddress(address));  
    msg.Subject = subject;  
    msg.Body = message;  
    msg.IsBodyHtml = true;

    smtpClient.EnableSsl = true;  
    smtpClient.UseDefaultCredentials = false;  
    smtpClient.Credentials = loginInfo;  
    smtpClient.Send(msg);  
}  

How to read data from database using SqlDataReader in C#

Import the System.Data.SqlClient first.

using System.Data.SqlClient;

SqlDataReader provides a way of reading a forward-only stream of rows from a SQL Server database.

SqlDataReader are used when the need is simply to display the records, not update them. You cannot update using SqlDataReader.

The code below is pretty self explanatory(read the comments), in case of queries please feel free to write in using the comment box below.

using System;
using System.Data.SqlClient;

class Program
{
static void Main()
{
// assign connectionString
string connectionString = "your connection string to go here";

using (SqlConnection con = new SqlConnection(connectionString))
{
// Open the SqlConnection.
con.Open();

string command = "SELECT * FROM YASSER_Table"; 

using (SqlCommand command = new SqlCommand(command, con))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} {1}",
reader.GetInt32(0), reader.GetString(1));
}
}

}// end of using (SqlConnection)

}
}

References : 

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

http://www.dotnetperls.com/sqldatareader