da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds.Tables[0] ?? new DataTable();
}
/// <summary>
/// 獲取數據根據sql語句 帶參數 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public DataTable GetProcTable(string procname, params SqlParameter[] pas)
{
DataSet ds = new DataSet();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
//cmd.CommandText = sql;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds.Tables[0] ?? new DataTable();
}
/// <summary>
/// 獲取數據根據sql語句 帶參數 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public DataTable GetProcCursorTable(string procname, params SqlParameter[] pas)
{
DataSet ds = new DataSet();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
//cmd.CommandText = sql;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
da.Fill(ds);
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return ds.Tables[1] ?? new DataTable();
}
/// <summary>
/// 獲取數據根據sql語句 帶參數 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public int GetProcState(string procname, params SqlParameter[] pas)
{
int state = 0;
try
{
OpenConn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
cmd.ExecuteNonQuery();
CloseConn();
state = Convert.ToInt32(pas[pas.Length - 1].Value);
}
catch
{
return 0;
}
return state;
}
/// <summary>
/// 獲取數據根據sql語句 帶參數 的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public int GetProcStateNo(string procname, params SqlParameter[] pas)
{
int state = 0;
try
{
OpenConn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
cmd.ExecuteNonQuery();
CloseConn();
state = 1;
// state = Convert.ToInt32(pas[pas.Length - 1].Value);
}
catch (Exception ex)
{
return 0;
}
return state;
}
/// <summary>
/// 根據sql語句返回跟新狀態
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool GetState(string sql)
{
bool succ = false;
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
OpenConn();
succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
CloseConn();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return false;
}
return succ;
}
/// <summary>
/// 根據sql語句返回跟新狀態帶參數的
/// </summary>
/// <param name="sql">sql語句</param>
/// <param name="pas">參數的集合</param>
/// <returns></returns>
public bool GetState(string sql, params SqlParameter[] pas)
{
bool succ = false;
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
OpenConn();
succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
CloseConn();
}
catch (Exception ex)
{
// this.ShowError(ex.Message);
//using (System.IO.StreamWriter sw = new System.IO.StreamWriter("D:\\error2008.txt"))
//{
// sw.Write(ex.Message);
// sw.Flush();
//}
return false;
}
return succ;
}
/// <summary>
/// 根據sql語句返回第一個單元格的數據
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public string GetOne(string sql)
{
string res = "";
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
OpenConn();
res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
CloseConn();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return res;
}
/// <summary>
/// 根據sql語句返回第一個單元格的數據帶參數的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public string GetOne(string sql, params SqlParameter[] pas)
{
string res = "";
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
OpenConn();
res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
CloseConn();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return res;
}
/// <summary>
/// 返回數據的DataReader
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public SqlDataReader GetDataReader(string sql)
{
SqlDataReader dr = null;
try
{
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.CommandText = sql;
dr = cmd.ExecuteReader();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return dr;
}
/// <summary>
/// 返回數據的DataReader帶參數的
/// </summary>
/// <param name="sql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public SqlDataReader GetDataReader(string sql, params SqlParameter[] pas)
{
SqlDataReader dr = null;
try
{
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.Parameters.Clear();
foreach (SqlParameter temppa in pas)
{
cmd.Parameters.Add(temppa);
}
cmd.CommandText = sql;
dr = cmd.ExecuteReader();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return null;
}
return dr;
}
/// <summary>
/// 事務處理函數
/// </summary>
/// <param name="al"></param>
/// <returns></returns>
public bool GetTranState(ArrayList al)
{
cmd.CommandType = CommandType.Text;
OpenConn();
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
try
{
for (int i = 0; i < al.Count; i++)
{
cmd.CommandText = Convert.ToString(al[i]);
cmd.ExecuteNonQuery();
}
tran.Commit();
CloseConn();
}
catch (Exception ex)
{
tran.Rollback();
return false;
}
return true;
}
/// <summary>
/// 事務處理函數
/// </summary>
/// <param name="al"></param>
/// <returns></returns>
public bool GetTranStateParameter(ArrayList al)
{
cmd.CommandType = CommandType.Text;
OpenConn();
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
try
{
for (int i = 0; i < al.Count; i++)
{
SqlTranModel stm = (SqlTranModel)al[i];
cmd.CommandText = Convert.ToString(stm.Sql);
SqlParameter[] pas = stm.Pas;
cmd.Parameters.Clear();
foreach (SqlParameter temp in pas)
{
cmd.Parameters.Add(temp);
}
cmd.ExecuteNonQuery();
}
tran.Commit();
CloseConn();
}
catch (Exception ex)
{
tran.Rollback();
return false;
}
return true;
}
/// <summary>
/// 分頁函數
/// </summary>
/// <param name="pagesize"></param>
/// <param name="columns"></param>
/// <param name="tablename"></param>
/// <param name="pid"></param>
/// <param name="order"></param>
/// <param name="current"></param>
/// <returns></returns>
public DataTable GetPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order)
{
current = current - 1 >= 0 ? (current - 1) : (0);
string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5} order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
return GetTable(sql);
}
/// <summary>
/// 分頁存儲過程的調用
/// </summary>
/// <param name="current"></param>
/// <param name="pagesize"></param>
/// <param name="columns"></param>
/// <param name="tablename"></param>
/// <param name="pid"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetProcPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string ordertype)
{
SqlParameter[] pas = { new SqlParameter("@PageIndex", current), new SqlParameter("@PageSize", pagesize), new SqlParameter("@Columns", columns), new SqlParameter("@Tablename", tablename), new SqlParameter("@Where", where), new SqlParameter("@Order", order), new SqlParameter("@OrderType", ordertype), new SqlParameter("@Pid", pid) };
return GetProcTable("Pages", pas);
//current = current - 1 >= 0 ? (current - 1) : (0);
//string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5} order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
//return GetTable(sql);
}
/// <summary>
/// 分頁存儲過程的調用
/// </summary>
/// <param name="current"></param>
/// <param name="pagesize"></param>
/// <param name="columns"></param>
/// <param name="tablename"></param>
/// <param name="pid"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetProcData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
{
SqlParameter[] pas = { new SqlParameter("@TableNames", SqlDbType.NVarChar, 200), new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@Fields", SqlDbType.NVarChar, 800), new SqlParameter("@Filter", SqlDbType.NVarChar, 1000), new SqlParameter("@ResultCount", SqlDbType.NVarChar, 12), new SqlParameter("@distinct", SqlDbType.NVarChar, 12) };
pas[0].Value = tablename;
pas[1].Value = pid;
pas[2].Value = order;
pas[3].Value = current;
pas[4].Value = pagesize;
pas[5].Value = columns;
pas[6].Value = where;
pas[7].Value = resultCount;
pas[8].Value = distinct;
return this.GetProcTable("Pages", pas);
//SqlParameter[] parameters = {
// new SqlParameter("@TableNames",SqlDbType.VarChar,8000),
// new SqlParameter("@PrimaryKey", SqlDbType.VarChar,8000),
// new SqlParameter("@Order", SqlDbType.VarChar,8000),
// new SqlParameter("@CurrentPage",SqlDbType.Int),
// new SqlParameter("@PageSize", SqlDbType.Int),
// new SqlParameter("@Fields",SqlDbType.VarChar,8000),
// new SqlParameter("@Filter", SqlDbType.VarChar,8000),
// new SqlParameter("@Group", SqlDbType.VarChar,8000)
// };
//parameters[0].Value = tablename;
//parameters[1].Value = pid;
//parameters[2].Value = order;
//parameters[3].Value = current;
//parameters[4].Value = pagesize;
//parameters[5].Value = columns;
//parameters[6].Value = where;
//parameters[7].Value = "";
//DataSet ds = DbHelperSQL.RunProcedure("Pages", parameters, "data");
//return ds.Tables["data"];//
//return GetProcTable("Pages", pas);
//current = current - 1 >= 0 ? (current - 1) : (0);
//string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5} order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
//return GetTable(sql);
}
/// <summary>
/// 分頁存儲過程的調用
/// </summary>
/// <param name="current"></param>
/// <param name="pagesize"></param>
/// <param name="columns"></param>
/// <param name="tablename"></param>
/// <param name="pid"></param>
/// <param name="where"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetProcAdminData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
{
SqlParameter[] pas = { new SqlParameter("@TableNames", SqlDbType.NVarChar, 200), new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurrentPage", SqlDbType.Int), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@Fields", SqlDbType.NVarChar, 800), new SqlParameter("@Filter", SqlDbType.NVarChar, 200), new SqlParameter("@ResultCount", SqlDbType.NVarChar, 24), new SqlParameter("@Distinct", SqlDbType.NVarChar, 12) };
pas[0].Value = tablename;
pas[1].Value = pid;
pas[2].Value = order;
pas[3].Value = current;
pas[4].Value = pagesize;
pas[5].Value = columns;
pas[6].Value = where;
pas[7].Value = resultCount;
pas[8].Value = distinct;
return this.GetProcTable("Pages", pas);
}
/// <summary>
/// 打開連接
/// </summary>
public void OpenConn()
{
if (conn.State != ConnectionState.Open)
{
try
{
conn.Open();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return;
}
}
}
/// <summary>
/// 關閉連接
/// </summary>
public void CloseConn()
{
if (conn.State != ConnectionState.Closed)
{
try
{
conn.Close();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return;
}
}
}
public void DisposeConn()
{
try
{
if (conn.State != ConnectionState.Closed)
{
try
{
conn.Close();
}
catch (Exception ex)
{
this.ShowError(ex.Message);
return;
}
}
}
catch
{
}
}
/// <summary>
/// 彈出錯誤的信息
/// </summary>
/// <param name="err"></param>
public void ShowError(string err)
{
// System.Web..Current.Response.Write(Script(err, ""));
}
/// <summary>
/// 顯示信息
/// </summary>
/// <param name="err"></param>
public void ShowMessage(string mes, string loc)
{
// System.Web.HttpContext.Current.Response.Write(Script(mes, loc));
}
/// <summary>
/// javascript腳本
/// </summary>
/// <param name="mess"></param>
/// <param name="loc"></param>
/// <returns></returns>
public string Script(string mess, string loc)
{
StringBuilder sb = new StringBuilder();
sb.Append("<script language='javascript'>");
sb.Append("alert('");
sb.Append(mess);
sb.Append("');");
sb.Append(loc);
sb.Append("</script>");
return sb.ToString();
}
/// <summary>
/// 彈出錯誤的信息
/// </summary>
/// <param name="err"></param>
public static void ShowErrorstatic(string err)
{
// System.Web.HttpContext.Current.Response.Write(DbAccess.Scriptstatic(err, ""));
}
/// <summary>
/// 顯示信息
/// </summary>
/// <param name="err"></param>
public static void ShowMessagestatic(string mes, string loc)
{
//System.Web.HttpContext.Current.Response.Write(DbAccess.Scriptstatic(mes, loc));
}
//<summary>
//javascript腳本
//</summary>
//<param name="mess"></param>
//<param name="loc"></param>
//<returns></returns>
public static string Scriptstatic(string mess, string loc)
{
StringBuilder sb = new StringBuilder();
sb.Append("<script language='javascript'>");
sb.Append("alert('");
sb.Append(mess);
sb.Append("');");
sb.Append(loc);
sb.Append("</script>");
return sb.ToString();
}
}
public class SqlTranModel
{
private string _sql;
public string Sql
{
set { this._sql = value; }
get { return this._sql; }
}
private SqlParameter[] _pas;
public SqlParameter[] Pas
{
set { this._pas = value; }
get { return this._pas; }
}
}
}
以上是數據庫操作類
using System;
using System.Collections.Generic;
using System.Text;
namespace Projects.Utils
{
public class ManageForm:System.Windows.Forms.Form
{
private DbAccess _db;
public DbAccess Db
{
set{
_db = value;
}
get{
if (_db == null)
{
_db = new DbAccess();
}
return _db;
}
}
}
}
頁面繼承類
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using System.Data.SqlClient;
namespace DataImportFromAccess
{
public partial class DataImport : Projects.Utils.ManageForm
{
protected ArrayList al = new ArrayList();
protected int i = 0;
public DataImport()
{
InitializeComponent();
}
public void InItData()
{
}
public void Dirs(string path)
{
DirectoryInfo dis = new DirectoryInfo(path);
FileInfo[] files = dis.GetFiles();
foreach (FileInfo fi in files)
{
i++;
Application.DoEvents();
this.label1.Text = i.ToString();
try
{
if (fi.FullName.ToString().ToLower().Contains("spiderresult.mdb"))
{
al.Add(fi.FullName.ToString());
}
else
{
}
// File.Delete(fi.FullName);
}
catch (Exception ex)
{
//this.richTextBox1.Text += ex.Message;.
MessageBox.Show(ex.Message);
}
}
if (dis.GetDirectories().Length > 0)
{
for (int i = 0; i < dis.GetDirectories().Length; i++)
{
Dirs(dis.GetDirectories()[i].FullName);
}
}
}
private void BtSelectFile_Click(object sender, EventArgs e)
{
//this.OpdFiles.ShowDialog();
this.FbdFiles.ShowDialog();
//this.OpdFiles.
}
private void BtImport_Click(object sender, EventArgs e)
{
//MessageBox.Show(System.Configuration.ConfigurationManager.AppSettings["datasource"]);
// Projects.Utils.DbAccess
Dirs(this.FbdFiles.SelectedPath);
ArrayList alsql = new ArrayList();
string tempCompanyName = "";//公司名稱
string tempCode = "";//郵 編
string tempAdd = "";//地 址
string tempLinkMan = "";//聯 系 人
string tempTel= "";//聯系電話
string tempMobile = "";//手 機
string tempFax = ""; //傳 真
string tempSql = "";
foreach (string str in al)
{
try
{
DataTable dt = this.Db.GetTable(string.Format("select * from openrowset('Microsoft.Jet.OLEDB.4.0','{0}';'admin';'',Content)", str));
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
tempCompanyName = Convert.ToString(dr["公司名稱"]).Replace("'","''");
// tempCode = Convert.ToString(dr["郵 編"]);
tempAdd = Convert.ToString(dr["地 址"]).Replace("'", "''");
tempLinkMan = Convert.ToString(dr["聯 系 人"]).Replace("'", "''");
tempTel = Convert.ToString(dr["聯系電話"]).Replace("'", "''");
tempMobile = Convert.ToString(dr["手 機"]).Replace("'", "''");
tempFax = Convert.ToString(dr["傳 真"]).Replace("'", "''");
tempSql = string.Format("insert into Company(CompanyName,Address,LinkMan,Tel,Mobile,Fax) values('{0}','{1}','{2}','{3}','{4}','{5}')", tempCompanyName, tempAdd, tempLinkMan, tempTel, tempMobile, tempFax);
alsql.Add(tempSql);
}
this.Db.GetTranState(alsql);
alsql.Clear();
}
}
catch (Exception ex)
{
continue;
MessageBox.Show(ex.Message);
}
}
MessageBox.Show("success");
}
private void button1_Click(object sender, EventArgs e)
{
//new Form1().ShowDialog();
SqlDataReader dr = this.Db.GetDataReader("select CompanyName,Address,LinkMan,Tel,");
while (dr.Read())
{
}
}
private void DataImport_Load(object sender, EventArgs e)
{
}
}
}
遞歸導入access數據類源碼
namespace DataImportFromAccess
{
partial class DataImport
{
/// <summary>
/// 必需的設計器變量。
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// 清理所有正在使用的資源。
/// </summary>
/// <param name="disposing">如果應釋放托管資源，為 true；否則為 false。</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows 窗體設計器生成的代碼
/// <summary>
/// 設計器支持所需的方法 - 不要
/// 使用代碼編輯器修改此方法的內容。
/// </summary>
private void InitializeComponent()
{
this.OpdFiles = new System.Windows.Forms.OpenFileDialog();
this.BtSelectFile = new System.Windows.Forms.Button();
this.BtImport = new System.Windows.Forms.Button();
this.button1 = new System.Windows.Forms.Button();
this.FbdFiles = new System.Windows.Forms.FolderBrowserDialog();
this.label1 = new System.Windows.Forms.Label();
this.SuspendLayout();
//
// OpdFiles
//
this.OpdFiles.FileName = "openFileDialog1";
//
// BtSelectFile
//
this.BtSelectFile.Location = new System.Drawing.Point(32, 38);
this.BtSelectFile.Name = "BtSelectFile";
this.BtSelectFile.Size = new System.Drawing.Size(86, 23);
this.BtSelectFile.TabIndex = 0;
this.BtSelectFile.Text = "選擇文件";
this.BtSelectFile.UseVisualStyleBackColor = true;
this.BtSelectFile.Click += new System.EventHandler(this.BtSelectFile_Click);
//
// BtImport
//
this.BtImport.Location = new System.Drawing.Point(139, 38);
this.BtImport.Name = "BtImport";
this.BtImport.Size = new System.Drawing.Size(75, 23);
this.BtImport.TabIndex = 1;
this.BtImport.Text = "導入數據";
this.BtImport.UseVisualStyleBackColor = true;
this.BtImport.Click += new System.EventHandler(this.BtImport_Click);
//
// button1
//
this.button1.Location = new System.Drawing.Point(239, 38);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 2;
this.button1.Text = "button1";
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(60, 158);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(41, 12);
this.label1.TabIndex = 3;
this.label1.Text = "label1";
//
// DataImport
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(663, 420);
this.Controls.Add(this.label1);
this.Controls.Add(this.button1);
this.Controls.Add(this.BtImport);
this.Controls.Add(this.BtSelectFile);
this.Name = "DataImport";
this.Text = "數據導入";
this.Load += new System.EventHandler(this.DataImport_Load);
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.OpenFileDialog OpdFiles;
private System.Windows.Forms.Button BtSelectFile;
private System.Windows.Forms.Button BtImport;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.FolderBrowserDialog FbdFiles;
private System.Windows.Forms.Label label1;
}
}
l類設計源碼