/// <summary> /// 存储过程查询 /// </summary> /// <param name="spName"></param> /// <param name="conn"></param> /// <returns></returns> protected MDataTable Select_SP(string spName, string conn) { MDataTable dt; string WhereStr = GetWhere(); string GroupBy = Query <string>("GroupBy"); string OrderBy = Query <string>("OrderBy"); string ShowGroup = Query <string>("ShowGroup"); string ShowStr = Query <string>("ShowStr"); using (MProc proc = new MProc(spName, conn)) { proc.Set("@PageIndex", PageIndex); proc.Set("@PageSize", PageSize); proc.Set("@ShowGroup", ShowGroup); proc.Set("@ShowStr", ShowStr); proc.Set("@WhereStr", WhereStr); proc.Set("@GroupBy", GroupBy); proc.Set("@OrderBy", OrderBy); proc.SetCustom("ReturnValue", ParaType.ReturnValue);//返回值 dt = proc.ExeMDataTable(); if (proc.ReturnValue > 0) { dt.RecordsAffected = proc.ReturnValue; } } return(dt); }
/// <summary> /// 修改表的列结构 /// </summary> /// <param name="tableName">表名</param> /// <param name="columns">列结构</param> /// <param name="conn">数据库链接</param> /// <returns></returns> public static bool AlterTable(string tableName, MDataColumn columns, string conn) { List <string> sqls = SqlCreateForSchema.AlterTableSql(tableName, columns, conn); if (sqls.Count > 0) { DalType dalType = DalType.None; string database = string.Empty; using (MProc proc = new MProc(null, conn)) { dalType = proc.DalType; database = proc.dalHelper.DataBase; proc.SetAopState(Aop.AopOp.CloseAll); if (proc.DalType == DalType.MsSql) { proc.BeginTransation();//仅对mssql有效。 } foreach (string sql in sqls) { proc.ResetProc(sql); if (proc.ExeNonQuery() == -2) { proc.RollBack(); Log.WriteLogToTxt(proc.DebugInfo); return(false); } } proc.EndTransation(); } RemoveCache(tableName, database, dalType); return(true); } return(false); }
/// <summary> /// 获取人员名称通过人员 ID /// </summary> /// <param name="userId"></param> /// <returns></returns> public static string GetRealNameByUserId(string userId) { using (MProc proc = new MProc(string.Format("SELECT RealName FROM Base_User WHERE UserId='{0}'", userId))) { return(proc.ExeScalar <string>()); } }
public static string GetOrganizeEnCode(string organizeId) { using (MProc proc = new MProc(string.Format("SELECT EnCode FROM Base_Organize WHERE OrganizeId='{0}'", organizeId))) { return(proc.ExeScalar <string>()); } }
public static MDataTable SelectForPage(object procName, NameValueCollection form) { using (MProc proc = new MProc(procName)) { return(proc.SelectForPage(form)); } }
/// <summary> /// 获取已接收通知数量,根据用户 ID /// </summary> /// <param name="userId"></param> /// <returns></returns> public static int GetHadReceiveNoticeCountByUserId(string userId) { using (MProc proc = new MProc(string.Format("SELECT COUNT(Id) FROM WJ_KC_NoticeReceive WHERE UserId='{0}'", userId))) { return(proc.ExeScalar <int>()); } }
/// <summary> /// 获取已发送通知数量,根据单位 ID /// </summary> /// <param name="organizeId"></param> /// <returns></returns> public static int GetHadSendNoticeCountByUnitId(string organizeId) { using (MProc proc = new MProc(string.Format("SELECT COUNT(Id) FROM WJ_KC_NoticeSend WHERE UnitId='{0}'", organizeId))) { return(proc.ExeScalar <int>()); } }
public static MDataTable Select(string sql) { using (MProc proc = new MProc(sql)) { return(proc.ExeMDataTable()); } }
/// <summary> /// 根据学习室摄像头ID获取单位ID /// </summary> /// <param name="cameraId"></param> /// <returns></returns> public static string GetOrganizeIdByCameraId(string cameraId, string studyRoomName) { using (MProc proc = new MProc(string.Format("SELECT UNITID FROM WJ_KC_XXS WHERE ZSXT='{0}' AND MC='{1}'", cameraId, studyRoomName))) { return(proc.ExeScalar <string>()); } }
/// <summary> /// 修改表的列结构 /// </summary> /// <param name="tableName">表名</param> /// <param name="columns">列结构</param> /// <param name="conn">数据库链接</param> /// <returns></returns> public static bool AlterTable(string tableName, MDataColumn columns, string conn) { List<string> sqls = SqlCreateForSchema.AlterTableSql(tableName, columns, conn); if (sqls.Count > 0) { DalType dalType = DalType.None; string database = string.Empty; using (MProc proc = new MProc(null, conn)) { dalType = proc.DalType; database = proc.dalHelper.DataBase; proc.SetAopOff(); if (proc.DalType == DalType.MsSql) { proc.BeginTransation();//仅对mssql有效。 } foreach (string sql in sqls) { proc.ResetProc(sql); if (proc.ExeNonQuery() == -2) { proc.RollBack(); Log.WriteLogToTxt(proc.DebugInfo); return false; } } proc.EndTransation(); } RemoveCache(tableName, database, dalType); return true; } return false; }
public static void ExeNonQuery(object procName, params object[] fieldValues) { using (MProc proc = new MProc(procName)) { proc.ExeNonQuery(fieldValues); } }
public static MDataTable Select(object procName, params object[] fieldValues) { using (MProc proc = new MProc(procName)) { return(proc.Select(fieldValues)); } }
public static string Get() { var template = "insert into Test(a,b,c,d,e) values({0},'{1}','{2}','{3}','{4}')"; var sqls = new List <string>(); sqls.Add(template.Format((object)"null", "2", "2", "3", "4")); sqls.Add(template.Format((object)"0", "1", "2", "3", "4")); sqls.Add(template.Format((object)"0", "1", "2", "3", "4")); var errors = new List <string>(); using (var proc = new MProc("select 1")) { foreach (var sql in sqls) { try { proc.ResetProc(sql); proc.ExeNonQuery(); } catch (Exception e) { errors.Add(e.Message); } } return(errors.ToArray().Join(" aaaaaa ")); } return(null); }
/// <summary> /// 修改表的列结构 /// </summary> /// <param name="tableName">表名</param> /// <param name="columns">列结构</param> /// <param name="conn">数据库链接</param> /// <returns></returns> public static bool AlterTable(string tableName, MDataColumn columns, string conn) { List<string> sqls = SqlCreateForSchema.AlterTableSql(tableName, columns, conn); if (sqls.Count > 0) { using (MProc proc = new MProc(null, conn)) { proc.SetAopOff(); if (proc.DalType == DalType.MsSql) { proc.BeginTransation();//仅对mssql有效。 } foreach (string sql in sqls) { proc.ResetProc(sql); if (proc.ExeNonQuery() == -2) { proc.RollBack(); Log.WriteLogToTxt(proc.DebugInfo); return false; } } proc.EndTransation(); } //清缓存 string key = GetSchemaCacheKey(tableName); Cache.CacheManage.LocalInstance.Remove(key); return true; } return false; }
/// <summary> /// 查看记录是否存在 /// 结果为 1:记录存在;结果为 0:记录不存在。 /// </summary> /// <returns></returns> public static int IsExist(string tableName, string where) { using (MProc proc = new MProc(string.Format("select isnull((select top(1) 1 from {0} where {1}), 0)", tableName, where))) { return(proc.ExeScalar <int>()); } }
/// <summary> /// 获取人员职务名称通过人员 ID /// </summary> /// <param name="userId"></param> /// <returns></returns> public static string GetPostNameByUserId(string userId) { using (MProc proc = new MProc(string.Format("SELECT ZW FROM WJ_MEMBER WHERE ID = '{0}'", userId))) { return(proc.ExeScalar <string>()); } }
public static string GetTeachScheduleTaskId(DateTime teachScheduleDate, string unitId) { using (MProc proc = new MProc(string.Format("SELECT ID FROM WJ_KC_PKRWB WHERE PKRQ='{0}' AND UNITID='{1}'", teachScheduleDate, unitId))) { return(proc.ExeScalar <string>()); } }
/// <summary> /// 获取单位编码根据单位 ID /// </summary> /// <param name="organizeId"></param> /// <returns></returns> public static string GetOrganizeCodeById(string organizeId) { using (MProc proc = new MProc(string.Format("SELECT UNITBM FROM WJ_Unit WHERE ID='{0}'", organizeId))) { return(proc.ExeScalar <string>()); } }
/// <summary> /// 获取附件类型名称 /// </summary> /// <param name="typeId"></param> /// <returns></returns> public static string GetAttachmentTypeNameById(string typeId) { using (MProc proc = new MProc(string.Format("SELECT Name FROM WJ_KC_FJLX WHERE ID='{0}'", typeId))) { return(proc.ExeScalar <string>()); } }
public static string ExeOnlyOutput(this MProc proc, object outputParmName, params object[] fieldValues) { proc.SetCustom(outputParmName, ParaType.OutPut); proc.Set(fieldValues); proc.ExeNonQuery(); return(proc.OutPutValue.ToString()); }
public static string ExeOnlyOutput(object procName, object outputParmName, params object[] fieldValues) { using (MProc proc = new MProc(procName)) { return(proc.ExeOnlyOutput(outputParmName, fieldValues)); } }
static void Exe1() { string sql = "select count(*) from users"; using (MProc proc = new MProc(sql)) { proc.ExeScalar <string>(); } }
public static MDataTable Select <T>(this MProc proc, object outputParmName, out T output, params object[] fieldValues) { proc.SetCustom(outputParmName, ParaType.OutPut); proc.Set(fieldValues); var dt = proc.ExeMDataTable(); output = (T)proc.OutPutValue; return(dt); }
public static MDataTable SelectForPage(this MProc proc, NameValueCollection form) { proc.SetCustom("total", ParaType.ReturnValue); proc.Set(form); var dt = proc.ExeMDataTable(); var total = (int)proc.ReturnValue; dt.RecordsAffected = total; return(dt); }
public static MDataTable SelectForPage(this MProc proc, params object[] fieldValues) { proc.SetCustom("total", ParaType.ReturnValue); proc.Set(fieldValues); var dt = proc.ExeMDataTable(); var total = (int)proc.ReturnValue; dt.RecordsAffected = total; return(dt); }
private void button1_Click(object sender, EventArgs e) { using (MProc proc = new MProc("OpenDoor")) { proc.Set("@CardNo", "111"); proc.SetCustom("result", ParaType.OutPut); //如果有output值 proc.ExeNonQuery(); //执行语句 string result = proc.OutPutValue.ToString(); //如果只有一个值 MessageBox.Show(result); } }
/// <summary> /// 检查目录下是否存在同名数据库,如不存在则创建,如存在则清空之前的数据 /// </summary> /// <param name="name"></param> /// <param name="path"></param> /// <returns></returns> public static bool CheckDB(string name, string path) { SetDBPath(name, path); string sql = "DROP TABLE IF EXISTS \"dt\";CREATE TABLE \"dt\" (\"id\" TEXT NOT NULL,\"z\" INTEGER DEFAULT 0,\"x\" INTEGER DEFAULT 0,\"y\" INTEGER DEFAULT 0,\"d\" INTEGER DEFAULT 0,PRIMARY KEY (\"id\")); "; using (MProc proc = new MProc(sql, ConnectStr)) { int result = proc.ExeNonQuery(); return(result >= 0); } }
/// <summary> /// 库中未处理的数据的数量 /// </summary> /// <param name="fm"></param> /// <param name="dataflag"></param> /// <returns></returns> public static int isComplate(Form1 fm, int dataflag) { string sql = "SELECT count(*) FROM dt WHERE d=" + dataflag; using (MProc proc = new MProc(sql, ConnectStr)) { int result = proc.ExeScalar <int>(); if (fm != null) { fm.showLable4(result.ToString()); } return(result); } }
public static void Set(this MProc proc, params object[] fieldValues) { if (fieldValues == null || fieldValues.Length == 0) { return; } for (int i = 0; i < fieldValues.Length; i += 2) { if (fieldValues[i + 1].IsNullOrEmpty()) { continue; } proc.Set(fieldValues[i], fieldValues[i + 1]); } }
public static void Set(this MProc proc, NameValueCollection form) { foreach (var key in form.AllKeys) { var value = form[key]; if (key == "time_rand") { continue; } if (value.IsNotNullEmpty()) { proc.Set(key.Replace("[]", ""), form[key]); } } }
private int CheckPrivilege(string CardNo) { int result; try { using (MProc proc = new MProc("OpenDoor")) { proc.Set("@CardNo", CardNo); proc.SetCustom("result", ParaType.OutPut); //如果有output值 proc.ExeNonQuery(); //执行语句 result = int.Parse(proc.OutPutValue.ToString()); } } catch { result = 100; } return(result); }
/// <summary> /// 执行SQL语句 /// </summary> static void ExeSql() { AppConfig.DB.DefaultConn = "server=CYQ-PC\\SQL2008;database=Test;uid=sa;pwd=123456"; string sql = "select * from users"; using (MProc proc = new MProc(sql)) { proc.BeginTransation();//事务的使用和MAction是一样的 //MDataTable dt = proc.ExeMDataTable(); //OutMsg(dt.Rows.Count); proc.ResetProc("select count(*) from demo_testa"); MDataTable dt2 = proc.ExeMDataTable(); OutMsg(dt2.Rows.Count); proc.ResetProc("select name from users where UserID=@UserID"); proc.Set("UserID", 1); string name = proc.ExeScalar <string>(); OutMsg(name); proc.ResetProc("update users set password=123 where name=@name"); proc.Set("name", name); int result = proc.ExeNonQuery(); OutMsg(result); if (result < 1) { proc.RollBack();//找不到结果,要回滚事务 return; } proc.ResetProc("select * from users;select * from Article");//多语句执行 List <MDataTable> dtList = proc.ExeMDataTableList(); OutMsg(dtList.Count); proc.EndTransation(); } }
/// <summary> /// 移除一张表 /// <param name="conn">数据库链接</param> /// </summary> public static bool DropTable(string tableName, string conn) { using (MProc proc = new MProc(null, conn)) { proc.ResetProc("drop table " + Keyword(tableName, proc.DalType)); int result = proc.ExeNonQuery(); if (result == -2) { return false; } RemoveCache(tableName, proc.DataBase, proc.DalType); } return true; }
/// <summary> /// 为指定的表架构生成SQL(Create Table)语句 /// </summary> public static bool CreateTable(string tableName, MDataColumn columns, string conn) { if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")")) { return false; } bool result = false; switch (GetDalType(conn)) { case DalType.Txt: case DalType.Xml: // string a, b, c; conn = AppConfig.GetConn(conn);// CYQ.Data.DAL.DalCreate.GetConnString(conn, out a, out b, out c); if (conn.ToLower().Contains(";ts=0"))//不写入表架构。 { return true; } else { tableName = Path.GetFileNameWithoutExtension(tableName); string fileName = NoSqlConnection.GetFilePath(conn) + tableName + ".ts"; result = columns.WriteSchema(fileName); } break; default: using (MProc proc = new MProc(null, conn)) { try { proc.dalHelper.IsAllowRecordSql = false; proc.SetAopOff(); proc.ResetProc(GetCreateTableSql(tableName, columns, proc.DalType, proc.DalVersion));//.Replace("\n", string.Empty) result = proc.ExeNonQuery() > -2; //获取扩展说明 string descriptionSql = GetCreateTableDescriptionSql(tableName, columns, proc.DalType).Replace("\r\n", string.Empty).Trim(' ', ';'); if (!string.IsNullOrEmpty(descriptionSql)) { if (proc.DalType == DalType.Oracle) { foreach (string sql in descriptionSql.Split(';')) { proc.ResetProc(sql); if (proc.ExeNonQuery() == -2) { break; } } } else { proc.ResetProc(descriptionSql); proc.ExeNonQuery(); } } } catch (Exception err) { Log.WriteLogToTxt(err); } } break; } return result; }