dbUtil

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace WindowsFormsApplication2
{
    public static class util
    {
        private static Regex r = new Regex(@"[^A-Za-z0-9\.\s]+", RegexOptions.Compiled);
        public static string RemoveSpecialCharacters(string s)
        {
            return r.Replace(s, "");
        }
        public static partial class sql
        {
            public static object sqlToScalar(string sqlCommand, params SqlParameter[] parameters)
            {
                object retVal;
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sys"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlCommand, cn))
                    {
                        if (parameters != null) cmd.Parameters.AddRange(parameters);
                        retVal = cmd.ExecuteScalar();
                    }
                    cn.Close();
                }
                return retVal;
            }
            public static DataTable sqlToTable(string sqlCommand)
            {
                return sqlToTable(sqlCommand, null);
            }
            public static DataTable sqlToTable(string sqlCommand, params SqlParameter[] parameters)
            {
                DataTable dt = new DataTable();
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sys"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlCommand, cn))
                    {
                        if (parameters != null) cmd.Parameters.AddRange(parameters);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd)) da.Fill(dt);
                    }
                    cn.Close();
                }
                return dt;
            }
            public static DataTable storedProcToTable(string procName, params SqlParameter[] parameters)
            {
                DataTable dt = new DataTable();
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sys"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(procName, cn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (parameters != null) cmd.Parameters.AddRange(parameters);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd)) da.Fill(dt);
                    }
                    cn.Close();
                }
                return dt;
            }
            public static DataSet storedProcToTables(string procName, params SqlParameter[] parameters)
            {
                DataSet ds = new DataSet();
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sys"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(procName, cn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (parameters != null) cmd.Parameters.AddRange(parameters);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd)) da.Fill(ds);
                    }
                    cn.Close();
                }
                return ds;
            }
            public static DataTable storedProcToTable(string procName)
            {
                return storedProcToTable(procName, null);
            }
            public static int executeSql(string sqlCommand, params SqlParameter[] parameters)
            {
                int ra = 0;
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sys"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlCommand, cn))
                    {
                        if (parameters != null) cmd.Parameters.AddRange(parameters);
                        ra = cmd.ExecuteNonQuery();
                    }
                    cn.Close();
                }
                return ra;
            }
            public static int executeStoredProc(string storedProc, params SqlParameter[] parameters)
            {
                int ra = 0;
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sys"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(storedProc, cn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (parameters != null) cmd.Parameters.AddRange(parameters);
                        ra = cmd.ExecuteNonQuery();
                    }
                    cn.Close();
                }
                return ra;
            }
            public static SqlParameterCollection executeStoredProcWithOutput(string storedProc, params SqlParameter[] parameters)
            {
                int ra = 0;
                SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sys"].ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(storedProc, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (parameters != null) cmd.Parameters.AddRange(parameters);
                ra = cmd.ExecuteNonQuery();
                cn.Close();
                return cmd.Parameters;
            }
        }
    }
}