public ClsPreparedQuery(ClsConnection_SqlServer pDa)
 {
     this.mDa = pDa;
     this.mCmd = new SqlCommand();
     this.mCmd.Connection = (SqlConnection)this.mDa.pConnection;
     this.mCmd.Transaction = (SqlTransaction)this.mDa.pTransaction;
     this.mCmd.CommandType = System.Data.CommandType.Text;
 }
 public ClsPreparedQuery()
 {
     this.mDa = new ClsConnection_SqlServer();
     this.mDa.Connect();
     this.mCmd = new SqlCommand();
     this.mCmd.Connection = (SqlConnection)this.mDa.pConnection;
     this.mCmd.Transaction = (SqlTransaction)this.mDa.pTransaction;
     this.mCmd.CommandType = System.Data.CommandType.Text;
     //this.IsDa = true;
 }
        /// <summary>
        /// Constructor for ClsPreparedQuery, uses the ClsConnection_SqlServer object
        /// </summary>
        /// <param name="Cn">
        /// An open ClsConnection_SqlServer object
        /// </param>
        /// <param name="Query">
        /// The query string to be executed
        /// </param>
        /// <param name="ArrSp">
        /// Array of parameters to be used
        /// </param>
        public ClsPreparedQuery(ClsConnection_SqlServer Cn, string Query, SqlParameter[] ArrSp)
        {
            this.mCn = Cn;
            this.mCmd = new SqlCommand();
            this.mCmd.Connection = (SqlConnection)this.mCn.pConnection;
            this.mCmd.Transaction = (SqlTransaction)this.mCn.pTransaction;
            this.mCmd.CommandType = System.Data.CommandType.Text;
            this.mCmd.CommandText = Query;

            foreach (SqlParameter Sp in ArrSp)
            { this.mCmd.Parameters.Add(Sp); }
        }
 public static DateTime GetServerDate()
 {
     ClsConnection_SqlServer Da = new ClsConnection_SqlServer();
     try
     {
         Da.Connect();
         return GetServerDate(Da);
     }
     catch (Exception ex)
     { throw ex; }
     finally
     { Da.Close(); }
 }
 public static DateTime GetServerDate(ClsConnection_SqlServer Da)
 {
     DataTable Dt = Da.ExecuteQuery("Select GetDate() As ServerDate").Tables[0];
     if (Dt.Rows.Count > 0) return (DateTime)Dt.Rows[0][0];
     else return DateTime.Now;
 }
        public static string GetSeriesNo(string Name)
        {
            string Rv = "";
            DataTable Dt;
            string TableName;
            string FieldName;
            string Prefix;
            Int32 Digits;

            Dt = new ClsBase().pDa.GetQuery("System_DocumentSeries", "", "ModuleName = '" + Name + "'");
            if (Dt.Rows.Count > 0)
            {
                TableName = (string)Do_Methods.IsNull(Dt.Rows[0]["TableName"], "");
                FieldName = (string)Do_Methods.IsNull(Dt.Rows[0]["FieldName"], "");
                Prefix = (string)Do_Methods.IsNull(Dt.Rows[0]["Prefix"], "");
                Digits = (Int32)Do_Methods.IsNull(Dt.Rows[0]["Digits"], "");
            }
            else
            { return Rv; }

            List<DataObjects_Framework.Common.Do_Constants.Str_Parameters> Sp = new List<DataObjects_Framework.Common.Do_Constants.Str_Parameters>();
            Sp.Add(new DataObjects_Framework.Common.Do_Constants.Str_Parameters("@TableName", TableName));
            Sp.Add(new DataObjects_Framework.Common.Do_Constants.Str_Parameters("@FieldName", FieldName));
            Sp.Add(new DataObjects_Framework.Common.Do_Constants.Str_Parameters("@Prefix", Prefix));
            Sp.Add(new DataObjects_Framework.Common.Do_Constants.Str_Parameters("@Digits", Digits));

            Dt = new ClsConnection_SqlServer().ExecuteQuery("usp_GetSeriesNo", Sp).Tables[0];
            if (Dt.Rows.Count > 0)
            { Rv = (string)Dt.Rows[0][0]; }

            return Rv;
        }
        public bool SaveDataRow(DataRow ObjDataRow, string TableName, string SchemaName = "", bool IsDelete = false, List<string> CustomKeys = null)
        {
            bool Cns_IsSoftDelete = Do_Globals.gSettings.pUseSoftDelete;

                //[-]

                if (SchemaName == "")
                { SchemaName = "dbo"; }

                eProcess cProcess = eProcess.Process_Insert;
                DataTable Dt_TableDef = new DataTable(TableName);
                DataTable Dt_Def;
                List<DataObjects_Framework.Common.Do_Constants.Str_Parameters> List_Param;

                //[Get Table Definition]
                List_Param = new List<DataObjects_Framework.Common.Do_Constants.Str_Parameters>();
                List_Param.Add(new DataObjects_Framework.Common.Do_Constants.Str_Parameters(@"@TableName", TableName));
                List_Param.Add(new DataObjects_Framework.Common.Do_Constants.Str_Parameters(@"@SchemaName", SchemaName));

                Dt_Def = this.ExecuteQuery("usp_DataObjects_GetTableDef", List_Param).Tables[0];
                foreach (DataRow Inner_Dr in Dt_Def.Rows)
                {
                    System.Type Inner_Type = null;
                    bool Inner_IsFound = true;
                    switch (Do_Methods.IsNull(Inner_Dr["DataType"], "").ToString().ToLower())
                    {
                        case "tinyint":
                            Inner_Type = typeof(System.Byte);
                            break;
                        case "smallint":
                            Inner_Type = typeof(System.Int16);
                            break;
                        case "int":
                            Inner_Type = typeof(System.Int32);
                            break;
                        case "bigint":
                            Inner_Type = typeof(System.Int64);
                            break;
                        case "bit":
                            Inner_Type = typeof(System.Boolean);
                            break;
                        case "decimal":
                        case "numeric":
                            Inner_Type = typeof(System.Double);
                            break;
                        case "datetime":
                        case "smalldatetime":
                            Inner_Type = typeof(System.DateTime);
                            break;
                        case "char":
                        case "varchar":
                        case "text":
                        case "nchar":
                        case "nvarchar":
                        case "ntext":
                            Inner_Type = typeof(System.String);
                            break;
                        default:
                            Inner_IsFound = false;
                            break;
                    }

                    if (Inner_IsFound) { Dt_TableDef.Columns.Add((string)Do_Methods.IsNull(Inner_Dr["ColumnName"], ""), Inner_Type); }
                }

                if (CustomKeys != null)
                {
                    foreach (DataRow Inner_Dr in Dt_Def.Rows)
                    { Inner_Dr["IsPk"] = false; }

                    foreach (string Inner_Key in CustomKeys)
                    {
                        DataRow[] Inner_ArrDr = Dt_Def.Select("ColumnName = '" + Inner_Key + "'");
                        if (Inner_ArrDr.Length > 0)
                        { Inner_ArrDr[0]["IsPk"] = true; }
                    }
                }

                //[Check ObjDataRow Fields for PK Data]

                bool IsFound = false;
                Int32 PKsCt = 0;
                Int32 PKsFoundCt = 0;

                DataRow[] ArrDr_Dt_Def;
                ArrDr_Dt_Def = Dt_Def.Select("IsPK = 1");
                PKsCt = ArrDr_Dt_Def.Length;

                foreach (DataRow Inner_Dr in ArrDr_Dt_Def)
                {
                    foreach (DataColumn Inner_Dc in ObjDataRow.Table.Columns)
                    {
                        if ((string)Inner_Dr["ColumnName"] == Inner_Dc.ColumnName)
                        {
                            if (Do_Methods.Convert_Int64(ObjDataRow[Inner_Dc.ColumnName]) != 0)
                            {
                                PKsFoundCt++;
                                if (PKsFoundCt >= PKsCt) break;
                            }
                        }
                    }
                }

                //Check Process
                if (PKsFoundCt != PKsCt)
                {
                    cProcess = eProcess.Process_Insert;

                    DataRow[] ArrDr_Dt_Def_Pks = Dt_Def.Select(@"IsPK = 1 And IsIdentity = 0");
                    foreach (DataRow Inner_Dr in ArrDr_Dt_Def_Pks)
                    {
                        //Check PK if there is already a value
                        //If there is, continue the loop
                        string Inner_ColumnName = (string)Do_Methods.IsNull(Inner_Dr["ColumnName"], "");
                        if (Do_Methods.Convert_Int64(ObjDataRow[Inner_ColumnName]) != 0)
                        { continue; }

                        ClsConnection_SqlServer Da = new ClsConnection_SqlServer();
                        try
                        {
                            Da.Connect();
                            Da.BeginTransaction();

                            Int64 NewID;
                            List_Param = new List<DataObjects_Framework.Common.Do_Constants.Str_Parameters>();
                            List_Param.Add(new DataObjects_Framework.Common.Do_Constants.Str_Parameters(@"@TableName", TableName + "." + Inner_ColumnName));
                            NewID = Do_Methods.Convert_Int64(Da.ExecuteQuery("usp_DataObjects_GetNextID", List_Param).Tables[0].Rows[0][0]);
                            ObjDataRow[Inner_ColumnName] = NewID;

                            Da.CommitTransaction();
                        }
                        catch (Exception ex)
                        {
                            Da.RollbackTransaction();
                            throw ex;
                        }
                        finally
                        {
                            Da.Close();
                            Da = null;
                        }
                    }
                }
                else
                {
                    //Check if Row to be updated has rows to be updated
                    //If none the return the function true
                    DataRow[] Inner_ArrDr = Dt_Def.Select(@"IsPk = 0 And IsIdentity = 0");
                    if (Inner_ArrDr.Length == 0) return true;
                    cProcess = eProcess.Process_Update;
                }

                if (IsDelete)
                {
                    if (cProcess == eProcess.Process_Update)
                    {
                        if (!Cns_IsSoftDelete)
                        { cProcess = eProcess.Process_Delete; }
                        else
                        { ObjDataRow["IsDeleted"] = true; }
                    }
                    else
                    { return true; }
                }

                //Prepare SQL Statement
                ClsPreparedQuery Pq = new ClsPreparedQuery(this);

                string Query_InsertFields = "";
                string Query_InsertFieldsValues = "";
                string Query_UpdateFields = "";
                string Query_Comma = "";

                foreach (DataColumn Dc_ObjDataRow in ObjDataRow.Table.Columns)
                {
                    IsFound = false;
                    foreach (DataColumn Dc_TableDef in Dt_TableDef.Columns)
                    {
                        if (Dc_ObjDataRow.ColumnName.ToLower() == Dc_TableDef.ColumnName.ToLower())
                        {
                            switch (cProcess)
                            {
                                case eProcess.Process_Insert:
                                    IsFound = true;
                                    break;
                                case eProcess.Process_Update:
                                    DataRow[] Inner_ArrDr = Dt_Def.Select(@"ColumnName = '" + Dc_ObjDataRow.ColumnName + "' And IsPk = 1");
                                    if (Inner_ArrDr.Length == 0) IsFound = true;
                                    break;
                            }
                            if (IsFound) break;
                        }
                    }

                    if (IsFound)
                    {
                        DataRow[] Inner_ArrDr_Def = Dt_Def.Select(@"ColumnName = '" + Dc_ObjDataRow.ColumnName + "'" + " And IsIdentity = 0");
                        if (Inner_ArrDr_Def.Length == 0) continue;

                        switch (cProcess)
                        {
                            case eProcess.Process_Insert:
                                Query_InsertFields += " " + Query_Comma + @" [" + Dc_ObjDataRow.ColumnName + @"] ";
                                Query_InsertFieldsValues += " " + Query_Comma + @" @" + Dc_ObjDataRow.ColumnName.Replace(@" ", @"_") + " ";
                                break;
                            case eProcess.Process_Update:
                                Query_UpdateFields += " " + Query_Comma + @" [" + Dc_ObjDataRow.ColumnName + @"] = @" + Dc_ObjDataRow.ColumnName.Replace(@" ", @"_") + " ";
                                break;
                        }

                        Query_Comma = ",";

                        SqlParameter Inner_Sp = new SqlParameter(@"@" + Dc_ObjDataRow.ColumnName.Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_ArrDr_Def[0]["DataType"]), Convert.ToInt32(Inner_ArrDr_Def[0]["Length"]));
                        Inner_Sp.Direction = ParameterDirection.Input;
                        Inner_Sp.Precision = (byte)Inner_ArrDr_Def[0]["Precision"];
                        Inner_Sp.Scale = (byte)Inner_ArrDr_Def[0]["Scale"];
                        Pq.pParameters.Add(Inner_Sp);
                    }
                }

                DataRow[] Inner_ArrDr_Pk;

                switch (cProcess)
                {
                    case eProcess.Process_Insert:
                        StringBuilder Sb_Query_Output = new StringBuilder();
                        StringBuilder Sb_Query_Output_Table = new StringBuilder();
                        string Query_Output = "";
                        char Query_Output_Comma = ' ';
                        string Query_Output_Table = "";
                        string Query_Output_Table_Select = "";

                        Inner_ArrDr_Pk = Dt_Def.Select("IsPK = 1 And IsIdentity = 1");
                        foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
                        {
                            Sb_Query_Output.Append(@" " + Query_Output_Comma + @" Inserted.[" + (string)Do_Methods.IsNull(Inner_Dr["ColumnName"], "") + @"] Into @Tb");
                            Sb_Query_Output_Table.Append(@" " + Query_Output_Comma + @" [" + (string)Do_Methods.IsNull(Inner_Dr["ColumnName"], "") + @"] " + (string)Do_Methods.IsNull(Inner_Dr["DataType"], ""));
                            Query_Output_Comma = ',';
                        }

                        Query_Output = Sb_Query_Output.ToString();
                        if (Query_Output.Trim() != "") Query_Output = " Output " + Query_Output;

                        Query_Output_Table = Sb_Query_Output_Table.ToString();
                        if (Query_Output_Table.Trim() != "")
                        {
                            Query_Output_Table = @" Declare @Tb As Table (" + Query_Output_Table + @"); ";
                            Query_Output_Table_Select = " Select * From @Tb ";
                        }

                        if (Query_InsertFields != "")
                        {
                            Query_InsertFields = "(" + Query_InsertFields + ")";
                            Query_InsertFieldsValues = " Values (" + Query_InsertFieldsValues + ") ";
                        }
                        else
                        {
                            //This path will be reached if the table to be inserted has only one field that is an identity field
                            Query_InsertFieldsValues = " Default Values ";
                        }

                        Pq.pQuery = Query_Output_Table + " Insert Into [" + SchemaName + "].[" + TableName + "] " + Query_InsertFields + " " + Query_Output + " " + Query_InsertFieldsValues + "; " + Query_Output_Table_Select;
                        break;
                    case eProcess.Process_Update:
                        string Query_UpdateCriteria = "";
                        Query_Comma = "";

                        Inner_ArrDr_Pk = Dt_Def.Select("IsPk = 1");
                        foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
                        {
                            DataRow[] Inner_ArrDr_TableDef = Dt_Def.Select(@"ColumnName = '" + (string)Inner_Dr["ColumnName"] + @"'");
                            Query_UpdateCriteria += " " + Query_Comma + " [" + Inner_Dr["ColumnName"] + "] = @" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_") + " ";
                            Query_Comma = "And";

                            SqlParameter Inner_Sp = new SqlParameter("@" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_Dr["DataType"]), Convert.ToInt32(Inner_Dr["Length"]));
                            Inner_Sp.Direction = ParameterDirection.Input;
                            Inner_Sp.Precision = (byte)Inner_Dr["Precision"];
                            Inner_Sp.Scale = (byte)Inner_Dr["Scale"];
                            Pq.pParameters.Add(Inner_Sp);
                        }

                        Pq.pQuery = "Update [" + SchemaName + "].[" + TableName + "] Set " + Query_UpdateFields + " Where " + Query_UpdateCriteria;
                        break;
                    case eProcess.Process_Delete:
                        string Query_DeleteCriteria = "";
                        Query_Comma = "";

                        Inner_ArrDr_Pk = Dt_Def.Select("IsPk = 1");
                        foreach (DataRow Inner_Dr in Inner_ArrDr_Pk)
                        {
                            DataRow[] Inner_ArrDr_TableDef = Dt_Def.Select(@"ColumnName = '" + (string)Inner_Dr["ColumnName"] + @"'");
                            Query_DeleteCriteria += " " + Query_Comma + " [" + Inner_Dr["ColumnName"] + "] = @" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_") + " ";
                            Query_Comma = "And";

                            SqlParameter Inner_Sp = new SqlParameter("@" + ((string)Inner_Dr["ColumnName"]).Replace(" ", "_"), this.SqlDataTypeLib((string)Inner_Dr["DataType"]), Convert.ToInt32(Inner_Dr["Length"]));
                            Inner_Sp.Direction = ParameterDirection.Input;
                            Inner_Sp.Precision = (byte)Inner_Dr["Precision"];
                            Inner_Sp.Scale = (byte)Inner_Dr["Scale"];
                            Pq.pParameters.Add(Inner_Sp);
                        }

                        Pq.pQuery = "Delete [" + SchemaName + "].[" + TableName + "] Where " + Query_DeleteCriteria;
                        break;
                }

                Pq.Prepare();

                foreach (DataColumn Dc_ObjDataRow in ObjDataRow.Table.Columns)
                {
                    foreach (SqlParameter Inner_Sp in Pq.pParameters)
                    {
                        if ("@" + Dc_ObjDataRow.ColumnName.Replace(" ", "_") == Inner_Sp.ParameterName)
                        {
                            if (Information.IsDBNull(Dc_ObjDataRow)) Inner_Sp.Value = DBNull.Value;
                            else Inner_Sp.Value = this.SqlConvertDataType(ObjDataRow[Dc_ObjDataRow], Inner_Sp.SqlDbType.ToString());
                            continue;
                        }
                    }
                }

                DataSet Ds_Output;
                DataTable Dt_Output;

                Ds_Output = Pq.ExecuteQuery();
                if (Ds_Output.Tables.Count > 0)
                {
                    Dt_Output = Ds_Output.Tables[0];
                    foreach (DataColumn Inner_Dc in Dt_Output.Columns)
                    {
                        ObjDataRow[Inner_Dc.ColumnName] = Dt_Output.Rows[0][Inner_Dc.ColumnName];
                    }
                }
                return true;
        }