Posts Tagged parameters

Class to Simplify and Minimize Code for Stored Procedures and TSQL

First of all if you are using TSQL in your code you really need to get in a another line of work. It isn’t scalable, difficult to maintain and is generally a bad practice. But I know some of you still do it so I did include it. This class will allow you to in a few lines of code do your select, update, insert or delete statements in a very few lines of code. All that is required of the developer is to list the values in order (if you are using stored procedures) that are required. Also included is how to declare a new instance of a sqlparametercollection – which you aren’t suppose to be able to do. I ended up not needing it but included it just in case someone needs to do that someday. Also if you are using a strongly typed dataset there is a method included of how to do that. After the code is example of usage. Have a great day!

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;

using System.IO;

using System.Data.SqlClient;

public class clsData
{

    public SqlConnection sqlconn = new SqlConnection();
    public string Error_Message;
    public List<SqlParameter> ParamList = new List<SqlParameter>();
    public ArrayList ParamValues = new ArrayList();

    public int SQLExecuteNonQueryValue;
    public clsData()
   

    public enum SQLAction
    {
        SelectAction,
        UpdateInsertDeleteAction
    }

    public virtual DataTable DataAction(clsData.SQLAction Action, DataTable dt, string cmdtext, string ParamValues = “”)
    {
        SQLExecuteNonQueryValue = -1;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = cmdtext;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = sqlconn;
        this.GetParameterList(cmdtext);
        if (!string.IsNullOrEmpty(Error_Message)) {
            return null;
        }
        if (!string.IsNullOrEmpty(ParamValues)) {
            this.ParseSQLParameterValues(ParamValues);
            for (i = 0; i <= this.ParamList.Count – 1; i++) {
                cmd.Parameters.AddWithValue(this.ParamList[i].ToString(), this.ParamValues[i].ToString());
            }
        }
        //Debug.WriteLine(cmd.CommandText)
        try {
            cmd.Connection.Open();
            switch (Action) {
                case SQLAction.SelectAction:
                    dt.Load(cmd.ExecuteReader());
                    break;
                case SQLAction.UpdateInsertDeleteAction:
                    //Check this value to make sure everything went ok
                    SQLExecuteNonQueryValue = cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    return null;
            }
            cmd.Connection.Close();

        } catch (Exception ex) {
            Error_Message = “There was a problem with ” + cmdtext + “. “;
            return null;
        }

        //removing duplicate table from dataset
        for (i = 0; i <= ds_Copy.Tables.Count – 1; i++) {
            if (ds_Copy.Tables(i).TableName == dt.TableName) {
                ds_Copy.Tables(i).Clear();
                ds_Copy.Tables(i).Columns.Clear();
            }
        }

        //placing datatable in the dataset
        ds_Copy.Merge(dt, false, MissingSchemaAction.Add);
        Error_Message = “”;
        return dt;
    }

    public DataTable DataActionTSQL(clsData.SQLAction Action, DataTable dt, string sqlstring)
    {
        SQLExecuteNonQueryValue = -1;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = sqlstring;
        cmd.Connection = sqlconn;
        try {
            cmd.Connection.Open();
            switch (Action) {
                case SQLAction.SelectAction:
                    dt.Load(cmd.ExecuteReader());
                    break;
                case SQLAction.UpdateInsertDeleteAction:
                    //Check this value to make sure everything went ok
                    SQLExecuteNonQueryValue = cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    return null;
            }
            cmd.Connection.Close();

        } catch (Exception ex) {
            Error_Message = “There was a problem with ” + sqlstring + “. “;
            return null;
        }
        //removing duplicate table from dataset
        for (i = 0; i <= ds_Copy.Tables.Count – 1; i++) {
            if (ds_Copy.Tables(i).TableName == dt.TableName) {
                ds_Copy.Tables(i).Clear();
                ds_Copy.Tables(i).Columns.Clear();
            }
        }
        //placing datatable in the dataset
        ds_Copy.Merge(dt, false, MissingSchemaAction.Add);
        Error_Message = “”;
        return dt;
    }

    private List<SqlParameter> GetParameterList(string ProcName)
    {
        //Get the parameters for the selected stored procedure
        List<SqlParameter> inputParamList = new List<SqlParameter>();
        ParamList.Clear();

        using (SqlConnection cn = new SqlConnection()) {
            DispatcherTool.My.MySettings Settings = new DispatcherTool.My.MySettings();
            cn.ConnectionString = Settings.SRAConnectionString;
            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = cn;
            myCommand.CommandText = ProcName;
            myCommand.CommandType = System.Data.CommandType.StoredProcedure;
            try {
                cn.Open();
                SqlCommandBuilder.DeriveParameters(myCommand);
                cn.Close();
            } catch (Exception ex) {
                Error_Message = “There was a problem with the connection to the database.”;
                return null;
            }

            //Dim sqlparams As SqlParameterCollection = DirectCast(GetType(SqlParameterCollection).GetConstructor(BindingFlags.NonPublic Or BindingFlags.Instance, Nothing, Type.EmptyTypes, Nothing).Invoke(Nothing), SqlParameterCollection)

            foreach (SqlParameter param in myCommand.Parameters) {
                if (param.Direction == System.Data.ParameterDirection.Input || param.Direction == System.Data.ParameterDirection.InputOutput) {
                    //sqlparams.Add(param.ParameterName & ” – ” & param.SqlDbType.ToString())
                    //Debug.WriteLine(param.ParameterName & ” – ” & param.SqlDbType.ToString())
                    inputParamList.Add(param);
                    //Else
                    // sqlparams.Add(param.ParameterName & ” – ” & param.SqlDbType)
                    //Debug.WriteLine(param.ParameterName & ” -2 ” & param.SqlDbType)
                }
            }
        }
        this.ParamList = inputParamList;
        Error_Message = “”;
        return this.ParamList;
    }

    public void ParseSQLParameterValues(string ValueString)
    {
        ParamValues.Clear();
        string[] parts = ValueString.Split(new char[] { ‘,’ });
        string part = null;
        foreach (string part_loopVariable in parts) {
            part = part_loopVariable;
            ParamValues.Add(part);
        }
    }

    //’Public Shared Function ConvertToTypedDataTable(Of T As {Data.DataTable, New})(ByVal dtBase As Data.DataTable) As T
    //’    Dim dtTyped As New T
    //’    dtTyped.Merge(dtBase)
    //’    Return dtTyped
    //’End Function

}

And then it’s use…..

private void Button1_Click(System.Object sender, System.EventArgs e)
{
    Strongly_Typed_DataSet.usp_Your_Stored_Procedure dt = new Strongly_Typed_DataSet.usp_Your_Stored_Procedure();

    DataGridView1.DataSource = cn.DataAction(clsData.SQLAction.SelectAction, dt, ds.usp_Get_PrinterName_for_PickList_Printing.ToString, “Tubing”);

    //DataGridView1.DataSource = cn.DataActionTSQL(clsData.SQLAction.SelectAction, dt, “SELECT *FROM Your_Table”)

}


Join me on Facebook

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Leave a comment

Write DataTable to an MS Word Table Efficiently with C# using a Dynamic Type

This is a C# adaptation of the code I wrote to write a datatable to a Microsoft Word document table for vb.net . But that doesn’t really begin to tell the story here. In vb.net we have been accustomed to being allowed to leave parameters empty when automating the creation of a table in Microsoft Word. C# has not permitted me that luxury which to be honest is probably a better code practice. So prepare to meet the Type.Missing object! In addition the default item that we learned to love/hate in Visual Basic for Applications (VBA) code years ago also is not used in C#. Finally in declaring the range object for the table it became an opportunity to use the new dynamic reference type keyword, which was designed for such situations. Check out this video on the subject which is quite excellent.

http://channel9.msdn.com/Shows/Going+Deep/Inside-C-40-dynamic-type-optional-parameters-more-COM-friendly/player?w=512&h=288

For these reasons you will see key differences between the two sets of code. Don’t forget to import Microsoft Word as a COM reference and do your import statements. As always feel free to comment or email. Have a great day!

using Office = Microsoft.Office.Core;
using Word = Microsoft.Office.Interop.Word;

public void CreateWordTableWithDataTable(DataTable dt)
        {
            int RowCount = dt.Rows.Count; int ColumnCount = dt.Columns.Count;
            Object[,] DataArray = new object[RowCount + 1, ColumnCount + 1];
            //int RowCount = 0; int ColumnCount = 0;
            int r = 0;
            for (int c = 0; c <= ColumnCount – 1; c++)
            {
                DataArray[r, c] = dt.Columns[c].ColumnName;
                for (r = 0; r <= RowCount – 1; r++)
                {
                    DataArray[r, c] = dt.Rows[r][c];
                } //end row loop
            } //end column loop

            Word.Document oDoc = new Word.Document();
            oDoc.Application.Visible = true;
            oDoc.PageSetup.Orientation = Word.WdOrientation.wdOrientLandscape;
           
            dynamic oRange = oDoc.Content.Application.Selection.Range;
            String oTemp = “”;
            for (r = 0; r <= RowCount – 1; r++)
            {
                for (int c = 0; c <= ColumnCount – 1; c++)
                {
                    oTemp = oTemp + DataArray[r, c] + “\t”;
                  
                }
            }

oRange.Text = oTemp;
           
object Separator = Word.WdTableFieldSeparator.wdSeparateByTabs;
object Format = Word.WdTableFormat.wdTableFormatWeb1;
object ApplyBorders = true;
object AutoFit = true;

object AutoFitBehavior = Word.WdAutoFitBehavior.wdAutoFitContent;
            oRange.ConvertToTable(ref Separator,
        ref RowCount, ref ColumnCount, Type.Missing, ref Format,
        ref ApplyBorders, Type.Missing, Type.Missing, Type.Missing,
         Type.Missing, Type.Missing, Type.Missing,
         Type.Missing, ref AutoFit, ref AutoFitBehavior,
         Type.Missing);
           
            oRange.Select();
            oDoc.Application.Selection.Tables[1].Select();
            oDoc.Application.Selection.Tables[1].Rows.AllowBreakAcrossPages = 0;
            oDoc.Application.Selection.Tables[1].Rows.Alignment = 0;
            oDoc.Application.Selection.Tables[1].Rows[1].Select();
            oDoc.Application.Selection.InsertRowsAbove(1);
            oDoc.Application.Selection.Tables[1].Rows[1].Select();

            //gotta do the header row manually
            for (int c = 0; c <= ColumnCount – 1; c++)
            {
               oDoc.Application.Selection.Tables[1].Cell(1, c + 1).Range.Text = dt.Columns[c].ColumnName;
            }

            oDoc.Application.Selection.Tables[1].Rows[1].Select();
            oDoc.Application.Selection.Cells.VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                      
                    }

Facebook

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Leave a comment