RSS

Monthly Archives: November 2010

Elegant Way of Executing a Stored Procedure using LINQ?

I was working in a project today and I was trying to create a generic way of using a DataContext class to execute a stored procedure.  Of course there’s the way of dragging a stored procedure onto the designer, etc… but I’m not interested in this at the moment.  TBQH, I haven’t even been doing the ORM mapping in this manner lately after using the System.Data.Linq.Mapping namespace to map a POCO (Plain ‘Ole CLR Object) to an actual SQL table (as well as using ColumnAttribute‘s to map the properties to columns.  This is extremely helpful for larger 2.0 projects that were recently converted to 3.5.   Some might argue that this isn’t how LINQ was supposed to be used or that it makes it more complicated.   First of all, I don’t think that there is any right or wrong way to use LINQ.  Secondly, I find it can be a pain to have to remove the table from the designer, reconnect via Server Explorer, refresh the database, re-drag onto designer over-and-over when making changes to tables, views, procedures, etc…  This is also problematic if you don’t have access to a production database from your dev environment.  Most web servers I experience don’t have Visual Studio 2008 installed so it’s not like I can quickly generate a newer set of LINQ-to-SQL classes.  If a table column name changes, I simply refactor the corresponding property in my library and I’m good to go.

[Table(Name = “dbo.FooTable”)]
public class Foo
{
#region Table Columns / Properties

[Column]
public long FooId { get; set; }

[Column]
public string FooName { get; set; }

[Column]
public string FooDescription { get; set; }

#endregion Table Columns / Properties
}

Easy.

public class Database<TDataContext, TEntity>
{
// …
}

Take the previous data access helper class.  Let’s say I want to create just a generic function to execute a stored procedure against my database.  Let’s call it “ExecuteCommand”.

public static void ExecuteCommand(string command, object[] parameters)
{
if (string.IsNullOrEmpty(command))
throw new ArgumentNullException(“command”);

using (var database = new TDataContext())
database.ExecuteCommand(command, parameters);
}

Basic.  The implementation is pretty mindless as well.

Database<MyDataContext, TFoo>.ExecuteCommand(“CommandName”, null);

This sucks when you have parameters because I haven’t found any documentation on how to execute a procedure elegantly using this approach.  It appears that the DataContext.ExecuteCommand approach is best-suited for on-the-fly dynamic SQL…. gross!  I also read (need to find the link again) that some super-sleuthing uncovered that parameter substitution is silently ignored.  (I’ll have to check back on that)

I did find a way to implement using a stored procedure….

string command = string.Format(“exec Command ‘{0}’, {1}”, “string”, 1);
Database<MyDataContext, TFoo>.ExecuteCommand(command, null);

That is disgusting.  Not a huge fan.  What if you have a procedure that has 5, 10, 20 parameters? Ouch.  What if your input parameters contain ‘ ‘s? You would then have to replace all ‘ with a ”.

Any suggestions?  I’m hoping that there is a much more elegant / succinct way of doing this that I haven’t found yet.  I’ve tried to “RTFM” as they say but a couple thousand pages of LINQ reference books and I’ve got nothing.

Here’s a little 2.0 throwback of what I’m trying to accomplish.  Like I said before, there has to bge a more elegant way of doing this with the 3.5 Framework / LINQ / DataContext that doesn’t involve dragging-and-dropping procedures onto the designer.

public static int ExecuteProcedure(List<SqlParameter> parameters, string command)
{
try
{
using (DBManager manager = …)
{
manager.Open();
manager.CreateParameters(parameters.Count);

for (int i = 0; i < parameters.Count; ++i)
manager.AddParameters(i, parameters[i].ParameterName, parameters[i].Value);

return (int) manager.ExecuteScalar(CommandType.StoredProcedure, command);
}
}
catch (SqlException)
{
return 0;
}

}

Help me from the following please!!

StringBuilder query = new StringBuilder(“exec Command “);
query.AppendFormat(“{0},”, foo.FooProperty1);
query.AppendFormat(“‘{0}’,”, foo.FooProperty2.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty3.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty4.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty5.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty6.Replace(“‘”, “””));
query.AppendFormat(“‘{0}'”, foo.FooProperty7.Replace(“‘”, “””));

Database<MyDataContext, Foo>.ExecuteCommand(query.ToString(), null);

C#, LINQ

Referenced by: http://www.willasrari.com/blog/elegant-way-of-executing-a-stored-procedure-using-linq/000312.aspx

Advertisements
 
Leave a comment

Posted by on November 22, 2010 in C#, LinQ

 

Add CheckBox inside DataGridView in windows application

This article will explain you how to insert Checkbox inside DataGridView in windows application. This article is for beginners.

First create a windows application from Visual Studio Menu File->Project-> Then select C#->Windows Form Application.

Then add DataGridView from toolbox of Visual Studio. And also add One Button to test the CheckBox Value inside DatGridView.

Now from the property window of DataGridView Select the Columns Property.
You will see this screen:

Click on the (Collection) button from the above shown window.
You will see the below screen:

 
From this window click on “Add” button.
You will see below window:

From the above window select “Type” as “GridViewCheckboxColumn” Column as shown in above window
Then press Add Button from above Window.
Now you have added the checkbox DataGridView in windows application. Now it’s time to add some code in your .cs file .

Now create one function, which will return a DataTable with sample data containing in it. This Data Table  will bind to DataGridView.

  /// <summary>

        /// Create DataTable to add to DataGridView

        /// </summary>

        /// <returns>DataTabe</returns>

        private DataTable SampleDataTable()

        {

            DataTable dt = new DataTable("MyDataTable");

 

            //Create columns and add to DataTable;

 

            DataColumn dcID = new DataColumn("ID");

            dt.Columns.Add(dcID); //ID column created and add to DataTable

 

            DataColumn dcSomeText = new DataColumn("SomeText");

            dt.Columns.Add(dcSomeText); //LastName column created and add to DataTable

 

            //Now Add some data to the DataTable

            DataRow dr;

            for (int count = 0; count <= 9; count++)

            {

                dr = dt.NewRow();

                dr["ID"] = count;

                dr["SomeText"] = "Some Text " + count;

                dt.Rows.Add(dr);

            }

 

            return dt;

 

        }

On the FormLoad_Event add this line of code to bind SampleDataTable() as a datasource of of windows application like this

private void Form1_Load(object sender, EventArgs e)

{

      myDataGridView.DataSource = SampleDataTable();

 

}

Now on Button click Event add this line of code to check what all rows user has selected from DataGridView.

private void btnSubmit_Click(object sender, EventArgs e)

{

     foreach (DataGridViewRow dr in myDataGridView.Rows)

     {

         if(dr.Cells[0].Value != null) //Cells[0] Because in cell 0th cell we have added checkbox

          {

                    MessageBox.Show("Rows " +dr.Index + " selected");

          }

      }

}

Now, its time to run the application,once you run your application you will see the output window like this:

Now, select the check box and press the Get Selected Rows Button. You will see this window:

 

Happy Coding!!!

praveen_7108_CheckBox_GridView.zip

Referenced by:  http://www.dotnetspark.com/kb/151-add-checkbox-inside-datagridview-windows.aspx

 
Leave a comment

Posted by on November 16, 2010 in C#, Window Application

 

SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

Introduction

Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. This feature works with RTM (Ready to Manufacture) version of SQL Server 2008 and does not work on CTP (Community Technology Preview) versions.

To create an Index, follow the steps:

  1. Create a Full-Text Catalog
  2. Create a Full-Text Index
  3. Populate the Index

1) Create a Full-Text Catalog

Full-Text can also be created while creating a Full-Text Index in its Wizard.

2) Create a Full-Text Index

3) Populate the Index

As the Index is created and populated, you can write the query and use it in searching records on that table which provides better performance.

For example, we will find the Employee Records that have “Marking” in their Job Title.

FREETEXT( ) is the predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

  • Separates the string into individual words based on word boundaries (word-breaking)
  • Generates inflectional forms of the words (stemming)
  • Identifies a list of expansions or replacements for the terms based on matches in the thesaurus

CONTAINS( ) is similar to Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search, then we need to provide the “and” or “or” in the search, else it will throw an error.

Collapse
USE AdventureWorks2008
GO

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing OR Assistant');

SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing AND Assistant');
GO

Conclusion

Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CONTAINS() with “and” or “or” operators.

 

Referenced by: http://www.codeproject.com/KB/database/SQLServer2K8FullTextSearh.aspx

 
Leave a comment

Posted by on November 1, 2010 in SQL Server