sql.cs

Gibphone is CSharp Program, it can tell you how to design p2p chat.

using System;
using System.Collections.Generic;
using System.Text;
using FirebirdSql.Data.FirebirdClient;
using FirebirdSql.Data.Isql;
using System.IO;
using System.Reflection;
using GPCore.Protocols;
using GPCore.Vocoders;
using GPCore.NetworkProtocols;
using GPCore.Phones;

namespace GPCore
{
    /// <summary>
    /// A Static Class to help with SQL Functions
    /// </summary>
    public static class Sql
    {
        private static FbConnectionStringBuilder sb;

        /// <summary>
        /// Gets/Sets the ConnectionString to change the Database to use.
        /// </summary>
        public static string ConnectionString
        {
            get
            {
                return sb.ToString();
            }
            set
            {
                sb = new FbConnectionStringBuilder(value);
            }
        }
        static Sql()
        {
            sb = new FbConnectionStringBuilder();
            sb.ServerType = FbServerType.Embedded;
            sb.Database = Path.Combine(Directory.GetCurrentDirectory(), "Gibphone.fdb");
            sb.Dialect = 3;
            sb.UserID = "Sysdba";
            sb.Password = "masterkey";
        }
        private static List<FbConnection> openconnections = new List<FbConnection>();
        /// <summary>
        /// Opens a new Connection to the SQLDatabase
        /// remember to close it after you are done with it.
        /// </summary>
        public static FbConnection  SqlDatabase
        {
            get
            {
                FbConnection s = new FbConnection(ConnectionString);
                
                s.Open();
                if (openconnections.Count > 60)
                {
                   
                    openconnections.RemoveAll(delegate(FbConnection c)
                    {
                        return (c.State == System.Data.ConnectionState.Closed);
                    });
                }
                openconnections.Add(s);
                return s;
            }
        }

        internal static void Start()
        {
            
            if (!File.Exists(sb.Database))
            {
                Core.UpdateSplash("Creating SQL Database for first time run");
                FbConnection.CreateDatabase(sb.ToString());
                StreamReader sr = new StreamReader(Assembly.GetCallingAssembly().GetManifestResourceStream("GPCore.CreateDatabase.sql"));
                FbScript script = new FbScript(sr);
                script.Parse();
                using (FbConnection conn = new FbConnection(ConnectionString))
                {
                    try
                    {
                        conn.Open();
                        FbBatchExecution batch = new FbBatchExecution(conn, script);
                        batch.Execute(true);
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(Logger.Log(e));
                        
                    }
                }
                Core.ShowAccountForm();
                Core.ShowPluginForm();
            }
            else
            {
                ExecuteNonQuery("update accounts set status = 'Offline'");
                bool hasRows = false;
                ExecuteReader(new ActOnQuery(delegate(GPDataReader read)
                {
                    while (read.Read())
                    {
                        hasRows = true;

                        Protocol p = Core.CreateInstance(read.GetString(0).Trim(), read.GetString(1).Trim(), read.GetString(2).Trim(), read.GetInt32(4) == 1, read.GetValue(3) as object[]);
                        Core.UpdateSplash(string.Format("Signing into {0} on {1}", p.Username, p.ToString()));
                        if (read.GetInt32(4) == 1 && p is IAuthenticate)
                        {
                            (p as IAuthenticate).SignOn();
                        }
                        if (read.GetInt32(5) == 1)
                        {
                            IVocoder vox = Core.CreateInstance(read.GetString(6)) as IVocoder;
                            INetworkProtocol nox = Core.CreateInstance(read.GetString(7)) as INetworkProtocol;
                            (p as IPhone).Initialize(vox, nox);
                        }
                    }
                }), "select ProtocolType,Username,Pass,OtherInformation,Autoload,Phone,Vocoder,Network from protocols");
                if (!hasRows)
                    Core.ShowAccountForm();
                hasRows = false;
                ExecuteReader(new ActOnQuery(delegate(GPDataReader read)
                {
                    while (read.Read())
                    {
                        hasRows = true;
                        Core.UpdateSplash("Starting Plugin: " + read.GetString(0).Trim());
                        Core.CreateInstance(read.GetString(0).Trim());
                        
                    }
                }), "select TypeName from Plugins");
                if (!hasRows)
                    Core.ShowPluginForm();
            }
        }

        /// <summary>
        /// Returns the ProtocolID of the given IProtocol.
        /// </summary>
        /// <param name="p">The IProtocol's whose ID you are interested in.</param>
        /// <returns>The ID of p</returns>
        public static int GetID(IProtocol p)
        {
            object o = ExecuteScalar("select ProtocolID from Protocols where Username = ? and ProtocolType = ?", p.Username, p.GetType().FullName);
            return (o == null ? -1 : (int)o);
        }

        /// <summary>
        /// Executes a SQL command that does not Return any Rows.
        /// </summary>
        /// <example>
        /// To Insert an element into a table.
        /// <code>
        /// public void Func()
        /// {
        ///     Sql.ExecuteNonQuery("Insert into Plugins (Typename) values ('TestPlugin.TestPlugin')");
        /// }
        /// </code>
        /// To Use the arguments add ?'s to the string one and put the args in
        /// the exact same order, you cannot reuse arguments more than once in a string.
        /// <code>
        /// public void Func(String s)
        /// {
        ///     Sql.ExecuteNonQuery("Insert into Plugins(Typename) values(?)",s);
        /// }
        /// </code>
        /// </example>
        /// <param name="Command">The Basic Command Text.</param>
        /// <param name="args">Arguments to add to replace the '?'s in the Command Text.</param>
        public static void ExecuteNonQuery(string Command, params object[] args)
        {

            using (FbConnection con = SqlDatabase)
            {
                FbCommand com = con.CreateCommand();

                com.CommandText = Command;
                foreach (object o in args)
                {
                    //com.Parameters.Add(o);
                    com.Parameters.AddWithValue("", o);
                }
                try
                {
                    com.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine(Logger.Log(e));
                }
            }
        }
        /// <summary>
        /// Executes a SQL command and performs Action on the DataReader
        /// Returned.
        /// </summary>
        /// <example>
        /// To Get information about Protocols
        /// <code>
        /// public void Func()
        /// {
        ///     Sql.ExecuteReader(new ActOnQuery(delegate (FbDataReader read)
        ///     {
        ///         while(read.Read())
        ///         {
        ///             Console.WriteLine("{0}: {1}",read.GetString(0),reader.GetString(1));
        ///         }
        ///     }),"select Username, ProtocolType from Protocols");
        /// }
        /// </code>
        /// To Use the arguments add ?'s to the string one and put the args in
        /// the exact same order, you cannot reuse arguments more than once in a string.
        /// <code>
        /// public void Func(int i)
        /// {
        ///     Sql.ExecuteReader(new ActOnQuery(delegate (FbDataReader read)
        ///     {
        ///         while(read.Read())
        ///         {
        ///             Console.WriteLine("{0}: {1}",read.GetString(0),reader.GetString(1));
        ///         }
        ///     }),"select Username, ProtocolType from Accounts where GibID = ?",i);
        /// }
        /// </code>
        /// </example>
        /// <param name="Action">A delegate that will process the DataRader created by this command to iterate through rows</param>
        /// <param name="Command">The Basic Command Text.</param>
        /// <param name="args">Arguments to add to replace the '?'s in the Command Text.</param>
        public static void ExecuteReader(ActOnQuery Action, string Command, params object[] args)
        {
            using (FbConnection con = SqlDatabase)
            {
                FbCommand com = con.CreateCommand();
                com.CommandText = Command;
                foreach (object o in args)
                {
                    com.Parameters.AddWithValue("",o);
                }
                try
                {
                    GPDataReader read = new GPDataReader(com.ExecuteReader());
                    Action(read);
                    read.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine(Logger.Log(e));
                }
            }
        }
        /// <summary>
        /// A delegate used with <see cref="ExecuteReader"/> to process
        /// a SQL Query.
        /// </summary>
        /// <param name="reader">The DataReader that contains information about the Query.</param>
        public delegate void ActOnQuery(GPDataReader reader);
        /// <summary>
        /// Executes a SQL command that Returns the first column of the first 
        /// row of the SQL Query.
        /// </summary>
        /// <example>
        /// To Count elements
        /// <code>
        /// public void Func()
        /// {
        ///     object o = Sql.ExecuteScalar("select Count(ID) from gibs");
        ///     int i = (o == null ? 0 : (int)o);
        /// }
        /// </code>
        /// To Use the arguments add ?'s to the string one and put the args in
        /// the exact same order, you cannot reuse arguments more than once in a string.
        /// <code>
        /// public void Func(String s)
        /// {
        ///     object o = Sql.ExecuteScalar("select Groupkey from Gibs where Alias = ?",s);
        ///     String s = (o == null ? "" : (string)o);
        /// }
        /// </code>
        /// </example>
        /// <param name="Command">The Basic Command Text.</param>
        /// <param name="args">Arguments to add to replace the '?'s in the Command Text.</param>
        /// <returns>The First column of the first row of the search query.</returns>
        public static object ExecuteScalar(string Command, params object[] args)
        {
            object read = null;
            using (FbConnection con = SqlDatabase)
            {
                FbCommand com = con.CreateCommand();
                com.CommandText = Command;
                foreach (object o in args)
                {
                    // com.Parameters.Add(o);
                    com.Parameters.AddWithValue("", o);
                }
                try
                {
                    read = com.ExecuteScalar();
                }
                catch (Exception e)
                {
                    Console.WriteLine(Logger.Log(e));
                }
            }
            return read;
                
        }
        /// <summary>
        /// Runs a batch Sql script from the given file.
        /// </summary>
        /// <param name="file">The path of the Sql file.</param>
        public static void RunBatch(String file)
        {
            RunBatch(new StreamReader(file));
        }
        /// <summary>
        /// Runs a batch Sql script from the given stream.
        /// </summary>
        /// <param name="stream">The stream to read Script from.</param>
        public static void RunBatch(Stream stream)
        {
            RunBatch(new StreamReader(stream));
        }
        /// <summary>
        /// Runs a batch Sql script.
        /// </summary>
        /// <param name="reader"></param>
        public static void RunBatch(TextReader reader)
        {
            FbScript script = new FbScript(reader );
            using (FbConnection con = SqlDatabase)
            {
                try
                {
                    FbBatchExecution batch = new FbBatchExecution(con, script);
                }
                catch (Exception e)
                {
                    Console.WriteLine(Logger.Log(e));
                }
            }
        }

        /// <summary>
        /// Checks if the given table already exists in the SqlDatabase
        /// Useful if a plugin defines a table itself and so that it doesnt try to create the table twice and cause an error.
        /// </summary>
        /// <param name="s">The name of the table that you want to check exists.</param>
        /// <returns>True if the table is already define; otherwise false.</returns>
        public static bool TableExists(String s)
        {
            object o = Sql.ExecuteScalar("SELECT COUNT(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE (RDB$RELATION_NAME = ?) AND RDB$VIEW_SOURCE IS NULL", s.ToUpper());
            return (o == null ? false : (int)o > 0);
        }

    }
}