基于ArcGIS10.0和Oracle10g的空间数据管理平台二（C#开发）
上一篇文章把整个系统的框架和功能简单介绍了一下，这个系统的架构就是C/S，数据库采用的是Oracle10g，空间数据库采用的ArcGIS基于Oracle10g的ArcSDE，开发工具用的是VS2008，开发平台是.NET Framework3.5，代码的版本控制工具是SVN。
在讲解整个系统的实现的时候我是按照我开发的功能顺序介绍，其实很多功能模块都是相对独立的，今天就从最简单的登录模块的实现开始。登录模块在两个地方使用到，第一处当然就是第一次进入主界面以前，第二次是进入元数据管理子系统的时候需要用有相应权限的账户登录。界面的设计就不详细介绍了，这个可以通过开发工具直接拖出来，及所谓的所见即所得编辑。
第一步我们需要设计一个数据库的表来专门保存用户信息，主要是用户名、密码和相应权限。
第二步就是设计界面了，必须有登录和取消按钮，这两个按钮的功能就是登录和退出系统的功能。额外还需要的就是在用户输入用户名和密码错误的时候需要有提示信息，通常的做法就是弹出一个提示对话框，但是我在这里并不是这样做的，为了更好的用户体验，我用了一个静态文本标签按钮来提示相应信息，这样用户就不用在去点击确认信息了。
第三步编写登录按钮的响应事件代码，当用户点击的时候执行这个功能，实现代码如下：
//首先判断用户名和密码都不能为空
if (userNameTxt.Text.Trim() == "" || passwordTxt.Text.Trim() == "")
{
errlabel.Text = "用户名和密码不能为空!";
return;
}
SqlHelper sh = new SqlHelper();//用Sql帮助类来实现查询，封装了常用的SQL操作
string sql = string.Empty;
//判断用户类型
if (userType == 1)
{
sql = "select * from jcsjk_users where username='" + userNameTxt.Text + "'" +
" and password='" + passwordTxt.Text + "' and type='1'";
}
else if (userType == 2)
{
sql = "select * from jcsjk_users where username='" + userNameTxt.Text + "'" +
" and password='" + passwordTxt.Text + "' and type='2'";
}
if (sh.GetRecordCount(sql) > 0)
{
LogHelp.writeLog(userNameTxt.Text, "用户登录", "用户成功登陆");
if (userType == 2)
{
FrmMain.metaIsLgoin = true;
FrmMain.metaUsername = userNameTxt.Text;
}
errlabel.Text = "系统正在初始化，请稍等...";
this.Update();
errlabel.Update();
while (!FrmMain.isInitFinish)
{
;
}
this.Close();
}
else
{
LogHelp.writeLog(userNameTxt.Text, "用户登录", "用户登陆失败");
errlabel.Text = "用户名或密码错误";
}
上面这段代码根据用户输入的用户名和密码来判断是否正确的，以及是属于哪种权限的用户。上面的代码用到了SQL的一个帮助类，主要封装了一些常用的SQL操作，这个类定义如下（以后还会经常用到这个类，基本上所有的SQL操作都由这个类完成，除了一些特殊操作，例如事务、必须考参数插入很长字符串等）：
public class SqlHelper
{
protected OracleConnection Connection;
private string connectionString;
public SqlHelper()
{
//connectionString = "Data Source=JCSJK;User Id=dzyj_jcsjk;Password=dzyj_jcsjk";
//connectionString = "Data Source=BRUCEWOO;User Id=sde;Password=wan861620";
connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
Connection = new OracleConnection(connectionString);
}
/// <summary>
/// 带参数的构造函数
/// </summary>
/// <param name="ConnString">数据库联接字符串</param>
public SqlHelper(string ConnString)
{
string connStr;
connStr = System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();
Connection = new OracleConnection(connStr);
}
/// <summary>
/// 打开数据库
/// </summary>
public void OpenConn()
{
if (this.Connection.State != ConnectionState.Open)
{
this.Connection.Open();
}
}
/// <summary>
/// 关闭数据库联接
/// </summary>
public void CloseConn()
{
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
}
#region 执行SQL语句，返回数据到DataSet中
/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回DataSet</returns>
public DataSet ReturnDataSet(string sql, string tableName)
{
DataSet dataSet = new DataSet();
OpenConn();
OracleDataAdapter OraDA = new OracleDataAdapter(sql, Connection);
OraDA.Fill(dataSet, tableName);
return dataSet;
}
#endregion
#region 执行Sql语句,返回带分页功能的dataset
/// <summary>
/// 执行Sql语句,返回带分页功能的dataset
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="PageSize">每页显示记录数</param>
/// <param name="CurrPageIndex"><当前页/param>
/// <param name="DataSetName">返回dataset表名</param>
/// <returns>返回DataSet</returns>
public DataSet ReturnDataSet(string sql,int PageSize,int CurrPageIndex,string DataSetName)
{
DataSet dataSet = new DataSet();
OpenConn();
OracleDataAdapter OraDA = new OracleDataAdapter(sql, Connection);
OraDA.Fill(dataSet, PageSize * (CurrPageIndex - 1), PageSize,DataSetName);
return dataSet;
}
#endregion
#region 执行SQL语句，返回 DataReader,用之前一定要先.read()打开,然后才能读到数据
/// <summary>
/// 执行SQL语句，返回 DataReader,用之前一定要先.read()打开,然后才能读到数据
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回一个OracleDataReader</returns>
public OracleDataReader ReturnDataReader(String sql)
{
OpenConn();
OracleCommand command = new OracleCommand(sql,Connection);
return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
#endregion
#region 执行SQL语句，返回记录总数数
/// <summary>
/// 执行SQL语句，返回记录总数数
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回记录总条数</returns>
public int GetRecordCount(string sql)
{
int recordCount = 0;
OpenConn();
OracleCommand command = new OracleCommand(sql,Connection);
OracleDataReader dataReader = command.ExecuteReader();
while(dataReader.Read())
{
recordCount++;
}
dataReader.Close();
return recordCount;
}
#endregion
#region 取当前序列,条件为seq.nextval或seq.currval
/// <summary>
/// 取当前序列
/// </summary>
/// <param name="seqstr"></param>
/// <returns></returns>
public decimal GetSeq(string seqstr)
{
decimal seqnum = 0;
string sql = "select " + seqstr + " from dual";
OpenConn();
OracleCommand command = new OracleCommand(sql,Connection);
OracleDataReader dataReader = command.ExecuteReader();
if(dataReader.Read())
{
seqnum = decimal.Parse(dataReader[0].ToString());
}
dataReader.Close();
// CloseConn();
return seqnum;
}
#endregion
#region 执行SQL语句,返回所影响的行数
/// <summary>
/// 执行SQL语句,返回所影响的行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteSQL(string sql)
{
int Cmd = 0;
OpenConn();
OracleCommand command = new OracleCommand(sql, Connection);
try
{
Cmd = command.ExecuteNonQuery();
}
catch (OracleException e)
{
CloseConn();
throw e;
}
return Cmd;
}
#endregion
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// ＝＝用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr"＝＝
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
#region 根据表名及哈稀表自动插入数据库 用法：Insert("test",ht)
/// <summary>
/// 用hashTable对数据库进行insert操作,注意此时只能用默认的数据库连接"connstr"＝＝
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="ht">键值对的HashTable(字段名，值)</param>
/// <returns>影响的行数</returns>
public int Insert(string TableName, Hashtable ht)
{
OracleParameter[] Parms = new OracleParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
DataTable dt = GetTabType(TableName);
System.Data.OracleClient.OracleType otype;
int size = 0;
int i = 0;
// 作哈希表循环
while (et.MoveNext())
{
GetoType(et.Key.ToString().ToUpper(), dt, out otype, out size);
OracleParameter op = MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
// 添加SqlParameter对象
Parms[i] = op;
i = i+1;
}
string str_Sql = GetInsertSqlbyHt(TableName, ht); // 获得插入sql语句
int val = ExecuteNonQuery(str_Sql, Parms);
return val;
}
#endregion
#region 根据相关条件对数据库进行更新操作 用法：Update("test","Id=:Id",ht);
public int Update(string TableName, string ht_Where, Hashtable ht)
{
OracleParameter[] Parms = new OracleParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
DataTable dt = GetTabType(TableName);
OracleType otype;
int size = 0;
int i = 0;
// 作哈希表循环
while ( et.MoveNext() )
{
GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
Parms[i] = op; // 添加SqlParameter对象
i = i + 1;
}
string str_Sql = GetUpdateSqlbyHt(TableName, ht_Where, ht); // 获得插入sql语句
int val = ExecuteNonQuery(str_Sql, Parms);
return val;
}
#endregion
#region del操作,注意此处条件个数与hash里参数个数应该一致 用法：Del("test","Id=:Id",ht)
/// <summary>
/// 删除一条记录
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="ht_Where"></param>
/// <param name="ht"></param>
/// <returns></returns>
public int Del(string TableName, string ht_Where, Hashtable ht)
{
OracleParameter[] Parms = new OracleParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
DataTable dt = GetTabType(TableName);
OracleType otype;
int i = 0;
int size = 0;
// 作哈希表循环
while (et.MoveNext())
{
GetoType(et.Key.ToString().ToUpper(), dt, out otype, out size);
OracleParameter op = MakeParam(":"+et.Key.ToString(),et.Value.ToString());
// 添加SqlParameter对象
Parms[i] = op;
i = i + 1;
}
// 获得删除sql语句
string str_Sql = GetDelSqlbyHt(TableName, ht_Where, ht);
int val = ExecuteNonQuery(str_Sql,null);
return val;
}
#endregion
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// ＝＝＝＝＝＝＝＝上面三个操作的内部调用函数＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
#region 根据哈稀表及表名自动生成相应insert语句(参数类型的)
/// <summary>
/// 根据哈稀表及表名自动生成相应insert语句
/// </summary>
/// <param name="TableName">要插入的表名</param>
/// <param name="ht">哈稀表</param>
/// <returns>返回sql语句</returns>
public static string GetInsertSqlbyHt(string TableName, Hashtable ht)
{
string str_Sql = "";
int i = 0;
int ht_Count = ht.Count; // 哈希表个数
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
string before = "";
string behide = "";
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
before = "(" + myEnumerator.Key;
}
else if ( i+1 == ht_Count)
{
before = before+","+myEnumerator.Key+")";
}
else
{
before=before+","+myEnumerator.Key;
}
i = i+1;
}
behide = " Values"+before.Replace(",",",:").Replace("(","(:");
str_Sql = "Insert into " + TableName + before + behide;
return str_Sql;
}
#endregion
#region 根据表名，where条件，哈稀表自动生成更新语句(参数类型的)
public static string GetUpdateSqlbyHt(string Table,string ht_Where,Hashtable ht)
{
string str_Sql = "";
int i = 0;
int ht_Count = ht.Count; // 哈希表个数
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)
{
str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
else
{
if (ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+" ").ToLower())==-1)
{
str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
i=i+1;
}
if (ht_Where==null || ht_Where.Replace(" ","") == "") // 更新时候没有条件
{
str_Sql = "update "+Table+" set "+str_Sql;
}
else
{
str_Sql = "update "+ Table +" set " + str_Sql + " where " + ht_Where;
}
str_Sql = str_Sql.Replace("set ,","set ").Replace("update ,","update ");
return str_Sql;
}
#endregion
#region 根据表名，where条件，哈稀表自动生成del语句(参数类型的)
public static string GetDelSqlbyHt(string Table, string ht_Where, Hashtable ht)
{
string str_Sql = "";
int i = 0;
int ht_Count = ht.Count; // 哈希表个数
IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
while ( myEnumerator.MoveNext() )
{
if (i==0)
{
if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)
{
str_Sql = myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
else
{
if (ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+" ").ToLower())==-1)
{
str_Sql = str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
}
}
i = i + 1;
}
if (ht_Where == null || ht_Where.Replace(" ","") == "") // 更新时候没有条件
{
str_Sql = "Delete "+Table;
}
else
{
str_Sql = "Delete "+Table+" where "+ht_Where;
}
return str_Sql;
}
#endregion
#region 生成oracle参数
/// <summary>
/// 生成oracle参数
/// </summary>
/// <param name="ParamName">字段名</param>
/// <param name="otype">数据类型</param>
/// <param name="size">数据大小</param>
/// <param name="Value">值</param>
/// <returns></returns>
public static OracleParameter MakeParam(string ParamName,System.Data.OracleClient.OracleType otype,int size,Object Value)
{
OracleParameter para = new OracleParameter(ParamName,Value);
para.OracleType = otype;
para.Size = size;
return para;
}
#endregion
#region 生成oracle参数
public static OracleParameter MakeParam(string ParamName, string Value)
{
return new OracleParameter(ParamName, Value);
}
#endregion
#region 根据表结构字段的类型和长度拼装oracle sql语句参数
public static void GetoType(string key, DataTable dt, out OracleType otype, out int size)
{
DataView dv = dt.DefaultView;
dv.RowFilter = "column_name='" + key + "'";
string fType = dv[0]["data_type"].ToString().ToUpper();
switch (fType)
{
case "DATE":
otype = OracleType.DateTime;
size = int.Parse(dv[0]["data_length"].ToString());
break;
case "CHAR":
otype= OracleType.Char;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case "LONG":
otype= OracleType.Double;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case "NVARCHAR2":
otype= OracleType.NVarChar;
size=int.Parse(dv[0]["data_length"].ToString());
break;
case "VARCHAR2":
otype= OracleType.NVarChar;
size=int.Parse(dv[0]["data_length"].ToString());
break;
default:
otype= OracleType.NVarChar;
size=100;
break;
}
}
#endregion
#region 动态取表里字段的类型和长度,此处没有动态用到connstr,是默认的！by/文少
public System.Data.DataTable GetTabType(string tabname)
{
string sql = "select column_name,data_type,data_length from all_tab_columns where table_name='"
+ tabname.ToUpper() + "'";
OpenConn();
return (ReturnDataSet(sql, "dv")).Tables[0];
}
#endregion
#region 执行sql语句
public int ExecuteNonQuery(string cmdText, params OracleParameter[] cmdParms)
{
OracleCommand cmd = new OracleCommand();
OpenConn();
cmd.Connection = Connection;
cmd.CommandText = cmdText;
if (cmdParms != null)
{
foreach (OracleParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
#endregion
/// <summary>
/// 根据表名得到所属用户
/// </summary>
/// <param name="strTableName">表名</param>
/// <returns>属于用户</returns>
public string GetTableOwner(string strTableName)
{
string strTableOwner = string.Empty;
OpenConn();
string sql = "select owner from dba_tables where table_name='" + strTableName.ToUpper() + "'";
OracleCommand cmd = new OracleCommand(sql, Connection);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.Read())
{
strTableOwner = odr[0].ToString();
}
odr.Close();
return strTableOwner;
}
}
第四步实现其他一些功能，如退出系统，代码如下：
Application.Exit();
为了以后其他模块能够知道登录的用户信息，实现了一个返回用户名的函数，也是一句代码实现，如下：
return userNameTxt.Text;
整个登录过程就全部实现，登录成功以后就开始初始化系统，初始化完成以后进入主界面。
