Using C# to interface with SQLite

If you need to interface with SQLite there are a couple of steps.

1) You need to get the .net provider for SQLite from sourceforge.net
2) Then add a reference to System.Data.SQLite to your project.
3) You need to make sure the reference is marked to be copied locally.

Here is a C# class to for doing select, insert, update, and delete.

public class SqlLiteHelper
    {
        String dbConnection;

        /// <summary>
        ///     Single Param Constructor for specifying the DB file.
        /// </summary>
        /// <param name="inputFile">The File containing the DB</param>
        public SqlLiteHelper(String inputFile)
        {
            dbConnection = String.Format("Data Source={0}", inputFile);
        }

        /// <summary>
        ///     Single Param Constructor for specifying advanced connection options.
        /// </summary>
        /// <param name="connectionOpts">A dictionary containing all desired options and their values</param>
        public SqlLiteHelper(Dictionary<String, String> connectionOpts)
        {
            String str = "";
            foreach (KeyValuePair<String, String> row in connectionOpts)
            {
                str += String.Format("{0}={1}; ", row.Key, row.Value);
            }
            str = str.Trim().Substring(0, str.Length - 1);
            dbConnection = str;
        }

        /// <summary>
        ///     Allows the programmer to run a query against the Database.
        /// </summary>
        /// <param name="sql">The SQL to run</param>
        /// <returns>A DataTable containing the result set.</returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();
                    using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        using (SQLiteDataReader reader = mycommand.ExecuteReader())
                        {
                            dt.Load(reader);
                            reader.Close();
                        }
                    }
                    cnn.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return dt;
        }

        /// <summary>
        ///     Allows the programmer to interact with the database for purposes other than a query.
        /// </summary>
        /// <param name="sql">The SQL to be run.</param>
        /// <returns>An Integer containing the number of rows updated.</returns>
        public int ExecuteNonQuery(string sql)
        {
            int rowsUpdated = 0;

            using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
            {
                cnn.Open();
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    mycommand.CommandText = sql;
                    rowsUpdated = mycommand.ExecuteNonQuery();
                }
                cnn.Close();
            }
            return rowsUpdated;
        }

        /// <summary>
        ///     Allows the programmer to retrieve single items from the DB.
        /// </summary>
        /// <param name="sql">The query to run.</param>
        /// <returns>A string.</returns>
        public string ExecuteScalar(string sql)
        {
            using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
            {
                cnn.Open();
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    mycommand.CommandText = sql;
                    object value = mycommand.ExecuteScalar();
                    cnn.Close();
                    if (value != null)
                    {
                        return value.ToString();
                    }
                }
            }
            return "";
        }

        /// <summary>
        ///     Allows the programmer to easily update rows in the DB.
        /// </summary>
        /// <param name="tableName">The table to update.</param>
        /// <param name="data">A dictionary containing Column names and their new values.</param>
        /// <param name="where">The where clause for the update statement.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete rows from the DB.
        /// </summary>
        /// <param name="tableName">The table from which to delete.</param>
        /// <param name="where">The where clause for the delete.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Delete(String tableName, String where)
        {
            Boolean returnCode = true;
            try
            {
                this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
            }
            catch (Exception ex)
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily insert into the DB
        /// </summary>
        /// <param name="tableName">The table into which we insert the data.</param>
        /// <param name="data">A dictionary containing the column names and data for the insert.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
            }
            catch (Exception ex)
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete all data from the DB.
        /// </summary>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearDB()
        {
            DataTable tables;
            try
            {
                tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
                foreach (DataRow table in tables.Rows)
                {
                    this.ClearTable(table["NAME"].ToString());
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        ///     Allows the user to easily clear all data from a specific table.
        /// </summary>
        /// <param name="table">The name of the table to clear.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearTable(String table)
        {
            try
            {

                this.ExecuteNonQuery(String.Format("delete from {0};", table));
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
[tweetmeme only_single=”false”]
Advertisements

Generic handle error method

Here is a generic handle error method I put in my catches.  It will send an email with the inner exception unless it’s null then you get the exception message.  The stack trace is included.

You should the subject from “Application Name” to whatever your application is.
Also you should check out a previous post about a generic send mail method since this method calls that one.

private static void HandleError(Exception ex)
        {
            String errorMessage = String.Empty;

            if (ex.InnerException != null)
            {
                errorMessage = ex.InnerException.ToString();
            }
            else
            {
                errorMessage = ex.Message.ToString();
            }

            var trace = new System.Diagnostics.StackTrace(ex);

            StringBuilder errorBodyString = new StringBuilder();

            errorBodyString = errorBodyString.Append("Exception:" + errorMessage);
            errorBodyString = errorBodyString.Append("<br />");
            errorBodyString = errorBodyString.Append(trace);

String Subject = "Application Name";
            String Body = errorMessage;

           // sendNotification(Subject, errorBodyString.ToString());
        
[tweetmeme only_single=”false”]

Generic Send mail method

Here is a generic send mail method.

You just need to pass in a subject and body.
In your web.config you need a key for the from (notification_from), To (notification_to) and mail server (mail_server).
You could easily alter it to move the to (or from) to input variables.

private static void sendNotification(String Subject, String Body)
        {
            MailMessage mailMessage = new MailMessage();
            SmtpClient smtpClient = new SmtpClient();
            string msg = string.Empty;

            String from = ConfigurationManager.AppSettings["notification_from"];
            String to = ConfigurationManager.AppSettings["notification_to"];
            String mailserver = ConfigurationManager.AppSettings["mail_server"];

            MailAddress fromAddress = new MailAddress(from);
            mailMessage.From = fromAddress;
            mailMessage.To.Add(to);
            mailMessage.Subject = Subject;
            mailMessage.IsBodyHtml = true;
            mailMessage.Body = Body;

            smtpClient.Host = mailserver;

            smtpClient.EnableSsl = false;
            smtpClient.UseDefaultCredentials = true;

            smtpClient.Send(mailMessage);
        }
[tweetmeme only_single=”false”]

Ternary operator

The .NET framework has a cool feature called the Ternary Operator.  It’s basically a shortcut for  an if/then/else statement.

Here’s the format:
condition ? first_expression : second_expression;

The condition must be something that evaluates to true or false.  The first expression is what get’s returned if the condition is true.  Obviously the second expression gets returned if the condition is false.

I make use of then when I’m pulling data from a source and loading an object (ie, handle null from the source).

So here we have a normal check for null.  If it is null then set the property to String.Empty otherwise set to the value.

if (FirstName == null)
{

emp.FirstName =

String.Empty;}
else
{
      emp.FirstName = FirstName;
}

Here is the same statement using the new format.

emp.FirstName  = (FirstName == null) ? String.Empty : emp.FirstName = FirstName;

Much more compact code.

Remember you need both the ? and :

Don’t forget those.

[tweetmeme only_single=”false”]

Method to strip out special characters from a string.

Special characters can cause all kind of trouble.
So here is a method that you pass in a string and all the special characters will be removed.

 

public static string StripSpecialCharacters(string stringToConvert)
        {
            stringToConvert = stringToConvert.Replace("~", string.Empty);
            stringToConvert = stringToConvert.Replace("#", string.Empty);
            stringToConvert = stringToConvert.Replace("%", string.Empty);
            stringToConvert = stringToConvert.Replace("&", string.Empty);
            stringToConvert = stringToConvert.Replace("*", string.Empty);
            stringToConvert = stringToConvert.Replace("{", string.Empty);
            stringToConvert = stringToConvert.Replace("}", string.Empty);
            stringToConvert = stringToConvert.Replace("\\", string.Empty);
            stringToConvert = stringToConvert.Replace(":", string.Empty);
            stringToConvert = stringToConvert.Replace("<", string.Empty);
            stringToConvert = stringToConvert.Replace(">", string.Empty);
            stringToConvert = stringToConvert.Replace("?", string.Empty);
            stringToConvert = stringToConvert.Replace("/", string.Empty);
            stringToConvert = stringToConvert.Replace("|", string.Empty);
            stringToConvert = stringToConvert.Replace("\"", string.Empty);
            return stringToConvert;
        }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

SharePoint filename special characters not allowed

I was creating files in memory and loading them into a doc library based on user input on a webpart.
Some smart user put special characters in and of course it blew up my web part.

So I had to research what characters are not allowed in file names.

Here they are:
~, #, %, & , *, {, }, \, :, <, >, ?, /, |, “

So I wrote a little function to remove the special characters.

public static string RemoveSpecialCharacters(string stringToConvert)
{
stringToConvert = stringToConvert.Replace(“~”, string.Empty);
stringToConvert = stringToConvert.Replace(“#”, string.Empty);
stringToConvert = stringToConvert.Replace(“%”, string.Empty);
stringToConvert = stringToConvert.Replace(“&”, string.Empty);
stringToConvert = stringToConvert.Replace(“*”, string.Empty);
stringToConvert = stringToConvert.Replace(“{“, string.Empty);
stringToConvert = stringToConvert.Replace(“}”, string.Empty);
stringToConvert = stringToConvert.Replace(“\\”, string.Empty);
stringToConvert = stringToConvert.Replace(“:”, string.Empty);
stringToConvert = stringToConvert.Replace(“<“, string.Empty);
stringToConvert = stringToConvert.Replace(“>”, string.Empty);
stringToConvert = stringToConvert.Replace(“?”, string.Empty);
stringToConvert = stringToConvert.Replace(“/”, string.Empty);
stringToConvert = stringToConvert.Replace(“|”, string.Empty);
stringToConvert = stringToConvert.Replace(“\””, string.Empty);
}

So any filename I was going to use got run through this function to clean it up.

 

[tweetmeme only_single=”false”]

How to add a row click event to a gridview

So I ran across the problem of I need a web page to do something if a user clicked a row in a grid.
It wasn’t going to work for the user to click a button, the whole row had to be clickable.

Adding a row data bound event solved the problem.
I also change the background color on mouse over (and mouse out).

When the user clicks the row they get redirected to a page (with a query string parm so I know what row they clicked on).

 

   1: protected void grid_RowDataBound(object sender, GridViewRowEventArgs e)

   2: {

   3:     if (e.Row.RowType == DataControlRowType.DataRow)

   4:     {

   5:         e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='#ceedfc'");

   6:         e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=''");

   7:         e.Row.Attributes.Add("style", "cursor:pointer;");

   8:         e.Row.Attributes.Add("onclick", "location='detail.aspx?id=" + e.Row.Cells[0].Text + "'");

   9:     }

  10: }

How to convert Timezones

Here is a code sample (in C#) that I found for converting datetime between time zones.  This only works in the 3.5 framework (via System.TimeZoneInfo).

DateTime oldTime = new DateTime(2007, 6, 23, 10, 0, 0);
TimeZoneInfo timeZone1 = TimeZoneInfo.FindSystemTimeZoneById(“Central Standard Time”);
TimeZoneInfo timeZone2 = TimeZoneInfo.FindSystemTimeZoneById(“Eastern Standard Time”);
DateTime newTime = TimeZoneInfo.ConvertTime(oldTime, timeZone1, timeZone2);

 

Here is the url where I found this info.
http://pietschsoft.com/post/2007/06/23/NET-35-How-to-Convert-from-one-TimeZone-to-another.aspx

Clear Controls on screen

Here is a method that will clear all the controls on your screen (as long as it matches one of the controls in the method).  It recursively calls itself until all the controls are clear.

Here is an example of how you call it:
ClearControlsOnScreen(this.Page);

  public void ClearControlsOnScreen(Control parent)
        {
            try
            {
                foreach (Control _ChildControl in parent.Controls)
                {
                    if ((_ChildControl.Controls.Count > 0))
                    {
                        ClearControlsOnScreen(_ChildControl);
                    }
                    else
                    {

                        TextBox textbox = _ChildControl as TextBox;
                        if (textbox != null)
                        {
                            textbox.Text = string.Empty;
                        }

                        DropDownList dropdownlist = _ChildControl as DropDownList;
                        if (dropdownlist != null)
                        {
                            dropdownlist.ClearSelection();
                        }

                        CheckBox checkBox = _ChildControl as CheckBox;
                        if (checkBox != null)
                        {
                            checkBox.Checked = false;
                        }

                        CheckBoxList checkBoxList = _ChildControl as CheckBoxList;
                        if (checkBoxList != null)
                        {
                            checkBoxList.ClearSelection();
                        }

                        GridView gridView = _ChildControl as GridView;
                        if (gridView != null)
                        {
                            gridView.DataSource = null;
                            gridView.DataBind();
                        }

                    }
                }

            }
            catch (Exception ex)
            {
                throw ;
            }
        }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Run app as a specific user

Running an application as a specific user is common practice, but sometimes it’s helps to debug your application if you can run it as a specific user.

This does not change security context which the application runs as.
This works if you have your own user/role tables that control application security.

So if you add a key to your config file (web.config for example) then anytime you need to get the current user just call this method.

If the strUsername key is set to “Default” it will run as the current user.
If you set it to some other username it will return that username.

public static string GetUsername()
        {
            try
            {
                if (System.Configuration.ConfigurationManager.AppSettings["strUsername"].ToString() == "Default")
                {
                    return HttpContext.Current.User.Identity.Name.Substring(HttpContext.Current.User.Identity.Name.IndexOf('\\') + 1).ToLower();
                }
                else
                {
                    return System.Configuration.ConfigurationManager.AppSettings["strUsername"].ToString();
                }
             }
            catch (Exception ex)
            {
                throw ;
            }
        }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }