public static int GetCount()
{
string sql = string.Empty;
int retval = 0;
sql = "select COUNT(*) from QUESTION";
retval = Convert.ToInt32(SQLMs.RunScalar(sql));
return(retval);
}
public static int getCountPaging()
{
string sql = string.Empty;
int retval = 0;
sql = "select COUNT(*) from BLOG";
retval = Convert.ToInt32(SQLMs.RunScalar(sql));
return(retval);
}
public static short GetLastSort()
{
string sql = string.Empty;
short retval = 0;
sql = "SELECT ISNULL(MAX(ID),0) FROM " + Functions.TABLEPREFIX + "QUESTION";
retval = Convert.ToInt16(SQLMs.RunScalar(sql));
return(retval);
}
public static short getCount()
{
string sql = string.Empty;
short retval = 0;
sql = "SELECT ISNULL(COUNT(ID),0) FROM " + Functions.TABLEPREFIX + "BLOG";
retval = Convert.ToInt16(SQLMs.RunScalar(sql));
return(retval);
}
public static List <QUESTION> GetbyPaging(int start, int end)
{
string sql = string.Empty;
sql = @"SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY SORT ) AS RowNum, * FROM QUESTION ) AS RowConstrainedResult WHERE RowNum >= " + start + " AND RowNum <= " + end + " ORDER BY RowNum";
DataSet retval = new DataSet();
retval = SQLMs.RunDataset(sql);
return(retval.ConvertToList <QUESTION>());
}
public static List <QUESTION> GetTop(int topCount)
{
string sql = string.Empty;
sql = @"SELECT TOP " + topCount + " * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY SORT ) AS RowNum, * FROM QUESTION ) AS RowConstrainedResult ORDER BY RowNum";
DataSet retval = new DataSet();
retval = SQLMs.RunDataset(sql);
return(retval.ConvertToList <QUESTION>());
}
public static List <QUESTION> Get8()
{
string sql = string.Empty;
sql = @"select TOP 8 * from QUESTION order by SORT";
Hashtable param = new Hashtable();
DataSet retval = new DataSet();
retval = SQLMs.RunDataset(sql, param);
return(retval.ConvertToList <QUESTION>());
}
public static bool HasImage(string IMAGE)
{
string sql = string.Empty;
bool retval;
sql = "declare @hasMember int = (select COUNT(*) as hasMember from BLOG where IMAGE = @IMAGE) if @hasMember > 0 select 0 as deger else select 1 as deger";
Hashtable param = new Hashtable();
param.Add("IMAGE", IMAGE);
retval = Convert.ToBoolean(SQLMs.RunScalar(sql, param));
return(retval);
}
public static bool HasTitle(string TITLE)
{
string sql = string.Empty;
bool retval;
sql = "declare @hasMember int = (select COUNT(*) as hasMember from DISEASES where TITLE = @TITLE) if @hasMember > 1 select 1 as deger else select 0 as deger";
Hashtable param = new Hashtable();
param.Add("TITLE", TITLE);
retval = Convert.ToBoolean(SQLMs.RunScalar(sql, param));
return(retval);
}
public static void DeleteItem(string tableName, List <WHERE> where)
{
string sql = "DELETE FROM " + TABLEPREFIX + tableName + " WHERE 1=1";
Hashtable param = new Hashtable();
foreach (WHERE w in where)
{
sql += " AND " + w.COLUMNNAME + w.OPERATOR + "@" + w.COLUMNNAME;
param.Add(w.COLUMNNAME, w.VALUE);
}
SQLMs.RunSQL(sql, param);
}
public static List <QUESTION> GetLastCount(int count)
{
string sql = string.Empty;
if (GetCount() >= count)
{
sql = @"select TOP " + count + " * from QUESTION order by DATE DESC";
}
else if (GetCount() > 0)
{
sql = @"select TOP " + 1 + " * from QUESTION order by DATE DESC";
}
else
{
sql = @"select TOP " + 0 + " * from QUESTION order by DATE DESC";
}
Hashtable param = new Hashtable();
DataSet retval = new DataSet();
retval = SQLMs.RunDataset(sql, param);
return(retval.ConvertToList <QUESTION>());
}
public static void InsertEntity <T>(string tableName, T entity)
{
string sql = string.Empty;
Type item = typeof(T);
Hashtable param = new Hashtable();
//var storeItem = DALBase.ctx.MetadataWorkspace.GetItems<EntityType>(DataSpace.OSpace).Where(et => et.FullName == entity.ToString()).FirstOrDefault();
EntityConnection con = new EntityConnection("name=BlogEntities");
MetadataWorkspace vStorageWorkspace = con.GetMetadataWorkspace();
EdmType vStorageEdmType = vStorageWorkspace.GetType(tableName, "BlogModel.Store", DataSpace.SSpace);
EntityType storeItem = vStorageEdmType as EntityType;
sql = "INSERT INTO " + TABLEPREFIX + tableName + "(";
string values = " VALUES(";
bool hasIdentity = false;
string identityColName = string.Empty;
foreach (EdmMember member in storeItem.Members)
{
if (member.TypeUsage.EdmType.BaseType != null)
{// gercek alanlari yapsin, kendi koydugu navigation propertyleri yapmasin diye
PropertyInfo info = item.GetProperty(member.Name);
Facet identity = member.TypeUsage.Facets.Where(p => p.Name == "StoreGeneratedPattern").FirstOrDefault();
if (storeItem.KeyMembers.Where(p => p.Name == member.Name).Count() > 0)
{// key var
if (identity != null && (StoreGeneratedPattern)identity.Value == StoreGeneratedPattern.Identity)
{
hasIdentity = true;
identityColName = member.Name;
}
else
{
// identity degilse genede ekleyecegiz,ornek related product tablosu icin
sql += "[" + member.Name + "],";
values += "@" + member.Name + ",";
param.Add(member.Name, info.GetValue(entity, null));
}
}
else
{
if (identity != null && (StoreGeneratedPattern)identity.Value == StoreGeneratedPattern.Computed)
{// bir sey yapmasin
}
else if (identity != null && (StoreGeneratedPattern)identity.Value == StoreGeneratedPattern.Identity)
{// key kolon haricinde Identity var
hasIdentity = true;
identityColName = member.Name;
}
else
{
sql += "[" + member.Name + "],";
values += "@" + member.Name + ",";
param.Add(member.Name, info.GetValue(entity, null));
}
}
}
}
sql = sql.TrimEnd(',') + ") " + values.TrimEnd(',') + ")";
if (hasIdentity)
{//varsa alip ID ye setlemek lazim, ornek, orderi ekleyince sonrasinda order detail icin kullanilacak
if (DBTYPE == SqlType.MSSql)
{
sql += Environment.NewLine + " SELECT SCOPE_IDENTITY()";
}
else if (DBTYPE == SqlType.MySql)
{
sql += ";" + Environment.NewLine + " SELECT LAST_INSERT_ID(); ";
}
object id = null;
id = SQLMs.RunScalar(sql, param);
PropertyInfo info = item.GetProperty(identityColName);
id = Convert.ChangeType(id, info.PropertyType);
info.SetValue(entity, id, null);
}
else
{
SQLMs.RunSQL(sql, param);
}
}
public static DataSet GetByPropertyValue(string tableName, List <WHERE> where)
{
DataSet retval = new DataSet();
Hashtable param = new Hashtable();
string sql = "SELECT * FROM " + TABLEPREFIX + tableName + " WHERE 1=1";
foreach (WHERE w in where)
{
switch (w.OPERATOR)
{
case "TOP":
{
sql = "SELECT TOP " + w.VALUE + " * FROM " + TABLEPREFIX + tableName + " WHERE 1=1";
break;
}
case "BETWEEN":
{
sql += " AND " + w.COLUMNNAME + " BETWEEN @" + w.COLUMNNAME + "Left AND @" + w.COLUMNNAME + "Right";
param.Add(w.COLUMNNAME + "Left", w.VALUE);
param.Add(w.COLUMNNAME + "Right", w.VALUE2);
break;
}
case "NOT BETWEEN":
{
sql += " AND " + w.COLUMNNAME + " NOT BETWEEN @" + w.COLUMNNAME + "Left AND @" + w.COLUMNNAME + "Right";
param.Add(w.COLUMNNAME + "Left", w.VALUE);
param.Add(w.COLUMNNAME + "Right", w.VALUE2);
break;
}
case "ISNOTNULL":
{
sql += " AND " + w.COLUMNNAME + " IS NOT NULL";
break;
}
case "ISNULL":
{
sql += " AND " + w.COLUMNNAME + " IS NULL";
break;
}
case "OR":
{
if (string.IsNullOrEmpty(w.VALUE2.ToString()))
{
if (!string.IsNullOrEmpty(w.OPERATOR2.ToString()))
{
sql += " AND (" + w.COLUMNNAME + " " + w.OPERATOR2 + " @" + w.COLUMNNAME + "Left " + w.OPERATOR + " " + w.COLUMNNAME + " IS NULL )";
param.Add(w.COLUMNNAME + "Left", w.VALUE);
break;
}
else
{
sql += " AND (" + w.COLUMNNAME + "=@" + w.COLUMNNAME + "Left " + w.OPERATOR + " " + w.COLUMNNAME + " IS NULL )";
param.Add(w.COLUMNNAME + "Left", w.VALUE);
break;
}
}
else
{
sql += " AND (" + w.COLUMNNAME + "=@" + w.COLUMNNAME + "Left " + w.OPERATOR + " " + w.COLUMNNAME + "=@" + w.COLUMNNAME + "Right )";
param.Add(w.COLUMNNAME + "Left", w.VALUE);
param.Add(w.COLUMNNAME + "Right", w.VALUE2);
break;
}
}
default:
{
if (!param.ContainsKey(w.COLUMNNAME))
{
sql += " AND " + w.COLUMNNAME + " " + w.OPERATOR + " @" + w.COLUMNNAME;
param.Add(w.COLUMNNAME, w.VALUE);
}
else
{ // bir kolon ikinci defa gelmisse
string paramName = string.Empty;
for (int i = 0; i < 100; i++)
{
if (!param.ContainsKey(w.COLUMNNAME + i.ToString()))
{
paramName = w.COLUMNNAME + i.ToString();
break;
}
}
sql += " AND " + w.COLUMNNAME + " " + w.OPERATOR + " @" + paramName;
param.Add(paramName, w.VALUE);
}
break;
}
}
}
retval = SQLMs.RunDataset(sql, param);
return(retval);
}