CODEWITHSUNDEEP
c#.net
sab669
sab669

Reputation: 4104

How to determine the type of value of a column in a DataRow?

I have the following method: public static void Post(this DataRow dr, string tableName, SqlConnection conn)

I'm trying to build an INSERT statement for my DataRow, but I'm running into difficulty trying to determine the type of data each column is in order to format the SQL statement correctly.

I have this:

public static void Post(this DataRow dr, string tableName, SqlConnection conn)
{
    string sql = "INSERT INTO " + tableName + " VALUES(";

    for (int i = 0; i < dr.ItemArray.Length; i++)
    {
        sql += "'" + dr[i].ToString() + "'";

        if (i != dr.ItemArray.Length - 1)
            sql += ",";
    }
}

But there are numerous Date and Numeric columns in the table, so obviously not all of my values can be enclosed in '.

I know that starting from a DataTable, you can do something like this: myDt.Columns[0].Datatype which I could then use to branch the logic out and format the values accordingly but I cannot seem to find how to access this information when given just a DataRow.

Worst case scenario, I can refactor the application to simply take a DataTable instead of an individual DataRow, but this would require significant work so I'd much prefer to find a way to do it as is.

If it matters, this is a winforms application / .NET 4.5

Upvotes: 2

Views: 3371

Answers (2)

Suamere
Suamere

Reputation: 6258

The best OO Type-Safe way to answer this, I believe, is that you shouldn't be passing direct SQL to be executed. Create a POCO with actual C# Types that represents your table.

T Insert<T>(T classToBeInserted) //where T matches a table

T Select<T>(T classForClauses) //where T matches a table

If your generic T matches the table name, and the fields in the class match the column names/types in your table, you can paramaterize and genericize all of your information.

So, if you have a table Person with columns Name varchar(wutev) and Age INTEGER, you should create this class:

public class Person
{
    public string Name {get; set;}
    public int Age {get; set;}
}

Then your SQL class could look like this:

public class MyPersonalORM
{
    private string _connectionString;

    public MyPersonalORM(string connectionString)
    {
        _connectionString = connectionString;
    }

    public T Insert<T>(T insertPlz)
    {
        string tableName = typeof(T).Name;
        Dictionary<string, string> parameters = ...; // Use reflection to get the properties of T, then reflect into insertPlz to find the values.

        SqlParameter ... // Add each value in the dictionary to a parameter collection.

        // Do a typical Insert, but using Parameters.
    }

    public T Select<T>(T classForClauses)
    {
        string tableName = typeof(T).Name;
        Dictionary<string, string> parameters = ...; // Use reflection to get the properties of T, then reflect into insertPlz to find the values.

        SqlParameter ... // Add each value in the dictionary to a parameter collection.

        // Do a typical ADO Selct, but using Parameters for the clauses.
    }
}

You have reflection at your fingertips. It's up to the consumer of the ORM to create classes that map to the database types.

Why? Cuz that's how E.F. does it. It never asks the database what types it is. E.F. requires that you properly map your resources using C# types.

Of course the real answer is: Don't use ADO. Use E.F. :)

Upvotes: 1

Andrey Korneyev
Andrey Korneyev

Reputation: 26886

If your DataRow actually was added to the rows of some DataTable somewhere outside your method - then you can access it using DataRow.Table property.

Otherwise you can use dr[i].GetType() and check is it DateTime or numeric.

Upvotes: 3

Related Questions