Using old-school ado but can anyone improve this using L2S or dynamic sql

I am rolling through all the cols on a particular row for processing. I am looking in general to replace using ado.net with L2S or some dynamic sql that can positionally read the columns on a particular row for processing.

This is my test code. I apologize for inefficiencies like sending the DataTable down to find the col name. It is a simple, quick test. I would like to convert to L2S or use a dynamic sql query.

Thanks in advance.

...

    DateTime Start = new DateTime(2010, 04, 05);
    DateTime End = new DateTime(2010, 04, 05);
    DataTable dt = getData(Start,End); 

    for (int ii = 0; ii < dt.Rows.Count; ii++)
    {
        processDR(dt.Rows[ii], dt.Columns.Count, dt);
    }

}

// Process DataRow Columns ordinally 
protected void processDR(DataRow dr, int colCount, DataTable dt)
{
    string stationID = dr[3].ToString();
    int iDur = 0;
    int iStat = 0;
    string stest = "";
    string sColName = "";
    // roll through cols
    int iiStartCol = 4; // skip initial cols [_Key], [DT_Stamp], [Log_Trigger],[StationID] zero based
    for (int ii = iiStartCol; ii < colCount; ii++)
    {
        // even = Status  based on select in proc
        // odd = duration
        if (ii % 2 == 0)
            iStat = ((int)dr[ii]);
        else
            iDur = ((int)dr[ii]);

        if (iStat > 0) // test set breakpoint to view data
        {
            stest = iStat.ToString();
            sColName = dt.Columns[ii].ColumnName.ToString();
        }


    }

}


protected DataTable getData(DateTime Start, DateTime End)
{
    // simple ado.net
    SqlCommand cmd = null;
    SqlDataAdapter da = null;
    DataSet ds = new DataSet("Log");
    string connR = WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connR))
    {
        try
        {
            cmd = new SqlCommand("GetTimeseries", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.DateTime));
            cmd.Parameters["@Start"].Value = Start;
            cmd.Parameters.Add(new SqlParameter("@End", SqlDbType.DateTime));
            cmd.Parameters["@End"].Value = End;
            conn.Open();
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);

        }
        catch (Exception err)
        {
            throw new ApplicationException(err.Message.ToString());

        }
        finally
        {
            if (cmd != null) { cmd.Dispose(); }
            if (da != null) { da.Dispose(); }
        }
    }

    return ds.Tables[0];
}

}

** Database particulars for those who are interested. Again I am more interested in the general question of rolling through cols on a row in a positional way. **

[table def & proc, udf]

CREATE TABLE [dbo].[LS_LOG]( [_Key] [int] IDENTITY(1,1) NOT NULL, [DT_Stamp] [datetime] NULL, [Log_Trigger] varchar NULL, [StationID] [int] NULL, [Status_0] [int] NULL, [Duration_0] [int] NULL, [Status_1] [int] NULL, [Duration_1] [int] NULL, [Status_2] [int] NULL, [Duration_2] [int] NULL, [Status_3] [int] NULL, [Duration_3] [int] NULL, [Status_4] [int] NULL, [Duration_4] [int] NULL, [Status_5] [int] NULL, [Duration_5] [int] NULL ... ) ON [PRIMARY]

Procedure [dbo].[GetTimeseries] ( @Start Datetime , @End Datetime ) as SELECT [_Key] ,[DT_Stamp] ,[Log_Trigger] ,[StationID] ,[Status_0] ,[Duration_0] ,[Status_1] ,[Duration_1] ,[Status_2] ,[Duration_2] ,[Status_3] ,[Duration_3] ,[Status_4] ,[Duration_4] ,[Status_5] ,[Duration_5] ... FROM [TODD].[dbo].[LS_LOG] Where dbo.GetDateElement(DT_Stamp) >= dbo.GetDateElement(@Start) And dbo.GetDateElement(DT_Stamp) <= dbo.GetDateElement(@End) Order by DT_Stamp

FUNCTION [dbo].[GetDateElement]( @inDateTime as DATETIME ) RETURNS datetime AS
BEGIN RETURN (select CAST(FLOOR( CAST(@inDateTime AS FLOAT ) )AS DATETIME)) END

ansaurus

tags:

views:

answers:

Using old-school ado but can anyone improve this using L2S or dynamic sql

related questions