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); }