Posts Tagged foreach

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

Advertisements

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

Leave a comment

WCF and Email Attachments

First off I want to apologize for being away so long. It has been a crazy ride to say the least.

So a colleague of mine ran into an issue with WCF and emailing attachments and asked for help. In this block of code below it would always fail at:

Attachment attachment = new Attachment(fileAttachment.ContentStream, fileAttachment.Name);”     The error is : “Value cannot be null.
Parameter name: stream”

 

 

 
EmailSendInput emailSendInput = new EmailSendInput
                {
                    Attachments = new List<Attachment>(),
                    Body = model.Body,
                    To = new List<string> { model.EmailTo },
                    From = model.EmailFrom,
                    Subject = model.EmailSubject
                };

                if (model.Files != null)
                {
                    foreach (var currAttachment in model.Files)
                    {
                        if (currAttachment != null && currAttachment.ContentLength > 0)
                        {
                            // the filename needs to be nice looking
                            string fileName =
                                currAttachment.FileName.Substring(
                                    currAttachment.FileName.LastIndexOf(@”\”, System.StringComparison.Ordinal) + 1,
                                    currAttachment.FileName.Length
                                    – currAttachment.FileName.LastIndexOf(@”\”, System.StringComparison.Ordinal) – 1);

                            var attachment = new Attachment(currAttachment.InputStream, fileName);
                            emailSendInput.Attachments.Add(attachment);
                        }
                    }
                }

                B2BMortgageDataServiceAgent b2BMortgageDataServiceAgent = new B2BMortgageDataServiceAgent();
                EmailSendResponse emailSendResponse = b2BMortgageDataServiceAgent.SendEmail(emailSendInput);
                if (!emailSendResponse.Success)
                {
                    throw new Exception(emailSendResponse.Message);
                }
            }

public EmailSendResponse SendEmail(EmailSendInput input)
        {
            EmailSendResponse retVal = new EmailSendResponse();
            try
            {
               
                string smtpServer = “”
                string smtpPort = “”

                using (var client = new SmtpClient(smtpServer, Convert.ToInt32(smtpPort)))
                {
                    var mail = new MailMessage
                    {
                        From = new MailAddress(input.From),
                        Subject = input.Subject,
                        Body = input.Body
                    };
                    input.To.ForEach(t => mail.To.Add(t));

                    if (input.Attachments != null && input.Attachments.Count > 0)
                    {
                        foreach (var fileAttachment in input.Attachments)
                        {
                            //Code Crashes here
                            Attachment attachment = new Attachment(fileAttachment.ContentStream, fileAttachment.Name);
                            mail.Attachments.Add(attachment);
                        }
                    }

                    client.Send(mail);
                }

                retVal.Success = true;
            }
            catch (Exception exception)
            {
                retVal.Success = false;
                LogUtil.LogException(exception);

            
                retVal.Message = exception.ToString();
            }

            return retVal;
        }

and then the contract:

[DataContract]
    public class EmailSendInput
    {
        [DataMember]
        public string From { get; set; }

        [DataMember]
        public List<string> To { get; set; }

        [DataMember]
        public string Subject { get; set; }

        [DataMember]
        public string Body { get; set; }

        [DataMember]
        public List<Attachment> Attachments { get; set; }

    }

Implemented as:

public EmailSendResponse SendEmail(EmailSendInput input)
         {
             try
             {
                 return this.dataAgent.SendEmail(input);
             }
             catch (Exception e)
             {
                 this.logger.LogException(e, 2);
                 throw new FaultException(“An error occured in SendEmail. Error details : ” + this.BuildMessage(e));
             } 
         }

and the Email response:

[DataContract]
    public class EmailSendResponse
    {
        [DataMember]
        public bool Success { get; set; }

        [DataMember]
        public string Message { get; set; }
    }

So now we have the problem. I basically had to recreate his datamember for attachment in the contract EmailSendInput to a

public List<EmailEncodedAttachment> Attachments { get; set; }

and added a new contract called EmailEncodedAttachment and process how he was handling the attachment differently. I’ve also included the MVC controller calls in case it might help you as well.

 

SOLUTION:

[DataContract]
    public class EmailSendInput
    {
        [DataMember]
        public string From { get; set; }

        [DataMember]
        public List<string> To { get; set; }

        [DataMember]
        public string Subject { get; set; }

        [DataMember]
        public string Body { get; set; }

        [DataMember]
        public List<EmailEncodedAttachment> Attachments { get; set; }

    }

 

[DataContract]
    public class EmailEncodedAttachment
    {
        [DataMember]
        public string Base64Attachment;

        [DataMember]
        public string Name;

        /// <summary>
        /// One of the System.Net.Mime.MediaTypeNames
        /// </summary>
        [DataMember]
        public string MediaType;
    }
}
                retVal.Message = exception.ToString();
            }

            return retVal;
        }

 

public EmailSendResponse SendEmail(EmailSendInput input)
        {
            EmailSendResponse retVal = new EmailSendResponse();
            try
            {
               
                string smtpServer = ConfigurationManager.AppSettings[“SmtpServer”] ?? “url”;
                string smtpPort = ConfigurationManager.AppSettings[“SmtpPort”] ?? “portnum”;

                using (var client = new SmtpClient(smtpServer, Convert.ToInt32(smtpPort)))
                {
                    var mail = new MailMessage
                    {
                        From = new MailAddress(input.From),
                        Subject = input.Subject,
                        Body = input.Body
                    };
                    input.To.ForEach(t => mail.To.Add(t));

                    if (input.Attachments != null && input.Attachments.Count > 0)
                    {
                        foreach (var fileAttachment in input.Attachments)
                        {
                            mail.Attachments.Add(this.CreateAttachment(fileAttachment));
                        }
                    }

                    client.Send(mail);
                }

                retVal.Success = true;
            }
            catch (Exception exception)
            {
                retVal.Success = false;
                LogUtil.LogException(exception);
             
                retVal.Message = exception.ToString();
            }

            return retVal;
        }

 

The MVC controller action calls the service like so:

EmailSendInput emailSendInput = new EmailSendInput
                {
                    Attachments = new List<EmailEncodedAttachment>(),
                    Body = model.Body,
                    To = new List<string> { model.EmailTo },
                    From = model.EmailFrom,
                    Subject = model.EmailSubject
                };

                if (model.Files != null)
                {
                    foreach (var file in model.Files)
                    {
                        if (file != null && file.ContentLength > 0)
                        {
                            // the filename needs to be nice looking
                            string prettyFileName =
                                file.FileName.Substring(
                                    file.FileName.LastIndexOf(@”\”, System.StringComparison.Ordinal) + 1,
                                    file.FileName.Length
                                    – file.FileName.LastIndexOf(@”\”, System.StringComparison.Ordinal) – 1);

                            var attachment = this.CreateAttachment(prettyFileName, file.InputStream);
                            emailSendInput.Attachments.Add(attachment);
                        }
                    }
                }

                B2BMortgageDataServiceAgent b2BMortgageDataServiceAgent = new B2BMortgageDataServiceAgent();
                EmailSendResponse emailSendResponse = b2BMortgageDataServiceAgent.SendEmail(emailSendInput);
                if (!emailSendResponse.Success)
                {
                    throw new Exception(emailSendResponse.Message);
                }

 

and finally the encoding method which makes this possible:

private EmailEncodedAttachment CreateAttachment(string fileName, Stream stream)
        {
            EmailEncodedAttachment att = new EmailEncodedAttachment
            {
                Name = fileName,
                MediaType = System.Net.Mime.MediaTypeNames.Text.Plain
            };

            byte[] buffer = new byte[stream.Length];
            stream.Read(buffer, 0, (int)stream.Length);
            att.Base64Attachment = Convert.ToBase64String(buffer);

            return att;
        }

 

There you have it folks. I hope you have a great Sunday!

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

Leave a comment