?? database.cs
字號:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
namespace MKIms3
{
/// <summary>
/// DataBase 的摘要說明。
/// </summary>
public class DataBase
{
private SqlConnection conn;
//private SqlCommand mycmd;
// private SqlDataAdapter da;
/// <summary>
/// 構造函數
/// </summary>
public DataBase()
{
//
// TODO: 在此處添加構造函數邏輯
//
this.conn = CreateConn();
}
/// <summary>
/// 通過讀取系統目錄下的授權文件經過解密得到包含連接信息的數組
/// </summary>
/// <returns>包含連接信息的數組</returns>
private string[] RetrunConn()
{
string input=null;
if (!File.Exists(Application.StartupPath+"\\授權文件.dat"))
{
MessageBox.Show("請確認您正確設置了授權文件","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
return null;
}
else
{
try
{
StreamReader sr = File.OpenText(Application.StartupPath+"\\授權文件.dat");
input=sr.ReadLine();
sr.Close();
string[] conAry = code_en_de.get_char(input);
return conAry;
}
catch(Exception e)
{
MessageBox.Show("系統錯誤！\n\n錯誤信息："+e.Message.ToString()+"","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
return null;
}
}
}
/// <summary>
/// 返回SQLSERVER數據庫的連接字符串
/// </summary>
/// <param name="conAry">包含連接信息的數組</param>
/// <returns></returns>
private string RSqlConn()
{
string[] conAry =RetrunConn();
if(conAry==null)
{
return null;
}
else
{
string SqlConn = "Data Source="+conAry[0]+";Initial Catalog="+conAry[1]+";User ID="+conAry[2]+";Password="+conAry[3];
return SqlConn;
}
}
/// <summary>
/// 返回工作空間連接字符串
/// </summary>
/// <param name="conAry">包含連接信息的數組</param>
/// <returns>有兩個元素的數組，為打開工作空間的兩個參數</returns>
public string[] MapConn()
{
string[] conAry = RetrunConn();
if(conAry==null)
{
return null;
}
else
{
string[] WorkSpaceConn = new string[2];
WorkSpaceConn[1] = "UID ="+conAry[2]+";pwd ="+conAry[3];
WorkSpaceConn[0] = "Provider = SQLOLEDB;Driver = SQL Server;SERVER = "+conAry[0]+";Database ="+conAry[1]+";Caption = 某礦信息管理系統;";//這個是工作空間名
return WorkSpaceConn;
}
}
/// <summary>
/// 建立數據庫連接對象
/// </summary>
/// <returns>返回一個數據庫連接對象</returns>
private SqlConnection CreateConn()
{
string constring = RSqlConn();
if(constring==null)
{
return null;
}
else
{
SqlConnection myConn = new SqlConnection(constring);
return myConn;
}
}
/// <summary>
/// 建立command對象
/// </summary>
/// <param name="storedProcName">存儲過程名稱</param>
/// <param name="parameters">參數數組</param>
/// <returns>SqlCommand</returns>
private SqlCommand BuildQueryCommand(string storedProcName,IDataParameter[] parameters)
{
if(conn!=null)
{
SqlCommand command = new SqlCommand(storedProcName,conn);
command.CommandType = CommandType.StoredProcedure;
foreach(SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
else
{
return null;
}
}
/// <summary>
/// 建立command對象 重載
/// </summary>
/// <returns>SqlCommand</returns>
private SqlCommand BuildQueryCommand(string storedProcName)
{
if(conn!=null)
{
SqlCommand command = new SqlCommand(storedProcName,conn);
command.CommandType = CommandType.StoredProcedure;
return command;
}
else
{
return null;
}
}
/// <summary>
/// 返回只讀的向前的記錄集
/// </summary>
/// <param name="storedProcName">存儲過程名稱</param>
/// <param name="parameters">存儲過程的參數數組</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcedure(string storedProcName,IDataParameter[] parameters)
{
//SqlConnection conn = CreateConn();
if(conn!=null)
{
SqlDataReader returnReader; //= new SqlDataReader();
try
{
conn.Open();
SqlCommand command = BuildQueryCommand(storedProcName,parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
catch(SqlException e)
{
MessageBox.Show("系統錯誤！\n\n錯誤信息："+e.Message.ToString()+"","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
conn.Close();
return null;
}
}
else
{
return null;
}
}
/// <summary>
/// 返回只讀的向前的記錄集 重載
/// </summary>
/// <param name="storedProcName">存儲過程名</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcedure(string storedProcName)
{
if(conn!=null)
{
SqlDataReader returnReader;
try
{
conn.Open();
SqlCommand command = BuildQueryCommand(storedProcName);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
catch(SqlException e)
{
MessageBox.Show("系統錯誤！\n\n錯誤信息："+e.Message.ToString()+"","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
return null;
}
}
else
{
return null;
}
}
/// <summary>
/// 返回一個dataset用來更新數據
/// </summary>
/// <param name="storedProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數</param>
/// <returns>dataset</returns>
public DataSet RunProcedure(string storedProcName,IDataParameter[] parameters,string tablename)
{
DataSet dataset = new DataSet();
SqlDataAdapter SqlDA = new SqlDataAdapter();
try
{
SqlDA.SelectCommand = BuildQueryCommand(storedProcName,parameters);
SqlDA.Fill(dataset,tablename);
return dataset;
}
catch(SqlException e)
{
MessageBox.Show("系統錯誤！\n\n錯誤信息："+e.Message.ToString()+"","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
return null;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回一個dataset用來更新數據 重載
/// </summary>
/// <param name="storedProcName">存儲過程名</param>
/// <returns>dataset</returns>
public DataSet RunProcedure(string storedProcName,string tablename)
{
DataSet dataset = new DataSet();
SqlDataAdapter SqlDA = new SqlDataAdapter();
try
{
SqlDA.SelectCommand = BuildQueryCommand(storedProcName);
SqlDA.Fill(dataset,tablename);
return dataset;
}
catch(SqlException e)
{
MessageBox.Show("系統錯誤！\n\n錯誤信息："+e.Message.ToString()+"","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
return null;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 執行無返回值的存儲過程
/// </summary>
/// <param name="storedProcName">存儲過程名</param>
/// <param name="parameters">存儲過程的參數</param>
/// <returns>執行是否成功</returns>
public bool RunProcedure_Nr(string storedProcName,IDataParameter[] parameters)
{
try
{
conn.Open();
SqlCommand mycommand = new SqlCommand();
mycommand = BuildQueryCommand(storedProcName,parameters);
mycommand.ExecuteNonQuery();
return true;
}
catch(SqlException e)
{
MessageBox.Show("系統錯誤！\n\n錯誤信息："+e.Message.ToString()+"","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
return false;
}
finally
{
conn.Close();
}
}
public bool Run_change(string selectaa,DataSet myset,string tablename)
{
// string selectaa = " select ceng.煤層名稱, ceng.止煤深度, ceng.底板坐標x, ceng.底板坐標y, ceng.底板坐標z, ceng.煤層偽厚, ceng.煤層傾角, ceng.利用厚度, ceng.頂板巖性, ceng.底板巖性, ceng.鉆孔序號, ceng.等級 from 煤層關聯鉆孔 as ceng where 1>2";
try
{
SqlDataAdapter da = new SqlDataAdapter(selectaa,conn);
SqlCommandBuilder cd = new SqlCommandBuilder(da);
da.Update(myset,tablename);
return true;
}
catch(SqlException e)
{
MessageBox.Show("系統錯誤！\n\n錯誤信息："+e.Message.ToString()+"","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
return false;
}
finally
{
conn.Close();
}
}
// public DataSet get_DataSet(string select,string tablename)
// {
// //mycmd = new SqlCommand(select,this.conn);
// da = new SqlDataAdapter(select,conn);
// DataSet myset = new DataSet();
// da.Fill(myset,tablename);
// return myset;
// }
//
// public bool update_DataSet(DataSet myset,string tablename)
// {
// try
// {
// //SqlDataAdapter da = new SqlDataAdapter(mycmd,conn);
// SqlCommandBuilder cb = new SqlCommandBuilder(da);
// da.Update(myset,tablename);
// return true;
// }
// catch(SqlException e)
// {
// MessageBox.Show("系統錯誤！\n\n錯誤信息："+e.Message.ToString()+"","錯誤",MessageBoxButtons.OK,MessageBoxIcon.Error);
// return false;
//
// }
// finally
// {
// conn.Close();
// }
//
// }
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -