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