SQLUtility.cs


using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Xml.Schema;

namespace CPUFramework
{
    public class SQLUtility
    {
        private static string ConnectionString = "";

        public static void SetConnectionString(string connstring, bool tryopen, string userid = "", string password = "")
        {
            ConnectionString = connstring;
            if(userid != "")
            {
                SqlConnectionStringBuilder b = new();
                b.ConnectionString = ConnectionString;
                b.UserID = userid;
                b.Password = password;
                ConnectionString = b.ConnectionString;
            }
            if (tryopen)
            {
                using(SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                }
            }
        }

        public static SqlCommand GetSQLCommand(string sprocname)
        {
            SqlCommand cmd;
            using (SqlConnection conn = new SqlConnection(SQLUtility.ConnectionString))
            {
                cmd = new SqlCommand(sprocname, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();
                SqlCommandBuilder.DeriveParameters(cmd);
            }
            
            return cmd;
        }

            public static DataTable GetDataTable(SqlCommand cmd)
        {
            return DoExecuteSQL(cmd, true);
        }

        public static void SaveDataTable(DataTable dt, string sprocname)
        {
            var rows = dt.Select("", "", DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
            foreach(DataRow r in rows)
            {
                SaveDataRow(r, sprocname, false);
            }
            dt.AcceptChanges();
        }

        public static void SaveDataRow(DataRow row, string sprocname, bool acceptchanges = true)
        {
            SqlCommand cmd = GetSQLCommand(sprocname);
            foreach(DataColumn col in row.Table.Columns)
            {
                string paramname = $"@{col.ColumnName}";
                if (cmd.Parameters.Contains(paramname))
                {
                    cmd.Parameters[paramname].Value = row[col.ColumnName];
                }
            }
            DoExecuteSQL(cmd, false);
            row.Table.AcceptChanges();
            foreach(SqlParameter p in cmd.Parameters)
            {
                string colname = p.ParameterName.Substring(1);
                if (row.Table.Columns.Contains(colname))
                {
                    row[colname] = p.Value;
                    row.Table.AcceptChanges();
                }
            }
            if (acceptchanges == true)
            {
                row.Table.AcceptChanges();
            }
        }

        public static void SaveChildTable(DataTable dt, int parentid, string parentcolumnname, string sprocname)
        {
            foreach (DataRow r in dt.Select("", "", DataViewRowState.Added))
            {
                r[parentcolumnname] = parentid;
            }
            SaveDataTable(dt, sprocname);
        }

        public static void DeleteChildTable(int childtableid, string sprocname, string childtableparamname)
        {
            SqlCommand cmd = GetSQLCommand(sprocname);
            cmd.Parameters[childtableparamname].Value = childtableid;
            SQLUtility.ExecuteSQL(cmd);
        }

        private static DataTable DoExecuteSQL(SqlCommand cmd, bool loadtable)
        {
            Debug.Print("-----" +Environment.NewLine + cmd.CommandText);
            DataTable dt = new();
            using(SqlConnection conn = new SqlConnection(SQLUtility.ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                try
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    CheckReturnValue(cmd);
                    if (loadtable == true)
                    {
                        dt.Load(dr);
                    }
                }
                catch(SqlException ex)
                {
                    string msg = ParseConstraintMsg(ex.Message);
                    throw new Exception(msg); 
                }
                catch(InvalidCastException ex)
                {
                    throw new Exception(cmd.CommandText + ": " + ex.Message, ex);
                }
            }
            SetAllColumnsProperties(dt);
            return dt;
        }

        private static void CheckReturnValue(SqlCommand cmd)
        {
            int returnvalue = 0;
            string msg = "";
            if (cmd.CommandType == CommandType.StoredProcedure)
            {
                foreach (SqlParameter p in cmd.Parameters)
                {
                    if (p.Direction == ParameterDirection.ReturnValue)
                    {
                        if (p.Value != null)
                        {
                            returnvalue = (int)p.Value;
                        }
                    }
                    else if (p.ParameterName.ToLower() == "@message")
                    {
                        if (p.Value != null)
                        {
                            msg = p.Value.ToString();
                        }
                    }
                }
                if (returnvalue == 1)
                {
                    if (msg == "")
                    {
                        msg = $"{cmd.CommandText} did not do action that was requested";
                    }
                    throw new Exception(msg);
                }
            }
        }

        public static DataTable GetDataTable(string sqlstatement)
        {
            return DoExecuteSQL(new SqlCommand(sqlstatement), true);
        }


        public static void ExecuteSQL(SqlCommand cmd)
        {
            DoExecuteSQL(cmd, false);
        }

        public static void ExecuteSQL(string sqlstatement)
        {
            GetDataTable(sqlstatement);
        }

        public static void SetParamValue(SqlCommand cmd, string paramname, object value)
        {
            if(paramname.StartsWith("@") == false) { paramname = "@" + paramname; }
            try
            {
                cmd.Parameters[paramname].Value = value;
            }
            catch(Exception ex)
            {
                throw new Exception(cmd.CommandText + ": " + ex.Message);
            }
        }

        public static string ParseConstraintMsg(string msg)
        {
            //PartyId', table 'RecordKeeperDB.dbo.President'; column does not allow nulls. INSERT fails.
            
            string origmsg = msg;
            string prefix = "ck_";
            string msgend = "";
            string notnullprefix = "Cannot insert the value NULL into column '";
            string msgstart = GetColumnFromConstraintName(msg);
            if (msg.Contains(prefix) == false)
            {
                if (msg.Contains("u_"))
                {
                    prefix = "u_";
                    msg = msgstart + " must be unique.";
                }
                else if (msg.Contains("f_"))
                {
                    prefix = "f_";
                }
                else if (msg.Contains(notnullprefix))
                {
                    prefix = notnullprefix;
                    msgend = " cannot be blank.";
                }
            }
            if (msg.Contains(prefix))
            {
                msg = msg.Replace("\"", "'");
                int pos = msg.IndexOf(prefix) + prefix.Length;
                msg = msg.Substring(pos);
                pos = msg.IndexOf("'");
                if (pos == -1)
                {
                    msg = origmsg;
                }
                else
                {
                    msg = msg.Substring(0, pos);
                    msg = msg.Replace("_", " ");
                    msg = msg + msgend;

                    if(prefix == "f_")
                    {
                        var words = msg.Split(" ");
                        if(words.Length > 1)
                        {
                            msg = $"Cannot delete {words[0]} because it has a related {words[1]} record.";
                        }
                    }
                }
            }
            return msg;
        }

        private static string GetColumnFromConstraintName(string constraintmsg)
        {
            int start = constraintmsg.IndexOf('\'');
            int end = constraintmsg.IndexOf('\'', start + 1);

            string constraintname = constraintmsg.Substring(start + 1, end - start - 1);

            return constraintname.Split('_').Last();
        }

        public static int GetFirstColumnFirstRowValue(string sql)
        {
            int n = 0;
            DataTable dt = GetDataTable(sql);
            if(dt.Rows.Count > 0 && dt.Columns.Count > 0)
            {
                if (dt.Rows[0][0] != DBNull.Value)
                {
                    int.TryParse(dt.Rows[0][0].ToString(), out n);
                }
            }
            return n;
        }

        private static void SetAllColumnsProperties(DataTable dt)
        {
            foreach (DataColumn c in dt.Columns)
            {
                c.AllowDBNull = true;
                c.AutoIncrement = false;
            }


        }

        public static int GetValueFromFirstRowAsInt(DataTable dt, string columnname)
        {
            int value = 0;
            if(dt.Rows.Count > 0)
            {
                DataRow r = dt.Rows[0];
                if (r[columnname] != null && r[columnname] is int)
                {
                    value = (int)r[columnname];
                }
            }
            return value;
        }

        public static string GetValueFromFirstRowAsString(DataTable dt, string columnname)
        {
            string value = "";
            if (dt.Rows.Count > 0)
            {
                DataRow r = dt.Rows[0];
                if (r[columnname] != null && r[columnname] is string)
                {
                    value = (string)r[columnname];
                }
            }
            return value;
        }

        public static bool TableHasChanges(DataTable dt)
        {
            bool b = false;
            if(dt.GetChanges() != null)
            {
                b = true;
            }
            return b;

        }

        public static void DebugPrintDataTable(DataTable dt)
        {
            foreach (DataRow r in dt.Rows)
            {
                foreach (DataColumn c in dt.Columns)
                {
                    Debug.Print(c.ColumnName + " = " + r[c.ColumnName].ToString());
                }
            }
        }
    }
}