public static bool Get <T>(T _Object, string query = "", string where_clause = "", Dictionary <string, string> Params = null)
{
List <PropertyInfo> _properties = GetReadableProperties <T>();
query = QueryMaker.SelectQuery <T>(query, where_clause);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
SqlCommand cmd = GetSqlCommandWithParams(query, connection, Params);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
foreach (PropertyInfo pi in _properties)
{
DBReader(_Object, pi, reader);
}
}
}
connection.Close();
return(true);
}
}
public static List <T> GetList <T>(string query = null, string whereClause = null, Dictionary <string, string> Params = null)
{
var results = new List <T>();
var properties = GetReadableProperties <T>();
query = QueryMaker.SelectQuery <T>(query, whereClause);
using (SqlConnection Connection = new SqlConnection(ConnectionString))
{
Connection.Open();
SqlCommand cmd = GetSqlCommandWithParams(query, Connection, Params);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var item = Activator.CreateInstance <T>();
foreach (var property in properties)
{
DBReader(item, property, reader);
}
results.Add(item);
}
}
Connection.Close();
return(results);
}
}
public bool CommitTrans(Dictionary <string, string> Params)
{
Query += QueryMaker.CommitTransQuery();
bool result = ExecuteQuery(Query, Params);
Query = string.Empty;
return(result);
}
public bool CommitTrans()
{
Query += QueryMaker.CommitTransQuery();
bool result = ExecuteQuery(Query);
Query = string.Empty;
return(result);
}
public static PageListObject <T> GetPagedList <T>(string query, Dictionary <string, string> sortAndOrder, int numberOfRecords, int pageNumber, Dictionary <string, string> Params)
{
int offset = numberOfRecords * pageNumber;
var Querries = QueryMaker.GetPagerQueries <T>(query, sortAndOrder, offset, numberOfRecords);
string mainquery = Querries.Query;
string countquery = Querries.CountQuery;
return(PagedResults <T>(numberOfRecords, pageNumber, Params, offset, mainquery, countquery));
}
public static bool ExecuteTransactionQuery(string query, Dictionary <string, string> Params = null)
{
query = $"{QueryMaker.BeginTransQuery()} {query} {QueryMaker.CommitTransQuery()}";
using (SqlConnection Connection = new SqlConnection(ConnectionString))
{
Connection.Open();
SqlCommand cmd = GetSqlCommandWithParams(query, Connection, Params);
int rowseffected = cmd.ExecuteNonQuery();
Connection.Close();
return(true);
}
}
public string InsertAndGetId(string tableName, Dictionary <string, string> dataset)
{
string query = QueryMaker.InsertQueryWithID(tableName, dataset);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
int id = 0;
connection.Open();
SqlCommand cmd = new SqlCommand(query, connection);
id = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return(id.ToString());
}
}
public static bool Update <T>(T _Object, string whereClause, Dictionary <string, string> values, string schemaName = null, string tableName = null)
{
return(ExecuteQuery(QueryMaker.UpdateQuery(_Object, whereClause, schemaName, tableName), values));
}
public static bool Insert(string tableName, Dictionary <string, string> dataset)
{
return(ExecuteQuery(QueryMaker.InsertQuery(tableName, dataset)));
}
public static bool Insert <T>(T _Object, string schemaName = null, string tableName = null)
{
return(ExecuteQuery(QueryMaker.InsertQuery(_Object, schemaName, tableName)));
}
public void BeginTrans()
{
Query = string.Empty;
Query += QueryMaker.BeginTransQuery();
}
public static bool Delete <T>(string whereClause, Dictionary <string, string> values, string schemaName = null, string tableName = null)
{
return(ExecuteQuery(QueryMaker.DeleteQuery <T>(whereClause), values));
}
public static bool Update(string tableName, Dictionary <string, string> dataset, string whereClause)
{
return(ExecuteQuery(QueryMaker.UpdateQuery(tableName, dataset, whereClause)));
}