Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version

A Helpful Database Library

, 6 Dec 2006
This article illustrates a helpful database library.
mydatabaselibrary_exe.zip
MyDatabaseLibrary.dll
WinDB for CodeProject.exe
Interop.ADODB.dll
Interop.MSDASC.dll
windb_for_codeproject_source.zip
WinDB for CodeProject
MyDatabaseLibrary
bin
Debug
Interop.ADODB.dll
Interop.MSDASC.dll
MyDatabaseLibrary.dll
Release
ClassDiagram1.cd
obj
Debug
Refactor
TempPE
Release
TempPE
Properties
Settings.settings
WinDB for CodeProject
bin
Debug
Interop.ADODB.dll
Interop.MSDASC.dll
MyDatabaseLibrary.dll
Release
WinDB for CodeProject.vshost.exe
obj
Debug
Refactor
TempPE
Release
TempPE
Properties
Settings.settings
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Threading;
using System.Text;
using System.Windows.Forms;
using Settings = LDC_10.Properties.Settings;

namespace LDC_10
{
    internal static class Database
    {
        internal static void RunSqlCommands(string _SQLString, string _ConnString, string _Provider)
        {
            try
            {
                object countLock = new object();
                Monitor.Enter(countLock);
                if (_Provider == "Microsoft.Jet.OLEDB.4.0")
                {
                    OleDbConnection OleConn = new OleDbConnection(_ConnString);
                    OleDbCommand OleComm = new OleDbCommand(_SQLString, OleConn);
                    OleComm.Connection.Open();
                    OleComm.ExecuteNonQuery();
                    OleComm.Connection.Close();
                }
                else
                {
                    SqlConnection SqlConn = new SqlConnection(_ConnString);
                    SqlCommand SqlComm = new SqlCommand(_SQLString, SqlConn);
                    SqlComm.Connection.Open();
                    SqlComm.ExecuteNonQuery();
                    SqlComm.Connection.Close();
                }
                Monitor.Exit(countLock);
            }
            catch (Exception eUpdateDB)
            {
                MyErrors.LogError(eUpdateDB.Message);
            }
        }

        internal static object SelectID(string _SQLString, string _ConnString, string _Provider)
        {
            try
            {
                object _id;
                DataTable Dtbl = new DataTable();
                if (_Provider == "Microsoft.Jet.OLEDB.4.0")
                {
                    OleDbDataAdapter OleAdpt = new OleDbDataAdapter(_SQLString, _ConnString);
                    OleAdpt.Fill(Dtbl);
                }
                else
                {
                    SqlDataAdapter SqlAdpt = new SqlDataAdapter(_SQLString, _ConnString);
                    SqlAdpt.Fill(Dtbl);
                }
                if (Dtbl.Rows.Count != 0)
                {
                    _id = Dtbl.Rows[0][0];
                }
                else
                {
                    _id = 0;
                }
                return _id;
            }
            catch (Exception eSelectID)
            {
                MyErrors.LogError(eSelectID.Message);
                return false;
            }
        }

        internal static void CheckTables(string _ConnString, string _Provider)
        {
            try
            {
                
                Settings MySettings = new Settings();
                DataTable Dtbl = null;
                bool hdr = false;
                bool ds = false;
                bool tm = false;
                bool un = false;
                bool rd = false;
                bool hr = false;
                bool lg = false;
                string a = DateTime.Now.Year.ToString() + "Header";
                string b = DateTime.Now.Year.ToString() + "DataSet";
                string c = DateTime.Now.Year.ToString() + "Time";
                string d = DateTime.Now.Year.ToString() + "Units";
                string e = DateTime.Now.Year.ToString() + "Readings";
                string f = DateTime.Now.Year.ToString() + "HourlyData";
                string g = "LDC10_LogData";
                string _row;
                if (_Provider == "Microsoft.Jet.OLEDB.4.0")
                {
                    OleDbConnection OleConn = new OleDbConnection(_ConnString);
                    OleConn.Open();
                    Dtbl = OleConn.GetSchema("Tables");
                    OleConn.Close();
                }
                else
                {
                    SqlConnection SqlConn = new SqlConnection(_ConnString);
                    SqlConn.Open();
                    Dtbl = SqlConn.GetSchema("Tables");
                    SqlConn.Close();
                }
                for (int i = 0; i < Dtbl.Rows.Count; i++)
                {
                    _row = Dtbl.Rows[i][2].ToString();
                    if (_row == a)
                    {
                        hdr = true;
                    }
                    else if (_row == b)
                    {
                        ds = true;
                    }
                    else if (_row == c)
                    {
                        tm = true;
                    }
                    else if (_row == d)
                    {
                        un = true;
                    }
                    else if (_row == e)
                    {
                        rd = true;
                    }
                    else if (_row == f)
                    {
                        hr = true;
                    }
                    else if (_row == g)
                    {
                        lg = true;
                    }
                }
                if (!hdr)
                {
                    RunSqlCommands(SQL.CreateHdrTable(MySettings.Provider), MySettings.ConnString, MySettings.Provider);
                }
                if (!ds)
                {
                    RunSqlCommands(SQL.CreateDSTable(MySettings.Provider), MySettings.ConnString, MySettings.Provider);
                }
                if (!tm)
                {
                    RunSqlCommands(SQL.CreateTmTable(MySettings.Provider), MySettings.ConnString, MySettings.Provider);
                }
                if (!un)
                {
                    RunSqlCommands(SQL.CreateUnTable(MySettings.Provider), MySettings.ConnString, MySettings.Provider);
                }
                if (!rd)
                {
                    RunSqlCommands(SQL.CreateRwTable(MySettings.Provider), MySettings.ConnString, MySettings.Provider);
                }
                if (!hr)
                {
                    RunSqlCommands(SQL.CreateHourlyTable(MySettings.Provider), MySettings.ConnString, MySettings.Provider);
                }
                if (!lg)
                {
                    RunSqlCommands(SQL.CreateLogTable(MySettings.Provider), MySettings.ConnString, MySettings.Provider);
                }
            }
            catch (Exception eCheckTables)
            {
                MyErrors.LogError(eCheckTables.Message);
            }
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

lost in transition
Web Developer
United States United States
I am a software, database, and gis developer. I love the challenge of learning new ways to code.

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 6 Dec 2006
Article Copyright 2006 by lost in transition
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid