MIS系统,就是管理信息系统,是企业管理思想的体现。一个MIS系统做得好不好,不仅在于IT部门的水平有多高,更在于一个企业的管理先不先进,规不规范,企业的高层对MIS系统得认识有多深。
当然,有一个好的CIO,一个好的系统分析员,一个好的架构师,一个好的数据设计师,3~5个训练有素,基本功过硬的程序员(至少要能熟练运用数组,链表,队列,堆栈,要知道递规,知道怎样把递规算法改为非递规算法,知道树,二叉查找树,知道穷举法,贪婪法等),再加上一个美工,那是再好不过了。可惜,大部分的企业没有这些人,或者这些人不全(至少我知道的企业如此,软件公司除外),都是一个人顶几个人用。像我,一个人既是系统分析员(直接和用户打交道,搞清楚他们的需求),又是架构师(架构不好,那以后的修改可是非常痛苦的,不要认为用户的要求的是不变,有时候,一个星期,他就要变3次),又是做数据设计(搞MIS系统,如果不熟悉SQL,那可说不过去),又是编码(分内之事),又作界面设计(虽然难看,但是没人,你就得抗),系统测试(你写的程序你不测试,谁测试?),有时还得给人装装机,换换硒鼓,调调IP。所以工作了好几年,但水平一直提不高,也就是弄弄数据库,见几个表,然后打开Delphi,新建一个表单,拉几个比如DbEdit之类的东东,在设置一下DataSource,放几个按钮,写几行Insert,Edit,Post之类的代码。说是高级打字员,一点也不为过。
就这样敲了几年的Begin,End,Insert,Edit,Post,Select,update,delete,发现了一个规律,就是我每天辛辛苦苦敲的这些东东,其实完全可以敲一次就可以了,其他地方拉过就是了,不就是对数据的增,删,改么?哪管你审核,核准,还不就是对数据中一个字段的修改?什么权限不权限,还不就是设定一下,让你看不看得到某个字段的内容?这样一想,居然觉得自己以前好傻。
所有的这些,最终都是通过SQL语句实现的,整个过程可以这样描述。
首先,用户开始使用系统,于是我根据他/她的用户名select field1,field2,... from table where .... order by ....,然后用户开始新增或修改数据,新增时,我 insert into table(field1,field2,field3....) values(value1,value2,value3...),修改时,我update table set field1=value1,field2=value2...where ....删除时,我delete from table where ....
再分析,发现这些select,update,delete们,也有共同的特点,就是都是由动作,对象,条件组成,比如,select * from table where conditon1 order by sequence,update table set field1=value where condition,delete from table where condition。
至于相关的业务流程,无非就在Insert,update,delete之前作判断,是否exists某种condition,如果存在,就再做相应的insert,update或delete,或者让这些Insert,update,delete动作不被执行。
如果将这些功能封装为一个或几个类,其他地方只要调用这个类或者几个类,那不就省事多了?说干就干,经过两天的冥思苦想,基于SQL Server 2005,C#2.0,写了下面的几行代码,我把它做为一个文件,DBWR.cs。
//
//Privileges.cs
//
namespace EwEAI.Business.Interfaces.IT
{
//
//权限定义
//
public enum PrivilegeType
{
PrivilegeNone,
GrantInsert = 1,
DenyInsert = 2,
GrantUpdate = 11,
DenyUpdate = 12,
GrantDelete = 21,
DenyDelete = 22,
GrantRun = 31,
DenyRun = 32,
GrantPrint = 41,
DenyPrint = 42,
GrantSelect = 91,
DenySelect = 92
}
}
//
//DBWR.cs
//
//#define Debug
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using EwEAI.Business.Interfaces.IT;
namespace EwEAI.Data
{
/// <summary>
/// DBWR:数据读写类
/// </summary>
public class DBWR
{
#region 构造函数
private DBWR()
{
InitSetting();
_ConnectionString = GetDbCononectionString();
}
private void InitSetting()
{
DbSetting.ReadXml(@"../settings/DbSettings.xml");
}
private DataSet DbSetting = new DataSet("DbSetting");
private string GetDbCononectionString()
{
DataTable table = DbSetting.Tables["DataBase"];
table.DefaultView.Sort = "Name ASC";
int index=table.DefaultView.Find("DBConStr");
if (index == -1)
return null;
return table.DefaultView[index]["Value"].ToString();
}
/// <summary>
/// 数据读写模块的唯一实例
/// </summary>
public static DBWR Instance
{
get
{
if (_instance == null)
_instance = new DBWR();
return _instance;
}
}
private static DBWR _instance = null;
#endregion
#region 数据库联接
/// <summary>
/// 数据库连接字符串
/// </summary>
public string ConnectionString
{
get
{
#if Debug
return "Data Source=EW-CLIENT;Initial Catalog=EwEAI;User Id=sa;password=yahong;Pooling=true;Max Pool Size=8;Min Pool Size=3";
#else
// return System.Configuration.ConfigurationManager.AppSettings["DBConStr"];
//return System.Configuration.ConfigurationSettings.AppSettings["DBConStr"];
return _ConnectionString;
#endif
}
}
private string _ConnectionString = null;
/// <summary>
/// 数据库连接
/// </summary>
public SqlConnection Connection
{
get
{
return new SqlConnection(ConnectionString);
}
}
#endregion
#region 数据读写
/// <summary>
/// 构建某个表的选择列表
/// </summary>
/// <param name="User"></param>
/// <param name="tableName"></param>
/// <param name="Where"></param>
/// <param name="Order"></param>
/// <param name="ColList"></param>
/// <returns></returns>
///
public string BuildSelectSQL(string User, string tableName, string Where, string Order,
string[] ColList/*null返回所有字段*/)
{
StringBuilder Result = new StringBuilder("");
//表判定
string TableSQL = "select 1 from TablePrivileges where tableName='" + tableName + "'"
+ " and UserName in(select UserName from UserTree('" + User + "',1))"
+ " and SelectPrivilege=" + ((int)PrivilegeType.DenySelect).ToString();
object isDeny = this.GetSingleValue(TableSQL);
if (isDeny != null)
return null;
DataTable ColTable = GetFieldList(tableName, User);
if (ColTable == null)
return null;
if (ColTable.Rows.Count == 0)
return null;
//字段判定
string DenyString = "select FieldName from FieldPrivileges where tableName='" + tableName + "'"
+ " and UserName in(select UserName from UserTree('" + User + "',1))"
+ " and SelectPrivilege=" + ((int)PrivilegeType.DenySelect).ToString();
DataTable DenyFieldsTable = this.ReadData(DenyString);
Hashtable DenyFields = new Hashtable(30);
if (DenyFieldsTable != null)
{
foreach (DataRow dr in DenyFieldsTable.Rows)
DenyFields.Add(dr[0].ToString(), dr[0].ToString());
DenyFieldsTable.Dispose();
}
Result.Append(" select ");
int FieldCount = 0;
for (int i = 0; i < ColTable.Rows.Count; i++)
{
string FieldName = ColTable.Rows[i][0].ToString();
if (DenyFields.Contains(FieldName))
continue;
//如果只返回部分字段
if (ColList != null)
{
int j = 0;
for (; j < ColList.Length; j++)
{
if (FieldName == ColList[j])
break;
}
if (j == ColList.Length)
continue;
}
Result.Append(FieldName);
FieldCount++;
if (ColList != null)
if (FieldCount == ColList.Length)
break;
if (i < ColTable.Rows.Count - 1)
Result.Append(",");
}
Result.Append(" from ");
Result.Append(tableName);
Result.Append(" ");
Result.Append(Where);
Result.Append(" ");
if (Order != null && Order != "")
{
Result.Append(" Order by ");
Result.Append(Order);
}
ColTable.Dispose();
return Result.ToString();
}
/// <summary>
/// 提取某个表的可以选择字段
/// </summary>
/// <param name="tableName"></param>
/// <param name="User"></param>
/// <returns></returns>
private DataTable GetFieldList(string tableName, string User)
{
string ASQL = "select FieldName,(select Seq from Fields where tableName='"
+ tableName + "' and FieldName=O.FieldName)Seq from FieldPrivileges O"
+ " where UserName in (select UserName from UserTree('" + User + "',1))"
+ " and TableName='" + tableName + "'"
+ " and FieldName in(select FieldName from Fields where IsUserDefined=0)"
+ " order by Seq";
return this.ReadData(ASQL);
}
public void ExecSQL(string SQL)
{
SqlConnection con = Connection;
try
{
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
finally
{
con.Close();
con.Dispose();
con = null;
}
}
public DataTable ReadData(string SQL)
{
SqlConnection con = Connection;
try
{
DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(SQL, con);
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
adapter.Dispose();
return table;
}
finally
{
con.Dispose();
}
}
public DataTable ReadData(string SQL,ref DataTable table)
{
SqlConnection con = Connection;
try
{
SqlDataAdapter adapter = new SqlDataAdapter(SQL, con);
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
adapter.Dispose();
return table;
}
finally
{
con.Dispose();
}
}
public DataTable GetStruct(string SQL)
{
SqlConnection con = Connection;
try
{
DataTable Result = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(SQL, con);
adapter.FillSchema(Result, SchemaType.Source);
adapter.Dispose();
return Result;
}
finally
{
con.Dispose();
}
}
public object GetSingleValue(string SQL)
{
SqlConnection con = Connection;
try
{
con.Open();
SqlCommand cmd = new SqlCommand(SQL, con);
object Result = cmd.ExecuteScalar();
cmd.Dispose();
if (Result is DBNull)
return null;
return Result;
}
finally
{
con.Dispose();
}
}
public string Update(string ASQL, DataTable table)
{
string Result = "";
SqlConnection con = Connection;
SqlTransaction action = null;
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(ASQL, con))
{
using (SqlCommandBuilder cb = new SqlCommandBuilder(adapter))
{
con.Open();
cb.RefreshSchema();
adapter.UpdateCommand = cb.GetUpdateCommand();
adapter.DeleteCommand = cb.GetDeleteCommand();
adapter.InsertCommand = cb.GetInsertCommand();
action = con.BeginTransaction();
adapter.UpdateCommand.Transaction = action;
adapter.DeleteCommand.Transaction = action;
adapter.InsertCommand.Transaction = action;
adapter.Update(table);
action.Commit();
}
}
}
catch (Exception e)
{
Result = e.Message;
action.Rollback();
}
finally
{
con.Dispose();
action.Dispose();
action = null;
}
return Result;
}
public string Update(string ASQL, DataRow row)
{
string Result = null;
SqlConnection con = Connection;
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(ASQL, con))
{
using (SqlCommandBuilder cb = new SqlCommandBuilder(adapter))
{
adapter.Update(new DataRow[] { row });
}
}
}
catch (Exception e)
{
Result = e.Message;
}
finally
{
con.Dispose();
}
return Result;
}
private Hashtable GetAdapter(Hashtable SQLS,SqlConnection con)
{
Hashtable Result = new Hashtable(SQLS.Count);
foreach (string SQL in SQLS.Values)
{
SqlDataAdapter adapter = new SqlDataAdapter(SQL, con);
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
adapter.UpdateCommand = cb.GetUpdateCommand();
adapter.DeleteCommand = cb.GetDeleteCommand();
adapter.InsertCommand = cb.GetInsertCommand();
Result.Add(SQL, adapter);
}
return Result;
}
public string Update(DataSet businessData,Hashtable SQLS)
{
SqlConnection con = Connection;
SqlTransaction action = null;
Hashtable Adapters = new Hashtable();
try
{
Adapters=GetAdapter(SQLS,con);
con.Open();
action = con.BeginTransaction();
foreach (DataTable table in businessData.Tables)
{
try
{
if (table.GetChanges() == null)
continue;
string SQL = SQLS[table.TableName].ToString();
SqlDataAdapter adapter = (SqlDataAdapter)Adapters[SQL];
adapter.InsertCommand.Transaction = action;
adapter.UpdateCommand.Transaction = action;
adapter.DeleteCommand.Transaction = action;
adapter.Update(table.GetChanges());
}
catch (Exception cex)
{
if (!(cex is DBConcurrencyException))
{
throw cex;
}
}
}
action.Commit();
}
catch (Exception e)
{
action.Rollback();
return e.Message;
}
finally
{
foreach (SqlDataAdapter adater in Adapters.Values)
adater.Dispose();
Adapters.Clear();
con.Dispose();
action.Dispose();
action = null;
}
return null;
}
#endregion
}
}