public List <T> RunSp <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName, int recordCount, int pageNumber, out int allRecordCount) { var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value); Parameters.Clear(); List <T> list; var com = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout); var dt = new DataTable(); try { using (var result = com.ExecuteReader()) { var schemaTable = result.GetSchemaTable(); if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0) { var sqlSpException = new DataException(); //sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString()); while (result.Read()) { sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString()); } throw sqlSpException; } if (typeof(T).IsValueType || "".GetType() == typeof(T)) { list = new List <T>(); Parallel.ForEach(SimpleParallelDataReader <T>(result), (data) => { list.Add(data); }); allRecordCount = list.Count; return(list); } return(new GeneralMethods().ToViewModel <T>(result, recordCount, pageNumber, out allRecordCount)); //dt.Load(result); ////var tempTable = dt.n ////for(int i = 0; i <= ) //var l = dt.AsEnumerable(); //.Select(); //allRecordCount = l.Count(); //var newList = l.Skip((pageNumber - 1)*recordCount).Take(recordCount); //list = new GeneralMethods().ToViewModel<T>(newList); } } catch (DataException) { Parameters.Clear(); throw; } finally { ConnectionManager.ReleaseConnection(com.Connection); } }
public DataSet RunSpReturnedDirectDataSet(ConnectionNameEnum connectionNameEnum, string storeProcureName) { var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value); Parameters.Clear(); var command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout); try { var da = new SqlDataAdapter(command); var dt = new DataSet(); da.Fill(dt); return(dt); } catch { return(new DataSet()); } finally { ConnectionManager.ReleaseConnection(command.Connection); Parameters.Clear(); } }
/// <summary> /// Runs the sp. /// </summary> /// <param name="connectionNameEnum">The connection name enum.</param> /// <param name="storeProcureName">Name of the store procure.</param> /// <returns>Scalar data of sp</returns> /// <exception>SqlSPException ex /// <cref>SqlSpException ex</cref> /// </exception> public static object RunSp(ConnectionNameEnum connectionNameEnum, string storeProcureName) { object returnedObject = null; SqlCommand command = SqlGeneralMethods.MakeSqlCommand(connectionNameEnum, storeProcureName, Parameters); SqlDataReader dr = command.ExecuteReader(); if (dr.Read()) { var schemaTable = dr.GetSchemaTable(); if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0) { var sqlSpException = new ShonizSqlSpException(); sqlSpException.Add((int)dr["ErrorCode"], dr["ErrorMessage"].ToString()); while (dr.Read()) { sqlSpException.Add((int)dr["ErrorCode"], dr["ErrorMessage"].ToString()); } ConnectionManager.ReleaseConnection(command.Connection); Parameters.Clear(); throw sqlSpException; } returnedObject = dr[0]; } ConnectionManager.ReleaseConnection(command.Connection); Parameters.Clear(); return(returnedObject); }
public List <T> GetTable <T>(ConnectionNameEnum connectionNameEnum, string tableName, string whereCondition) { List <T> list; var com = GeneralMethods.MakeQuerySqlCommand(connectionNameEnum, "Select * from " + tableName + " " + whereCondition, ConnectionTimeout); try { using (var result = com.ExecuteReader()) { if (typeof(T).IsValueType || "".GetType() == typeof(T)) { list = new List <T>(); Parallel.ForEach(SimpleParallelDataReader <T>(result), (data) => { list.Add(data); }); return(list); } list = new GeneralMethods().ToViewModel <T>(result); } } catch (DataException) { Parameters.Clear(); throw; } finally { ConnectionManager.ReleaseConnection(com.Connection); } return(list); }
public IList RunQuery(string assemblyName, string typeName, ConnectionNameEnum connectionNameEnum, string query) { MethodInfo method = typeof(StoreProcdureManagement).GetMethods().First(m => m.Name == "RunQuery" && m.GetGenericArguments().Length == 1); var typeArgs = Assembly.Load(assemblyName).GetTypes().First(t => t.Name == typeName); MethodInfo generic = method.MakeGenericMethod(typeArgs); return((IList)generic.Invoke(this, new[] { (object)connectionNameEnum, (object)query })); }
public async Task <List <T> > RunSpAsync <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName, int pageIndex = 0, int pageLength = 0) { var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value); Parameters.Clear(); List <T> list; SqlCommand com; if (connectionNameEnum == ConnectionNameEnum.DirectConnectionString) { com = new GeneralMethods().MakeSqlCommand(_ConnectionString, storeProcureName, param, ConnectionTimeout); } else { com = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout); } using (var result = await com.ExecuteReaderAsync()) { try { var schemaTable = result.GetSchemaTable(); if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0) { var sqlSpException = new DataException(); //sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString()); while (result.Read()) { sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString()); } throw sqlSpException; } if (typeof(T).IsValueType || "".GetType() == typeof(T)) { list = new List <T>(); Parallel.ForEach(SimpleParallelDataReader <T>(result), (data) => { list.Add(data); }); return(list); } var gm = new GeneralMethods(); list = gm.ToViewModel <T>(result, pageIndex, pageLength); CurrentListLength = gm.RecordCount; } catch (DataException) { Parameters.Clear(); throw; } finally { ConnectionManager.ReleaseConnection(com.Connection); } } return(list); }
/// <summary> /// Gets the connection. /// </summary> /// <param name="connectionNameEnum">The connection name enum(An enum that has fields /// which named by web.config connection string).</param> /// <returns>New Connection</returns> internal SqlConnection GetConnection(ConnectionNameEnum connectionNameEnum) { var connection = new SqlConnection( System.Configuration.ConfigurationManager. ConnectionStrings[connectionNameEnum.ToString()].ConnectionString); connection.Open(); return(connection); }
/// <summary> /// Gets the connection. /// </summary> /// <param name="connectionNameEnum">The connection name enum(An enum that has fields /// which named by web.config connection string).</param> /// <returns>New Connection</returns> public static SqlConnection GetConnection(ConnectionNameEnum connectionNameEnum) { SqlConnection connection = null; connection = new SqlConnection( System.Configuration.ConfigurationManager. ConnectionStrings[connectionNameEnum.ToString()].ConnectionString); connection.Open(); return(connection); }
public List <dynamic> RunSpReturnDynamic(ConnectionNameEnum connectionNameEnum, string storeProcureName, int pageIndex = 0, int pageLength = 0) { var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value); Parameters.Clear(); List <dynamic> list = new List <dynamic>(); SqlCommand com; com = connectionNameEnum == ConnectionNameEnum.DirectConnectionString ? new GeneralMethods().MakeSqlCommand(_ConnectionString, storeProcureName, param, ConnectionTimeout) : new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout); try { using (var result = com.ExecuteReader()) { var schemaTable = result.GetSchemaTable(); if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0) { var sqlSpException = new DataException(); //sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString()); while (result.Read()) { sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString()); } throw sqlSpException; } Parallel.ForEach(DynamicParallelDataReader(result, schemaTable), (data) => { list.Add(data); }); } } catch (DataException) { Parameters.Clear(); throw; } catch (Exception ex) { var exp = new DataException(); exp.Data.Add("Error", ex); throw exp; } finally { ConnectionManager.ReleaseConnection(com.Connection); } return(list); }
/// <summary> /// Makes the SQL command. /// </summary> /// <param name="connectionNameEnum">The connection name enum.</param> /// <param name="storeProcureName">Name of the store procure.</param> /// <param name="parameters">The parameters.</param> /// <returns></returns> public static SqlCommand MakeSqlCommand(ConnectionNameEnum connectionNameEnum, string storeProcureName, Dictionary <string, object> parameters) { var sqlCommand = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = storeProcureName }; foreach (var r in parameters) { sqlCommand.Parameters.Add(new SqlParameter(r.Key, r.Value)); } sqlCommand.Connection = ConnectionManager.GetConnection(connectionNameEnum); return(sqlCommand); }
/// <summary> /// Makes the SQL command. /// </summary> /// <param name="connectionNameEnum">The connection name enum.</param> /// <param name="storeProcureName">Name of the store procure.</param> /// <param name="parameters">The parameters.</param> /// <param name="connectionTimeOut"></param> /// <returns></returns> internal SqlCommand MakeSqlCommand(ConnectionNameEnum connectionNameEnum, string storeProcureName, List <SqlParameter> parameters, int connectionTimeOut) { var sqlCommand = new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = storeProcureName }; sqlCommand.Parameters.AddRange(parameters.ToArray()); //foreach (var r in parameters) //{ // sqlCommand.Parameters.Add(new SqlParameter(r.Key, r.Value)); //} sqlCommand.Connection = new ConnectionManager().GetConnection(connectionNameEnum); sqlCommand.CommandTimeout = connectionTimeOut; return(sqlCommand); }
/// <summary> /// Runs the object list returned sp. /// </summary> /// <typeparam name="T">The model which sp result will be converted to it then they will be converted to object</typeparam> /// <param name="connectionNameEnum">The connection name enum.</param> /// <param name="storeProcureName">Name of the store procure.</param> /// <returns> /// The list of object. object is parent of T type /// </returns> /// <exception>SqlSPException ex /// <cref>SqlSPException</cref> /// </exception> public static List <object> RunObjectListReturnedSp <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName) { List <object> list; try { var innerList = RunSp <T>(connectionNameEnum, storeProcureName); list = innerList.Cast <object>().ToList(); } catch (ShonizSqlSpException ex) { Parameters.Clear(); throw; } return(list); }
/// <summary> /// Makes the SQL command. /// </summary> /// <param name="connectionNameEnum">The connection name enum.</param> /// <param name="query"></param> /// <param name="connectionTimeOut"></param> /// <returns></returns> internal static SqlCommand MakeQuerySqlCommand(ConnectionNameEnum connectionNameEnum, string query, int connectionTimeOut, List <SqlParameter> parameters = null) { var sqlCommand = new SqlCommand { CommandText = query, Connection = new ConnectionManager().GetConnection(connectionNameEnum) }; if (parameters != null) { sqlCommand.Parameters.AddRange(parameters.ToArray()); } sqlCommand.CommandTimeout = connectionTimeOut; return(sqlCommand); }
public List <T> RunQuery <T>(ConnectionNameEnum connectionNameEnum, string query) { List <T> list; SqlCommand com; if (connectionNameEnum == ConnectionNameEnum.DirectConnectionString) { com = GeneralMethods.MakeQuerySqlCommand(_ConnectionString, query, ConnectionTimeout, Parameters); } else { com = GeneralMethods.MakeQuerySqlCommand(connectionNameEnum, query, ConnectionTimeout, Parameters); } using (var result = com.ExecuteReader()) { try { if (typeof(T).IsValueType || "".GetType() == typeof(T)) { list = new List <T>(); Parallel.ForEach(SimpleParallelDataReader <T>(result), (data) => { list.Add(data); }); return(list); } list = new GeneralMethods().ToViewModel <T>(result); } catch (DataException) { Parameters.Clear(); throw; } finally { ConnectionManager.ReleaseConnection(com.Connection); } } return(list); }
public DataTable RunSpReturnedDirectTable(ConnectionNameEnum connectionNameEnum, string storeProcureName) { var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value); Parameters.Clear(); var command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout); try { var da = new SqlDataAdapter(command); var dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count > 0) { if (dt.Columns["ErrorCode"] != null) { var sqlSpException = new DataException(); for (var i = 0; i < dt.Rows.Count; i++) { sqlSpException.Add((int)dt.Rows[i]["ErrorCode"], dt.Rows[i]["ErrorMessage"].ToString()); } Parameters.Clear(); throw sqlSpException; } } return(dt); } catch { return(new DataTable()); } finally { ConnectionManager.ReleaseConnection(command.Connection); Parameters.Clear(); } }
/// <summary> /// Runs the sp returned table. /// </summary> /// <param name="connectionNameEnum">The connection name enum.</param> /// <param name="storeProcureName">Name of the store procure.</param> /// <returns></returns> public DataTable RunSpReturnedTable(ConnectionNameEnum connectionNameEnum, string storeProcureName) { var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value); Parameters.Clear(); var command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout); try { var dr = command.ExecuteReader(); var dt = new DataTable(); dt.Load(dr); if (dt.Rows.Count > 0) { if (dt.Columns.Contains("ErrorCode")) { var sqlSpException = new DataException(); for (var i = 0; i < dt.Rows.Count; i++) { sqlSpException.Add((int)dt.Rows[i]["ErrorCode"], dt.Rows[i]["ErrorMessage"].ToString()); } ConnectionManager.ReleaseConnection(command.Connection); Parameters.Clear(); throw sqlSpException; } return(dt); } } finally { ConnectionManager.ReleaseConnection(command.Connection); Parameters.Clear(); } return(null); }
/// <summary> /// Runs a store procedure. /// </summary> /// <typeparam name="T">The model which sp result will be converted to it</typeparam> /// <param name="connectionNameEnum">The connection name enum.</param> /// <param name="storeProcureName">Name of the store procure.</param> /// <returns>The list of T object type</returns> /// <exception> /// <cref>SqlSpException ex</cref> /// </exception> public static List <T> RunSp <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName) { List <T> list; var com = SqlGeneralMethods.MakeSqlCommand(connectionNameEnum, storeProcureName, Parameters); using (var result = com.ExecuteReader()) { try { list = SqlGeneralMethods.ToViewModel <T>(result); } catch (ShonizSqlSpException ex) { Parameters.Clear(); throw; } finally { ConnectionManager.ReleaseConnection(com.Connection); Parameters.Clear(); } } return(list); }
public async Task <object> RunSpAsync(ConnectionNameEnum connectionNameEnum, string storeProcureName) { var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value); Parameters.Clear(); object returnedObject = null; SqlCommand command; if (connectionNameEnum == ConnectionNameEnum.DirectConnectionString) { command = new GeneralMethods().MakeSqlCommand(_ConnectionString, storeProcureName, param, ConnectionTimeout); } else { command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout); } try { if (param.Any(p => p.Direction == ParameterDirection.Output)) { command.ExecuteNonQuery(); returnedObject = param.First(p => p.Direction == ParameterDirection.Output).Value; } else { var dr = await command.ExecuteReaderAsync(); if (dr.Read()) { var schemaTable = dr.GetSchemaTable(); if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0) { var sqlSpException = new DataException(); //sqlSpException.Add((int)dr["ErrorCode"], dr["ErrorMessage"].ToString()); sqlSpException.Add(int.Parse(dr["ErrorCode"].ToString()), dr["ErrorMessage"].ToString()); while (dr.Read()) { sqlSpException.Add(int.Parse(dr["ErrorCode"].ToString()), dr["ErrorMessage"].ToString()); } ConnectionManager.ReleaseConnection(command.Connection); Parameters.Clear(); throw sqlSpException; } returnedObject = dr[0]; } } } catch (Exception ex) { var exp = new DataException(); exp.Data.Add("Error", ex); throw exp; } finally { ConnectionManager.ReleaseConnection(command.Connection); } return(returnedObject); }