// -------------------------------------------------------------------------------- public void Load(string context, ref string aux) { //CREATE PROCEDURE spGKv2_OU_GetOUTypes // @context VARCHAR(1020), // @aux VARCHAR(1020) OUTPUT // Init params StoredProcedureCallContext.ClearStandardParams(ref context, ref aux); StoredProcedureCallContext spContext = new StoredProcedureCallContext("GKSDB", "spGKv2_OU_GetOUTypes"); DataSet ds = spContext.ExecuteDataSet(); this.Clear(); if (ds != null) { DataTable dt = ds.Tables[0]; if (dt != null) { int idxId = dt.Columns.IndexOf("Id"); int idxDescription = dt.Columns.IndexOf("Description"); int idxRefCnt = dt.Columns.IndexOf("RefCount"); foreach (DataRow dr in dt.Rows) { OUType ouType = new OUType((int)dr[idxId], (string)dr[idxDescription], (int)dr[idxRefCnt]); this.Add(ouType); } } } aux = spContext.Aux; }
// ================================================================================ //private void ClearStandardParams(ref string context, ref string aux) //{ // context = string.Empty; // aux = string.Empty; //} // ================================================================================ private Guid?[] GetObjectIDsFromCprArrayImpl(string context, string[] cprNoArr, Guid objectOwnerID, ref string aux) { //CREATE PROCEDURE spGK_PM_GetObjectIDsFromCPRArray // @context VARCHAR(1020), // @cprNoArray VARCHAR(MAX), // @objectOwnerID uniqueidentifier, // @aux VARCHAR(1020) OUTPUT // Init params StoredProcedureCallContext.ClearStandardParams(ref context, ref aux); StoredProcedureCallContext spContext = new StoredProcedureCallContext("CPRMapperDB", "spGK_PM_GetObjectIDsFromCPRArray"); var cprNoArrComma = Array.ConvertAll <string, string>(cprNoArr, s => string.Format("{0}", s).Trim().Replace(",", "") + ","); spContext.AddInParameter("cprNoArray", DbType.String, string.Join("", cprNoArrComma)); if (objectOwnerID == Guid.Empty) { spContext.AddInParameter("objectOwnerID", DbType.Guid, null); } else { spContext.AddInParameter("objectOwnerID", DbType.Guid, objectOwnerID); } Dictionary <string, List <string> > errors = new Dictionary <string, List <string> >(); var dataSet = spContext.ExecuteDataSet(); var returnTable = dataSet.Tables[0]; var ret = new Guid?[returnTable.Rows.Count]; for (int iRow = 0; iRow < returnTable.Rows.Count; iRow++) { var row = returnTable.Rows[iRow]; if (row.IsNull("ObjectID")) { string error = row["Aux"] as string; string cprNumber = row["CprNo"] as string; if (!errors.ContainsKey(error)) { errors.Add(error, new List <string>()); } errors[error].Add(cprNumber); } else { ret[iRow] = (Guid)row["ObjectID"]; } } aux = string.Join( Environment.NewLine, errors.Select(e => string.Format("{0}: {1}", e.Key, string.Join(", ", e.Value))) ); return(ret); }
// ================================================================================ public string[] GetAllLoginnamesFromCpr(string context, string cprNo, ref string aux) { //CREATE PROCEDURE spGK_PMU_GetLoginNameFromCPR // @context VARCHAR(120), // @cprNo VARCHAR(10), // @loginName VARCHAR(30) OUTPUT, // @aux VARCHAR(1020) OUTPUT string[] sa = new string[0]; // Init params StoredProcedureCallContext.ClearStandardParams(ref context, ref aux); StoredProcedureCallContext spContext = new StoredProcedureCallContext("CPRMapperDB", "spGK_PMU_GetLoginNameFromCPR"); spContext.AddInParameter("cprNo", DbType.String, cprNo.Trim()); spContext.AddOutParameter("loginName", DbType.String, 30); DataSet ds = spContext.ExecuteDataSet(); if (ds != null) { int tblCnt = ds.Tables.Count; if (tblCnt > 0) { // It's the last table that contains login names DataTable dt = ds.Tables[tblCnt - 1]; if (dt != null) { int rowCnt = dt.Rows.Count; sa = new string[rowCnt]; for (int i = 0; i < rowCnt; i++) { sa[i] = (string)dt.Rows[i]["loginName"]; } } } } aux = spContext.Aux; return(sa); }
// -------------------------------------------------------------------------------- public static int SearchOUWhere(string context, string whereClause, ref DataSet ds, ref string aux, bool extendedResult) { //CREATE PROCEDURE spGKv2_OU_BasicOp_SearchWhere // @context VARCHAR(1020), // @whereClause VARCHAR(1020), // @extendedResult INTEGER, // @aux VARCHAR(1020) OUTPUT // Init params StoredProcedureCallContext.ClearStandardParams(ref context, ref aux); StoredProcedureCallContext spContext = new StoredProcedureCallContext("GKSDB", "spGKv2_OU_BasicOp_SearchWhere"); spContext.AddInParameter("whereClause", DbType.String, whereClause); spContext.AddInParameter("extendedResult", DbType.Int32, (extendedResult ? 1 : 0)); ds = spContext.ExecuteDataSet(); aux = spContext.Aux; return(spContext.ReturnValue); }
// ================================================================================ private String[] GetCPRsFromObjectIDArrayImpl(string context, string[] objectIDArr, ref string aux) { /* * CREATE PROCEDURE [dbo].[spGK_PM_GetCPRsFromObjectIDArray] * @context VARCHAR(1020), * @objectIDArray VARCHAR(MAX), * @aux VARCHAR(1020) OUTPUT */ // Init params StoredProcedureCallContext.ClearStandardParams(ref context, ref aux); StoredProcedureCallContext spContext = new StoredProcedureCallContext("CPRMapperDB", "spGK_PM_GetCPRsFromObjectIDArray"); // We check if all Strings are indead Guids. In opposite situations we set an empty String at the given position(s) Regex isGuid = new Regex(@"[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}$", RegexOptions.Compiled); for (int i = 0; i < objectIDArr.Length; i++) { if (objectIDArr[i] != null) { if (objectIDArr[i].Length != 36) { objectIDArr[i] = ""; } else if (!isGuid.IsMatch(objectIDArr[i])) { objectIDArr[i] = ""; } } else { objectIDArr[i] = ""; } } var objectIDArrComma = Array.ConvertAll <string, string>(objectIDArr, s => string.Format("{0}", s).Trim().Replace(",", "") + ","); String commSepString = string.Join("", objectIDArrComma); // Checking output: Debug.WriteLine("Debug objectID array: " + commSepString); spContext.AddInParameter("objectIDArray", DbType.String, string.Join("", objectIDArrComma)); Dictionary <string, List <string> > errors = new Dictionary <string, List <string> >(); var dataSet = spContext.ExecuteDataSet(); var returnTable = dataSet.Tables[0]; var ret = new String[returnTable.Rows.Count]; for (int iRow = 0; iRow < returnTable.Rows.Count; iRow++) { var row = returnTable.Rows[iRow]; if (row.IsNull("CprNo")) { string error = row["Aux"] as string; string objectID = row["ObjectID"] as string; if (!errors.ContainsKey(error)) { errors.Add(error, new List <string>()); } errors[error].Add(objectID); } else { ret[iRow] = (String)row["CprNo"]; } } aux = string.Join( Environment.NewLine, errors.Select(e => string.Format("{0}: {1}", e.Key, string.Join(", ", e.Value))) ); return(ret); }
// ================================================================================ //private void ClearStandardParams(ref string context, ref string aux) //{ // context = string.Empty; // aux = string.Empty; //} // ================================================================================ private Guid?[] GetObjectIDsFromCprArrayImpl(string context, string[] cprNoArr, Guid objectOwnerID, ref string aux) { //CREATE PROCEDURE spGK_PM_GetObjectIDsFromCPRArray // @context VARCHAR(1020), // @cprNoArray VARCHAR(MAX), // @objectOwnerID uniqueidentifier, // @aux VARCHAR(1020) OUTPUT // Init params StoredProcedureCallContext.ClearStandardParams(ref context, ref aux); StoredProcedureCallContext spContext = new StoredProcedureCallContext("CPRMapperDB", "spGK_PM_GetObjectIDsFromCPRArray"); var cprNoArrComma = Array.ConvertAll<string, string>(cprNoArr, s => string.Format("{0}", s).Trim().Replace(",", "") + ","); spContext.AddInParameter("cprNoArray", DbType.String, string.Join("", cprNoArrComma)); if (objectOwnerID == Guid.Empty) { spContext.AddInParameter("objectOwnerID", DbType.Guid, null); } else { spContext.AddInParameter("objectOwnerID", DbType.Guid, objectOwnerID); } Dictionary<string, List<string>> errors = new Dictionary<string, List<string>>(); var dataSet = spContext.ExecuteDataSet(); var returnTable = dataSet.Tables[0]; var ret = new Guid?[returnTable.Rows.Count]; for (int iRow = 0; iRow < returnTable.Rows.Count; iRow++) { var row = returnTable.Rows[iRow]; if (row.IsNull("ObjectID")) { string error = row["Aux"] as string; string cprNumber = row["CprNo"] as string; if (!errors.ContainsKey(error)) { errors.Add(error, new List<string>()); } errors[error].Add(cprNumber); } else { ret[iRow] = (Guid)row["ObjectID"]; } } aux = string.Join( Environment.NewLine, errors.Select(e => string.Format("{0}: {1}", e.Key, string.Join(", ", e.Value))) ); return ret; }
// ================================================================================ private String[] GetCPRsFromObjectIDArrayImpl(string context, string[] objectIDArr, ref string aux) { /* * CREATE PROCEDURE [dbo].[spGK_PM_GetCPRsFromObjectIDArray] * @context VARCHAR(1020), * @objectIDArray VARCHAR(MAX), * @aux VARCHAR(1020) OUTPUT */ // Init params StoredProcedureCallContext.ClearStandardParams(ref context, ref aux); StoredProcedureCallContext spContext = new StoredProcedureCallContext("CPRMapperDB", "spGK_PM_GetCPRsFromObjectIDArray"); // We check if all Strings are indead Guids. In opposite situations we set an empty String at the given position(s) Regex isGuid = new Regex(@"[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}$", RegexOptions.Compiled); for (int i = 0; i < objectIDArr.Length; i++) { if (objectIDArr[i] != null) { if (objectIDArr[i].Length != 36) objectIDArr[i] = ""; else if (!isGuid.IsMatch(objectIDArr[i])) objectIDArr[i] = ""; } else objectIDArr[i] = ""; } var objectIDArrComma = Array.ConvertAll<string, string>(objectIDArr, s => string.Format("{0}", s).Trim().Replace(",", "") + ","); String commSepString = string.Join("", objectIDArrComma); // Checking output: Debug.WriteLine("Debug objectID array: " + commSepString); spContext.AddInParameter("objectIDArray", DbType.String, string.Join("", objectIDArrComma)); Dictionary<string, List<string>> errors = new Dictionary<string, List<string>>(); var dataSet = spContext.ExecuteDataSet(); var returnTable = dataSet.Tables[0]; var ret = new String[returnTable.Rows.Count]; for (int iRow = 0; iRow < returnTable.Rows.Count; iRow++) { var row = returnTable.Rows[iRow]; if (row.IsNull("CprNo")) { string error = row["Aux"] as string; string objectID = row["ObjectID"] as string; if (!errors.ContainsKey(error)) { errors.Add(error, new List<string>()); } errors[error].Add(objectID); } else { ret[iRow] = (String)row["CprNo"]; } } aux = string.Join( Environment.NewLine, errors.Select(e => string.Format("{0}: {1}", e.Key, string.Join(", ", e.Value))) ); return ret; }
// ================================================================================ public string[] GetAllLoginnamesFromCpr(string context, string cprNo, ref string aux) { //CREATE PROCEDURE spGK_PMU_GetLoginNameFromCPR // @context VARCHAR(120), // @cprNo VARCHAR(10), // @loginName VARCHAR(30) OUTPUT, // @aux VARCHAR(1020) OUTPUT string[] sa = new string[0]; // Init params StoredProcedureCallContext.ClearStandardParams(ref context, ref aux); StoredProcedureCallContext spContext = new StoredProcedureCallContext("CPRMapperDB", "spGK_PMU_GetLoginNameFromCPR"); spContext.AddInParameter("cprNo", DbType.String, cprNo.Trim()); spContext.AddOutParameter("loginName", DbType.String, 30); DataSet ds = spContext.ExecuteDataSet(); if (ds != null) { int tblCnt = ds.Tables.Count; if (tblCnt > 0) { // It's the last table that contains login names DataTable dt = ds.Tables[tblCnt - 1]; if (dt != null) { int rowCnt = dt.Rows.Count; sa = new string[rowCnt]; for (int i = 0; i < rowCnt; i++) { sa[i] = (string)dt.Rows[i]["loginName"]; } } } } aux = spContext.Aux; return sa; }