January, 2008

  • Wriju's BLOG

    ADO.NET : OleDbCommand Parameterized Query Sequence Rule

    • 9 Comments

    While working with MS Access database with OleDbCommand you might have faced issue that your parameterized update statement is not working. The most frustrating part is that it also does not throw any runtime error. So ideally you paralyzedJ.

     

    Golden rule is that you need to maintain the exact sequence of parameters you have specified in the sql statement. So while adding the parameter value sequence is very important.

     

    Command parameter collection addition sequence has to match with the sql statement sequence.

     

    String sSQL = "UPDATE PHONEDB SET [NAMES] = @pNames WHERE [ID] = @pID";

     

    OleDbConnection conn = new OleDbConnection("ConnectionString");

    OleDbCommand cmd = new OleDbCommand();

     

    cmd.Connection = conn;

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = sb.ToString();

     

     

    cmd.Parameters.AddWithValue("@pNames", "Your Name");

    cmd.Parameters.AddWithValue("@pID", 12345);

     

     

    Now if you alter the sequence like,

     

    cmd.Parameters.AddWithValue("@pID", 12345);

    cmd.Parameters.AddWithValue("@pNames", "Your Name");

     

    Things would not work as expected.

     

    Namoskar!!!

  • Wriju's BLOG

    LINQ to SQL : Understanding Mapping

    • 6 Comments

    In LINQ to SQL you play with pure .NET objects and write your LINQ on top of that. But question might arise how this magic happens. Though we say that it is object, the question might arise in our mind that there has to be a mechanism through which it connects. Yes, there is. That is DataContext which controls the background transaction. But again how it understands that your class is actually representing a database object, is only though Attribute magic.

     

    There could be different database components,

     

    Database Æ DataContect

    Table Æ Class

    Column Æ Property/Field

    Relationship Æ Property/Field

    View Æ Class

    Stored Procedure Æ Function

     

    Now when we write code for that how we define them?

     

    Let us have a database TestDB and a table Emp.

     

    CREATE TABLE [dbo].[Emp](

          [Id] [int] IDENTITY(1,1) NOT NULL,

          [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

          CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED

    (

          [Id] ASC

    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

     

    Step 1:

    ++++

    Create a table representation,

    public class Employee

    {

        public int Id { get; set; }

     

        public string Name { get; set; }

    }

     

    Now map this class for dbo.Emp table through attribute.

     

    [Table(Name="dbo.Emp")]

    public class Employee

    {

    public int Id { get; set; }

     

    public string Name { get; set; }

    }

     

     

    Step 2:

    ++++

    Now you need to map columns,

     

    [Column(IsDbGenerated=true, IsPrimaryKey=true)]

    public int Id { get; set; }

     

    Now this is because the Id column is Primary Key and automatically generated column.

     

    [Column]

    public string Name { get; set; }

     

    Notice here I am not specifying the “Name=” is because the property name and the column names are identical.

     

    Step 3

    ++++

    You have Database which ideally DataContext should be able to handle, so if you do not want to create class for that, you can very well do that. Like

     

    DataContext db = new DataContext(@"Connection String");

    var query = db.GetTable<Employee>();

     

    But if you really would like to get the strongly typed behavior with statement completion then the approach would be little different.

     

    public class TestDB : DataContext

    {

        public TestDB(string sConn): base(sConn){}

     

        public Table<Employee> Emps;

    }

     

    Now if you start writing the same code this will give you the itellisense and statement completion,

     

    TestDB db = new TestDB(@"Connection String");

    var query = db.Emps;

     

    var query1 = from e in db.Emps

                 where e.Id == 1

                 select e;

     

    Hope you liked this.

     

     

    Namoskar!!!

  • Wriju's BLOG

    MSDN Tech Posters

    • 1 Comments

    I found an article at http://blogs.msdn.com/cbowen/archive/2007/12/09/got-tech-posters.aspx for Tech Posters.

    Namoskar!!!

  • Wriju's BLOG

    Funny but real SDLC

    • 2 Comments

    I was browsing http://blechie.com/WPierce/archive/2007/12/03/The-Way-Things-Work-in-the-Software-Business.aspx

    A nice view which is self explanatory reality

    TheWayThingsWork_thumb

    Namoskar!!!

Page 1 of 1 (4 items)