Click here to Skip to main content
12,457,187 members (56,832 online)
Click here to Skip to main content
Articles » Database » Database » General » Downloads

Stats

36.7K views
600 downloads
26 bookmarked
Posted

A Helpful Database Library

, 6 Dec 2006
This article illustrates a helpful database library.
MyDatabaseLibrary.dll
WinDB for CodeProject.exe
Interop.ADODB.dll
Interop.MSDASC.dll
WinDB for CodeProject
MyDatabaseLibrary
bin
Debug
Interop.ADODB.dll
Interop.MSDASC.dll
MyDatabaseLibrary.dll
Release
ClassDiagram1.cd
obj
Debug
Refactor
TempPE
Release
TempPE
Properties
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
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.

You may also be interested in...

Pro
Pro
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160826.1 | Last Updated 6 Dec 2006
Article Copyright 2006 by lost in transition
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid