Data Access Layer

 I work on a team. We have standards I have put in place.  One aspect of those standards includes the data access layer (DAL).   What this does for someone is separate the database connection from the rest of the code. Here is how we set it up and use it. First we have the web.config. In there we have our connection string:

<add connectionString="Data Source=55.55.55.55;
Initial Catalog=Databasename;User; Pwd=password;
Connect Timeout=1000"/>

From there we want to create the class file to hold all our database calls. I have a file called DAL.cs in my App_Code folder. Inside the class file I have the connection string acquisition:

#region Connection string aquistition
    public class Database
    {
        static public String ConnectionString
        {
            get
            {    // get connection string with name database from  web.config
                return ConfigurationManager.ConnectionStrings["dsn"].ConnectionString;
            }
        }
    }
#endregion

On a side note you can see the use of #region above.  That makes the code more vision friendly in my opinion. Instead of having all lines of code showing on the whole page where you have to scroll all over the place, you can wrap each code segment with the #region and minimize the segment. Works very cool. Looks good also.

Now, back to what we were talking about. We created our connection string acquisition and now we need to put it to use:

#region Dynamic StoredProc
    public DataTable get_sProc(string qryStr)
    {
        try
        {
            //create command Object
            SqlCommand sqlCommand = new SqlCommand("dbo.sProc_MyQuery");
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@MyQuery";
            param.Value = qryStr;
            sqlCommand.Parameters.Add(param);
            // create Sql Connection (using above ConnectionString class)
            using (SqlConnection myConnection = new SqlConnection(Database.ConnectionString))
            {
                sqlCommand.Connection = myConnection;
                SqlDataAdapter da_regions = new SqlDataAdapter(sqlCommand);
                //Instantiate and populate the DataTable
                DataTable DataTable = new DataTable();
                da_regions.Fill(DataTable);
                return DataTable;
            }
        }
 // something went wrong
        catch (Exception ex)
        {
            return null;
        }
    }
#endregion

Notice the use of #region. Anyway, that’s how you would call it. Now if you need to change the database server you can with ease. If you need to change the connection string, you can with ease without effecting the application. I am all for standards. They make the work place a better place to work. Next I will go into how to consume the class to display the queried information. Hope this helped someone.  Have fun coding and as always, if there are any questions or suggestions, they are welcome.  Thank you.

Email using C#

I had a co-worker recently ask me what I did to send email from a C# web application. I gave him what worked for me. He made a few adjustments and sent it back to me.  I did request that he send me what he came up with that finally worked for him. I think he did a better job than I so I am going to post it for you to see. Have a look and see how it may fit into your application or project. Improvements are welcome. He ended up writing a class to call from anywhere in the project. It takes the following arguments; from, to, subject, body, attachments, and isBodyHTML boolean.

using System.Net;
using System.Net.Mail;

public class mailer
{
public Boolean sendemail(String strFrom, string strTo, string strSubject, string strBody, string strAttachmentPath, bool IsBodyHTML)
{
Array arrToArray;
char[] splitter = { ';' };
arrToArray = strTo.Split(splitter);
MailMessage mm = new MailMessage();
mm.From = new MailAddress(strFrom);
mm.Subject = strSubject;
mm.Body = strBody+disclaimer;
mm.IsBodyHtml = IsBodyHTML;
//mm.ReplyTo = new MailAddress("replyto@xyz.com");
foreach (string s in arrToArray)
{
mm.To.Add(new MailAddress(s));
}
if (strAttachmentPath != "")
{
try
{
//Add Attachment
Attachment attachFile = new Attachment(strAttachmentPath);
mm.Attachments.Add(attachFile);
}
catch { }
}
SmtpClient smtp = new SmtpClient();
try
{
smtp.Host = "mail.domain.com";
smtp.Port = 25; //Specify your port No;
smtp.Send(mm);
return true;
}
catch
{
mm.Dispose();
smtp = null;
return false;
}
}
}

Thanks Shane!

Hope this helped someone.  Have fun coding and as always, if there are any questions or suggestions, they are welcome.  Thank you.

Working With Dates and Times Using ASP.NET C#

I know we have all have to use dates at some time or another.  I came across this posting and thought I would list it here for you.  Hope it helps.

DateTime today = DateTime.Today; // just the date

DateTime tomorrow = today.AddDays(1);

DateTime yesterday = today.AddDays(-1);

TimeSpan time = tomorrow – today;

int days = time.Days;

int hours = time.Hours;

int minutes = time.Minutes;

int seconds = time.Seconds;

int milliseconds = time.Milliseconds;

time += new TimeSpan(days, hours, minutes, seconds,

milliseconds);

As always, if there are any questions or suggestions, they are welcome. Thank you.

Performing Loops – VB and C#

This will be a short post. I just wanted to display the differences between C# and VB when performing loops.  See below:

VB:

For i As Integer = 10 To 1 Step -1

Console.WriteLine(i)

If i < 5 Then

Exit For

End If

Next

 

Dim start As Integer = 5

For i As Integer = start To start + 10

Console.WriteLine(i)

Next

 

Dim str As String

Do

str = Console.ReadLine()

Console.WriteLine(str)

Loop Until str = “”

 

Do

str = Console.ReadLine().Trim()

If String.IsNullOrEmpty(str) Then

Exit Do

End If

Console.WriteLine(“You typed:{0}”, str)

Loop

Dim names As String() = New String() {“Bob”, “David”}

For Each name As String In names

Console.WriteLine(name)

Next

 

C#: 

for (int i=0; i<10; i++) {}

 

while (i<10) { i++; }

 

do { i++; } while (i<10);

 

foreach (ListItem item in list.Items) {}

 

Working With a Database in C#

I do not use C# much. Here is some code I found while browsing the Internet that helps to connect to a database:

using System.Data;
using System.Data.SqlClient;
string cs = @"Data Source=.\SQLEXPRESS;" +
string cs = @"Initial Catalog=NamesDB;" +
string cs = @"Integrated Security=True;";
using (SqlConnection con = new SqlConnection(cs)) {
con.Open();
string sql = "INSERT INTO Names(Name) VALUES(@Name)";
// insert a record
SqlCommand cmd1 = new SqlCommand(sql, con);
cmd1.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
cmd1.Parameters["@Name"].Value = "Bob";
cmd1.ExecuteNonQuery();
// insert a second record
cmd1.Parameters["@Name"].Value = "David";
cmd1.ExecuteNonQuery();
// read records
sql = "SELECT * FROM Names";
SqlCommand cmd2 = new SqlCommand(sql, con);
using (SqlDataReader r = cmd2.ExecuteReader()) {
int iName = r.GetOrdinal("Name");
while (r.Read()) {
Console.WriteLine(
r.IsDBNull(iName)?"Null":r.GetString(iName)
);
}
}
// read a single value
sql = "SELECT TOP 1 Name FROM Names";
SqlCommand cmd3 = new SqlCommand(sql, con);
Console.WriteLine(cmd3.ExecuteScalar());
}

Good luck and Happy Coding!

Working With Number in C#

int i = 0;

// convert from a string

int i = int.Parse(“1″);

// convert froma string and don’t throw exceptions

if (int.TryParse(“1″, out i)) {}

i++; // increment by one

i–; // decrement by one

i += 10; // add 10

i -= 10; // subtract 10

i *= 10; // multiply by 10

i /= 10; // divide by 10

i = checked(i*2) // check for over flow

i = unchecked(i*2) // ignore over flow