public static List <T> DataSource(string query)
{
var returnList = new List <T>();
var connection = new CommonConnection();
try
{
DataTable dataTable = connection.GetDataTable(query);
var data = (List <T>)ListConversion.ConvertTo <T>(dataTable);
if (data != null)
{
return(data);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(returnList);
}
public static bool InsertTable(DataTable dtTable, string destinationTableName)
{
bool rv = false;
try
{
var conn = new CommonConnection();
using (var sqlConnection = new SqlConnection(conn.ConnectionString))
{
sqlConnection.Open();
var bulkCopy = new SqlBulkCopy(sqlConnection,
SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction, null);
bulkCopy.DestinationTableName = destinationTableName;
bulkCopy.WriteToServer(dtTable);
sqlConnection.Close();
rv = true;
}
}
catch (Exception)
{
rv = false;
}
return(rv);
}
public static dynamic DataSource(AutoCompOptions options, string query, string orderBy)
{
var connection = new CommonConnection();
try
{
query = query.Replace(';', ' ');
string sqlQuery = GridQueryBuilder <T> .Query(options, query, orderBy, "");
DataTable dataTable = connection.GetDataTable(sqlQuery);
var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable);
return(dataList);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
}
}
private static int GetVoucherLastNo(string prifix, DateTime vDate)
{
string sql = string.Format(@"Select isnull(MAX(Number),0)Number from (
Select VoucharNo, substring(VoucharNo,1,2)Prefix,
substring(VoucharNo,4,6)MonthYear,
Convert(int,substring(VoucharNo,11,6))Number ,
substring(VoucharNo,11,6)IncNumber,
convert(date,substring(VoucharNo,4,4)+'-'+substring(VoucharNo,8,2)+'-01')vDate
from VoucharMaster) T where vDate='{1}'
and Prefix='{0}' ", prifix, vDate.ToString("yyyy-MM-01"));
var conn = new CommonConnection();
var lNo = conn.GetScaler(sql);
conn.Close();
return(lNo);
}
public static GridEntity <T> GenericDataSource(GridOptions options, string query, string orderBy,
string condition)
{
var _connection = new CommonConnection();
StringBuilder gridQuery;
StringBuilder totalQuery;
GetGridPagingQuery(options, query, orderBy, condition, out gridQuery, out totalQuery,
_connection.DatabaseType);
DataTable dataTable = _connection.GetDataTable(gridQuery.ToString());
int totalCount = _connection.GetScaler(totalQuery.ToString());
var dataList = (List <T>)GenericListGenerator.GetList <T>(dataTable);
var result = new GridResult <T>().Data(dataList, totalCount);
return(result);
}
public static DataTable DataTable(string query)
{
var connection = new CommonConnection();
try
{
DataTable dataTable = connection.GetDataTable(query);
return(dataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static DataSet GetDataSet(GridOptions options, string query, string orderBy)
{
//string sql = "SELECT * FROM " + tableName;
DataSet gridDataSet = new DataSet();
var _connection = new CommonConnection();
string condition = "";
try
{
query = query.Replace(';', ' ');
string sqlQuery = options != null
? GridQueryBuilder <T> .Query(options, query, orderBy, condition)
: query;
if (!string.IsNullOrEmpty(condition))
{
condition = " WHERE " + condition;
}
var condition1 = options != null ? GridQueryBuilder <T> .FilterCondition(options.filter) : "";
if (!string.IsNullOrEmpty(condition1))
{
if (!string.IsNullOrEmpty(condition))
{
condition += " And " + condition1;
}
else
{
condition = " WHERE " + condition1;
}
}
DataTable dataTable = _connection.GetDataTable(sqlQuery);
gridDataSet.Tables.Add(dataTable);
String sqlCount = "";
if (_connection.DatabaseType == DatabaseType.SQL)
{
sqlCount = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
}
else if (_connection.DatabaseType == DatabaseType.Oracle)
{
sqlCount = "SELECT COUNT(*) FROM (" + query + " )" + condition;
}
int totalCount = _connection.GetScaler(sqlCount);
DataTable totalCountDt = new DataTable("TotalCount");
DataColumn col = new DataColumn("totalCount");
col.DataType = Type.GetType("System.Int32");
totalCountDt.Columns.Add(col);
DataRow dr = totalCountDt.NewRow();
dr["totalCount"] = totalCount;
totalCountDt.Rows.Add(dr);
//var dataList = (List<T>)ListConversion.ConvertTo<T>(dataTable);
//var result = new GridResult<T>().Data(dataList, totalCount);
gridDataSet.Tables.Add(totalCountDt);
return(gridDataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_connection.Close();
}
}
public static GridEntity <T> DataSource(GridOptions options, string query, string orderBy, string condition)
{
var _connection = new CommonConnection();
try
{
query = query.Replace(';', ' ');
string orderby = "";
string sqlQuery = query;
if (options != null)
{
if (options.pageSize > 0)
{
sqlQuery = GridQueryBuilder <T> .Query(options, query, orderBy, condition);
}
else
{
if (orderBy != "")
{
if (orderBy.ToLower().Contains("asc") || orderBy.ToLower().Contains("desc"))
{
orderby = string.Format(" order by {0}", orderBy);
}
else
{
orderby = string.Format(" order by {0} asc ", orderBy);
}
}
}
}
else
{
if (orderBy != "")
{
if (orderBy.ToLower().Contains("asc") || orderBy.ToLower().Contains("desc"))
{
orderby = string.Format(" order by {0}", orderBy);
}
else
{
orderby = string.Format(" order by {0} asc ", orderBy);
}
}
}
if (!string.IsNullOrEmpty(condition))
{
condition = " WHERE " + condition;
}
var condition1 = "";
if (options != null)
{
if (options.filter != null)
{
condition1 = GridQueryBuilder <T> .FilterCondition(options.filter).Trim();
}
}
if (!string.IsNullOrEmpty(condition1))
{
if (!string.IsNullOrEmpty(condition))
{
condition += " And " + condition1;
}
else
{
condition = " WHERE " + condition1;
}
}
sqlQuery = "SELECT * FROM (" + sqlQuery + " ) As tbl " + condition;
DataTable dataTable = _connection.GetDataTable(sqlQuery + orderby);
String sqlCount = "";
if (_connection.DatabaseType == DatabaseType.SQL)
{
sqlCount = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
}
else if (_connection.DatabaseType == DatabaseType.Oracle)
{
sqlCount = "SELECT COUNT(*) FROM (" + query + " )" + condition;
}
int totalCount = _connection.GetScaler(sqlCount);
var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable);
var result = new GridResult <T>().Data(dataList, totalCount);
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_connection.Close();
}
}
public static GridEntity <T> DataSourceWithDateQuary(GridOptions options, string query, string orderBy,
string condition, string withDateQuary)
{
//string sql = "SELECT * FROM " + tableName;
var _connection = new CommonConnection();
try
{
query = query.Replace(';', ' ');
string sqlQuery = options != null
? GridQueryBuilder <T> .Query(options, query, orderBy, condition)
: query;
if (!string.IsNullOrEmpty(condition))
{
condition = " WHERE " + condition;
}
var condition1 = options != null ? GridQueryBuilder <T> .FilterCondition(options.filter) : "";
if (!string.IsNullOrEmpty(condition1))
{
if (!string.IsNullOrEmpty(condition))
{
condition += " And " + condition1;
}
else
{
condition = " WHERE " + condition1;
}
}
if (withDateQuary != "")
{
sqlQuery = withDateQuary + sqlQuery;
}
DataTable dataTable = _connection.GetDataTable(sqlQuery);
String sqlCount = "";
if (_connection.DatabaseType == DatabaseType.SQL)
{
sqlCount = withDateQuary + " SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
}
else if (_connection.DatabaseType == DatabaseType.Oracle)
{
sqlCount = withDateQuary + " SELECT COUNT(*) FROM (" + query + " )" + condition;
}
int totalCount = _connection.GetScaler(sqlCount);
var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable);
var result = new GridResult <T>().Data(dataList, totalCount);
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_connection.Close();
}
}
public string GetVirtualId(string prefix, string suffix, bool useSeperator, CommonConnection connection, string enitityName, int transectionResetType, DateTime vDate)
{
var idPolicy = GetIdPolicy(connection, enitityName);
string idCode = "";
if (prefix == "")
{
prefix = idPolicy.Prefix.Trim();
}
if (suffix == "")
{
suffix = idPolicy.Suffix == null ? "" : idPolicy.Suffix.Trim();
}
if (idPolicy.LastNumber < 0)
{
idPolicy.LastNumber = 0;
}
if (transectionResetType == 1)
{
if (idPolicy.YearName == vDate.Year)
{
//idPolicy.LastNumber = 0;
}
else
{
idPolicy.LastNumber = 0;
}
}
else if (transectionResetType == 2)
{
if (idPolicy.YearName == vDate.Year && idPolicy.MonthName == vDate.Month)
{
//idPolicy.LastNumber = 0;
}
else
{
idPolicy.LastNumber = 0;
}
}
else
{
if (idPolicy.YearName == vDate.Year && idPolicy.MonthName == vDate.Month &&
idPolicy.DateName == vDate.Day)
{
//idPolicy.LastNumber = 0;
}
else
{
idPolicy.LastNumber = 0;
}
}
string newNumber = GenerateVirtualIDNumber(idPolicy.StartNumber, idPolicy.NumberDigit, idPolicy.LastNumber, enitityName);
if (useSeperator == false)
{
idCode = prefix + newNumber + suffix;
}
else
{
idCode = prefix + "-" + newNumber + "-" + suffix;
}
return(idCode.Trim());
}
private IDPolicy GetIdPolicy(CommonConnection connection, string enitityName)
{
string quary = string.Format("Select * from IDPolicy where EntityName = '{0}'", enitityName);
return(connection.Data <IDPolicy>(quary).FirstOrDefault());
}
public string GenerateEntityId(string prefix, string suffix, bool useSeperator, CommonConnection connection, string enitityName, int transectionResetType, DateTime vDate = new DateTime())
{
if (vDate == DateTime.MinValue)
{
vDate = DateTime.Now;
}
//transectionResetType = 1 then Yearly, 2 then Monthly, 3 then Daily
string idCode = "";
var idPolicy = GetIdPolicy(connection, enitityName);
if (prefix == "")
{
prefix = idPolicy.Prefix.Trim();
}
if (suffix == "")
{
suffix = idPolicy.Suffix == null ? "" : idPolicy.Suffix.Trim();
}
if (idPolicy.LastNumber < 0)
{
idPolicy.LastNumber = 0;
}
if (transectionResetType == 1)
{
if (idPolicy.YearName == vDate.Year)
{
//idPolicy.LastNumber = 0;
}
else
{
idPolicy.LastNumber = 0;
}
}
else if (transectionResetType == 2)
{
if (idPolicy.YearName == vDate.Year && idPolicy.MonthName == vDate.Month)
{
//idPolicy.LastNumber = 0;
}
else
{
idPolicy.LastNumber = 0;
}
}
else
{
if (idPolicy.YearName == vDate.Year && idPolicy.MonthName == vDate.Month &&
idPolicy.DateName == vDate.Day)
{
//idPolicy.LastNumber = 0;
}
else
{
idPolicy.LastNumber = 0;
}
}
string newNumber = GenerateIDNumber(idPolicy.StartNumber, idPolicy.NumberDigit, idPolicy.LastNumber, enitityName, connection, vDate);
if (useSeperator == false)
{
idCode = prefix + newNumber + suffix;
}
else
{
idCode = prefix + "-" + newNumber + "-" + suffix;
}
return(idCode.Trim());
}
private string GenerateIDNumber(int startNumber, int digit, int lastNumber, string entityName, CommonConnection connection, DateTime vDate)
{
string num = "";
var length = (lastNumber + 1).ToString().Length;
var diff = digit - length;
if (diff < 0)
{
try
{
num = "000001";
startNumber = startNumber + 1;
lastNumber = 1;
string quary = string.Format(UPDATE_IDPOLICY_WITH_STNUMBER, startNumber, lastNumber, vDate.Year, vDate.Month, vDate.Day, entityName);
connection.ExecuteNonQuery(quary);
}
catch
{
connection.RollBack();
}
}
else
{
for (int i = 0; i < diff; i++)
{
num += "0";
}
num = num + (lastNumber + 1);
try
{
lastNumber = lastNumber + 1;
string quary = string.Format(UPDATE_IDPOLICY, lastNumber, vDate.Year, vDate.Month, vDate.Day, entityName);
connection.ExecuteNonQuery(quary);
}
catch
{
connection.RollBack();
}
}
return(num);
}
