基于ArcGIS10.0和Oracle10g的空間數(shù)據(jù)管理平臺二（C#開發(fā)）
上一篇文章把整個系統(tǒng)的框架和功能簡單介紹了一下，這個系統(tǒng)的架構(gòu)就是C/S，數(shù)據(jù)庫采用的是Oracle10g，空間數(shù)據(jù)庫采用的ArcGIS基于Oracle10g的ArcSDE，開發(fā)工具用的是VS2008，開發(fā)平臺是.NET Framework3.5，代碼的版本控制工具是SVN。
在講解整個系統(tǒng)的實現(xiàn)的時候我是按照我開發(fā)的功能順序介紹，其實很多功能模塊都是相對獨立的，今天就從最簡單的登錄模塊的實現(xiàn)開始。登錄模塊在兩個地方使用到，第一處當然就是第一次進入主界面以前，第二次是進入元數(shù)據(jù)管理子系統(tǒng)的時候需要用有相應(yīng)權(quán)限的賬戶登錄。界面的設(shè)計就不詳細介紹了，這個可以通過開發(fā)工具直接拖出來，及所謂的所見即所得編輯。
第一步我們需要設(shè)計一個數(shù)據(jù)庫的表來專門保存用戶信息，主要是用戶名、密碼和相應(yīng)權(quán)限。
第二步就是設(shè)計界面了，必須有登錄和取消按鈕，這兩個按鈕的功能就是登錄和退出系統(tǒng)的功能。額外還需要的就是在用戶輸入用戶名和密碼錯誤的時候需要有提示信息，通常的做法就是彈出一個提示對話框，但是我在這里并不是這樣做的，為了更好的用戶體驗，我用了一個靜態(tài)文本標簽按鈕來提示相應(yīng)信息，這樣用戶就不用在去點擊確認信息了。
第三步編寫登錄按鈕的響應(yīng)事件代碼，當用戶點擊的時候執(zhí)行這個功能，實現(xiàn)代碼如下：
//首先判斷用戶名和密碼都不能為空
if (userNameTxt.Text.Trim() == "" || passwordTxt.Text.Trim() == "")
{
errlabel.Text = "用戶名和密碼不能為空!";
return;
}
SqlHelper sh = new SqlHelper();//用Sql幫助類來實現(xiàn)查詢，封裝了常用的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 = "系統(tǒng)正在初始化，請稍等...";
this.Update();
errlabel.Update();
while (!FrmMain.isInitFinish)
{
;
}
this.Close();
}
else
{
LogHelp.writeLog(userNameTxt.Text, "用戶登錄", "用戶登陸失敗");
errlabel.Text = "用戶名或密碼錯誤";
}
上面這段代碼根據(jù)用戶輸入的用戶名和密碼來判斷是否正確的，以及是屬于哪種權(quán)限的用戶。上面的代碼用到了SQL的一個幫助類，主要封裝了一些常用的SQL操作，這個類定義如下（以后還會經(jīng)常用到這個類，基本上所有的SQL操作都由這個類完成，除了一些特殊操作，例如事務(wù)、必須考參數(shù)插入很長字符串等）：
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>
/// 帶參數(shù)的構(gòu)造函數(shù)
/// </summary>
/// <param name="ConnString">數(shù)據(jù)庫聯(lián)接字符串</param>
public SqlHelper(string ConnString)
{
string connStr;
connStr = System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();
Connection = new OracleConnection(connStr);
}
/// <summary>
/// 打開數(shù)據(jù)庫
/// </summary>
public void OpenConn()
{
if (this.Connection.State != ConnectionState.Open)
{
this.Connection.Open();
}
}
/// <summary>
/// 關(guān)閉數(shù)據(jù)庫聯(lián)接
/// </summary>
public void CloseConn()
{
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
}
#region 執(zhí)行SQL語句，返回數(shù)據(jù)到DataSet中
/// <summary>
/// 執(zhí)行SQL語句，返回數(shù)據(jù)到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 執(zhí)行Sql語句,返回帶分頁功能的dataset
/// <summary>
/// 執(zhí)行Sql語句,返回帶分頁功能的dataset
/// </summary>
/// <param name="sql">Sql語句</param>
/// <param name="PageSize">每頁顯示記錄數(shù)</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 執(zhí)行SQL語句，返回 DataReader,用之前一定要先.read()打開,然后才能讀到數(shù)據(jù)
/// <summary>
/// 執(zhí)行SQL語句，返回 DataReader,用之前一定要先.read()打開,然后才能讀到數(shù)據(jù)
/// </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 執(zhí)行SQL語句，返回記錄總數(shù)數(shù)
/// <summary>
/// 執(zhí)行SQL語句，返回記錄總數(shù)數(shù)
/// </summary>
/// <param name="sql">sql語句</param>
/// <returns>返回記錄總條數(shù)</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 執(zhí)行SQL語句,返回所影響的行數(shù)
/// <summary>
/// 執(zhí)行SQL語句,返回所影響的行數(shù)
/// </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
//?。剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑?
//?。剑接胔ashTable對數(shù)據(jù)庫進行insert,update,del操作,注意此時只能用默認的數(shù)據(jù)庫連接"connstr"＝＝
//?。剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑?
#region 根據(jù)表名及哈稀表自動插入數(shù)據(jù)庫 用法：Insert("test",ht)
/// <summary>
/// 用hashTable對數(shù)據(jù)庫進行insert操作,注意此時只能用默認的數(shù)據(jù)庫連接"connstr"＝＝
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="ht">鍵值對的HashTable(字段名，值)</param>
/// <returns>影響的行數(shù)</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;
// 作哈希表循環(huán)
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 根據(jù)相關(guān)條件對數(shù)據(jù)庫進行更新操作 用法：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;
// 作哈希表循環(huán)
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操作,注意此處條件個數(shù)與hash里參數(shù)個數(shù)應(yīng)該一致 用法：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;
// 作哈希表循環(huán)
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
//?。剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑? //?。剑剑剑剑剑剑剑缴厦嫒齻€操作的內(nèi)部調(diào)用函數(shù)＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
#region 根據(jù)哈稀表及表名自動生成相應(yīng)insert語句(參數(shù)類型的)
/// <summary>
/// 根據(jù)哈稀表及表名自動生成相應(yīng)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; // 哈希表個數(shù)
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 根據(jù)表名，where條件，哈稀表自動生成更新語句(參數(shù)類型的)
public static string GetUpdateSqlbyHt(string Table,string ht_Where,Hashtable ht)
{
string str_Sql = "";
int i = 0;
int ht_Count = ht.Count; // 哈希表個數(shù)
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 根據(jù)表名，where條件，哈稀表自動生成del語句(參數(shù)類型的)
public static string GetDelSqlbyHt(string Table, string ht_Where, Hashtable ht)
{
string str_Sql = "";
int i = 0;
int ht_Count = ht.Count; // 哈希表個數(shù)
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參數(shù)
/// <summary>
/// 生成oracle參數(shù)
/// </summary>
/// <param name="ParamName">字段名</param>
/// <param name="otype">數(shù)據(jù)類型</param>
/// <param name="size">數(shù)據(jù)大小</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參數(shù)
public static OracleParameter MakeParam(string ParamName, string Value)
{
return new OracleParameter(ParamName, Value);
}
#endregion
#region 根據(jù)表結(jié)構(gòu)字段的類型和長度拼裝oracle sql語句參數(shù)
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 動態(tài)取表里字段的類型和長度,此處沒有動態(tài)用到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 執(zhí)行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>
/// 根據(jù)表名得到所屬用戶
/// </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;
}
}
第四步實現(xiàn)其他一些功能，如退出系統(tǒng)，代碼如下：
Application.Exit();
為了以后其他模塊能夠知道登錄的用戶信息，實現(xiàn)了一個返回用戶名的函數(shù)，也是一句代碼實現(xiàn)，如下：
return userNameTxt.Text;
整個登錄過程就全部實現(xiàn)，登錄成功以后就開始初始化系統(tǒng)，初始化完成以后進入主界面。
浙公網(wǎng)安備 33010602011771號