using System;using System.Data;using System.Data.SqlClient;using System.Windows.Forms;
namespace DataLibrary{ /// <summary> /// 数据连接设置 /// </summary> public class SqlConnect { static string sqlconnstr;
/// <summary> /// 连接字符串 /// </summary> public string ConnString { set { sqlconnstr=value; } get { return sqlconnstr; } }
/// <summary> /// 取得连接 /// </summary> /// <returns></returns> public SqlConnection GetSqlConnection() { SqlConnection _connection=null; if(sqlconnstr==null || sqlconnstr=="") { string SystemDir=System.Environment.SystemDirectory; DisposalConductService DisposalConductService1= new DisposalConductService(SystemDir+"/CBERP_Dis.DLL") ; sqlconnstr = "server="+DisposalConductService1.GetValue("ServerName","(local)") +";user id="+DisposalConductService1.GetValue("SqlAccount","sa") +";password="+DisposalConductService1.GetValue("PassWord","jiayu520") +";Database="+DisposalConductService1.GetValue("Database","CBERP"); } try { _connection = new SqlConnection(sqlconnstr); _connection.Open(); } catch(SqlException SE) { if(SE.Number==17 || SE.Number==18456) { if( MessageBox.Show("数据库访问参数设置不正确,是否对需要对其进行设置?","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Question)==DialogResult.Yes) { sqlconnstr=""; DisposalConduct config = new DisposalConduct(); config.ShowDialog(); _connection=GetSqlConnection(); } else { MessageBox.Show("建立数据库连接失败,程序不能继续运行!","错误",MessageBoxButtons.OK,MessageBoxIcon.Stop); Application.ExitThread(); Application.Exit(); } } else { MessageBox.Show(SE.Message,"提示信息",MessageBoxButtons.OK,MessageBoxIcon.Information); _connection.Close(); } } return _connection; } }
/// <summary> /// 数据配置 /// </summary> public class DataCatena : IDisposable { private SqlConnection _connection; /// <summary> /// 数据配置 /// </summary> public DataCatena() { SqlConnect sqlconnect = new SqlConnect(); _connection = sqlconnect.GetSqlConnection(); _connection.Close(); }
/// <summary> /// 数据配置 /// </summary> /// <param name="connStr">连接字符串</param> public DataCatena(String connStr) { _connection = new SqlConnection(connStr); }
/// <summary> /// 取得DataView /// </summary> /// <param name="Sqlstr">连接字符串</param> /// <returns>数据视图</returns> public DataView GetDataView(String Sqlstr) { DataView dv=null; SqlDataAdapter myCommand = new SqlDataAdapter(Sqlstr, _connection); DataSet ds = new DataSet(); try { myCommand.Fill(ds,"table"); dv=ds.Tables["table"].DefaultView; } catch(SqlException exc) { DebugBox db = new DebugBox(Sqlstr + "" + exc.Message); db.Show(); } return dv; }
/// <summary> /// 清理资源 /// </summary> public void Dispose() { Dispose(true); GC.SuppressFinalize(true); }
/// <summary> /// 清理所有正在使用的资源。 /// </summary> /// <param name="disposing"></param> protected virtual void Dispose(bool disposing) { if (! disposing) return;
if (this._connection != null) { this._connection.Dispose(); } }
/// <summary> /// 取得数据适配器 /// </summary> /// <param name="Sqlstr">SQL语句</param> /// <returns>数据适配器</returns> public SqlDataAdapter GetSqlDA(string Sqlstr) { SqlDataAdapter myCommand = new SqlDataAdapter(Sqlstr, _connection); return myCommand; }
/// <summary> /// 取得数据表 /// </summary> /// <param name="Sqlstr">SQL语句</param> /// <returns>数据表</returns> public DataTable GetDT(String Sqlstr) { SqlDataAdapter myCommand = new SqlDataAdapter(Sqlstr, _connection); DataSet ds = new DataSet(); try { myCommand.Fill(ds,"table"); } catch(SqlException exc) { DebugBox db = new DebugBox(Sqlstr + "" + exc.ToString()); db.Show(); } return ds.Tables["table"]; }
/// <summary> /// 执行语句 /// </summary> /// <param name="Sqlstr">SQL语句</param> /// <returns></returns> public object ExecWithValue(String Sqlstr) { object result=null; _connection.Open(); SqlCommand myCommand = new SqlCommand(Sqlstr, _connection); try { result=myCommand.ExecuteScalar(); } catch (SqlException exc) { DebugBox db = new DebugBox(Sqlstr + "" + exc.ToString()); db.Show(); //MessageBox.Show(exc.Message); } _connection.Close(); return result; }
/// <summary> /// 返回相应的行数 /// </summary> /// <param name="Sqlstr">SQL语句</param> /// <returns>行数</returns> public int Exec(String Sqlstr) { int sucess=0; SqlCommand myCommand = new SqlCommand(Sqlstr, _connection); _connection.Open(); try { myCommand.ExecuteNonQuery(); } catch (SqlException exc) { DebugBox db = new DebugBox(Sqlstr + "" + exc.ToString()); db.Show(); // MessageBox.Show(exc.Message); sucess=exc.Number; } _connection.Close(); return sucess; } }
/// <summary> /// 单表操作 /// </summary> public class SingleTable { private DataCatena DataCatena; private SqlDataAdapter myDataAdapter; private DataSet ds; /// <summary> /// 单表操作 /// </summary> /// <param name="Sqlstr"></param> public SingleTable(string Sqlstr) { DataCatena = new DataCatena(); myDataAdapter = DataCatena.GetSqlDA(Sqlstr); ds = new DataSet(); myDataAdapter.Fill(ds, "Table1"); }
/// <summary> /// 更新一个表 /// </summary> /// <param name="Sqlstr"></param> public void SetUpdateCommand(string Sqlstr) { myDataAdapter.UpdateCommand = new SqlCommand(Sqlstr,myDataAdapter.SelectCommand.Connection); }
/// <summary> /// 返回数据表 /// </summary> /// <returns></returns> public DataTable GetDataTable() { return ds.Tables["Table1"]; }
/// <summary> /// 返回数据适配器 /// </summary> /// <returns></returns> public SqlDataAdapter GetDataAdapter() { return myDataAdapter; }
/// <summary> /// 更新表 /// </summary> public void Update() { myDataAdapter.Update(ds, "Table1"); } }
/// <summary> /// 自动更新 /// </summary> public class AutoUpdateObj { private DataCatena DataCatena; private SqlDataAdapter myDataAdapter; private SqlCommandBuilder cb; private DataSet ds; /// <summary> /// 自动更新 /// </summary> /// <param name="Sqlstr"></param> public AutoUpdateObj(string Sqlstr) { DataCatena = new DataCatena(); myDataAdapter = DataCatena.GetSqlDA(Sqlstr); cb = new SqlCommandBuilder(myDataAdapter); ds = new DataSet(); myDataAdapter.Fill(ds, "Table1"); }
/// <summary> /// 更新SQL语句 /// </summary> /// <param name="Sqlstr"></param> public void SetUpdateCommand(string Sqlstr) { myDataAdapter.UpdateCommand = new SqlCommand(Sqlstr,myDataAdapter.SelectCommand.Connection); }
/// <summary> /// 设置Select语句 /// </summary> /// <param name="Sqlstr"></param> public void SetSelectCommand(string Sqlstr) { myDataAdapter.SelectCommand = new SqlCommand(Sqlstr,myDataAdapter.SelectCommand.Connection); cb.DataAdapter = myDataAdapter ; ds.Tables.Clear(); }
/// <summary> /// 取得数据表 /// </summary> /// <returns></returns> public DataTable GetDataTable() { ds.Clear(); myDataAdapter.Fill(ds, "Table1"); return ds.Tables["Table1"]; }
/// <summary> /// 取得数据适配器 /// </summary> /// <returns></returns> public SqlDataAdapter GetDataAdapter() { return myDataAdapter; }
/// <summary> /// 更新 /// </summary> public void Update() { try { myDataAdapter.Update(ds, "Table1"); } catch(SqlException sqlex) { MessageBox.Show("出错原因："+sqlex.Message,"出错",MessageBoxButtons.OK,MessageBoxIcon.Error); } } }}