jisco_winform_wzjl/CreateAccess/frmCreateAccess.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Infragistics.Win.UltraWinGrid;
using System.IO;
using System.Data.OleDb;        //连接Access数据库
using ADOX;
using System.Reflection;
using System.Threading;

namespace CreateAccess
{
    public partial class frmCreateAccess : Form
    {
        public frmCreateAccess()
        {
            InitializeComponent();
        }


        private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
        {
            switch (e.Tool.Key)
            {
                case "Query":
                    {
                        cbAll.Checked = false;
                        Query();
                        break;
                    }
                case "Export":
                    {
                        Export();
                        break;
                    }
                default: break;
            }
        }

        ServiceReference1.WebServerClient webServer = new ServiceReference1.WebServerClient();
        public void Query()
        {
            DataSet ds = new DataSet();
            

            try
            {
                ServiceReference1.meterToLimsOld mto = new ServiceReference1.meterToLimsOld();
                mto.cpmc = txtCpmc.Text.Trim();
                mto.qx = txtQx.Text.Trim();
                mto.ly = txtLy.Text.Trim();

                ServiceReference1.commonPage cp = new ServiceReference1.commonPage();
                cp.startTime = dtStartTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
                cp.endTime = dtEndTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
                cp.param = mto;
                
               
                ServiceReference1.meterToLimsOld[] ls = webServer.ExportAccess(cp);

                DataTable dt = this.dt_meterToOld.Clone();
                if (ls != null && ls.Length > 0)
                {
                    dt = ListToDataTable<ServiceReference1.meterToLimsOld>(ls);
                }
                CopyDataToDatatable(ref dt, ref this.dt_meterToOld, true);
                RefreshAndAutoSize(ultraGrid1);
            }
            catch (Exception ex) 
            {
                MessageBox.Show("查询异常："+ex.Message);
            }

        }

        public void Export() 
        {
            string fileName = Path.Combine(Application.StartupPath, "db1.mdb");
            bool bflag = File.Exists(fileName);
            if (!bflag)
            {
                CreateData(fileName, "weighing");
                AddData(fileName, "weighing");
            }
            else 
            {
                AddData(fileName, "weighing");
            }
        }

        #region 创建数据库并添加数据
        //创建数据库
        public void CreateData(string fileName, string Pwd)
        {
            if (File.Exists(fileName))
            {
                string delFile = fileName;
                File.Delete(delFile);
            }

            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Jet OLEDB:Database Password=" + Pwd + ";Jet OLEDB:Engine Type=5";
            //创建数据库
            ADOX.Catalog catalog = new Catalog();
            try
            {
                catalog.Create(conn);
            }
            catch(Exception ex)
            {
                MessageBox.Show("access创建失败："+ex.Message);
                return;
            }


            //连接数据库
            ADODB.Connection cn = new ADODB.Connection();
            cn.Open(conn, null, null, -1);
            catalog.ActiveConnection = cn;


            //新建表
            ADOX.Table table = new ADOX.Table();
            table.Name = "biaoqian";

            ADOX.Column column = new ADOX.Column();
            column.ParentCatalog = catalog;
            column.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
            column.Name = "id";
            column.DefinedSize = 9;
            column.Properties["AutoIncrement"].Value = true; //自增长
            table.Columns.Append(column, DataTypeEnum.adInteger, 0);
            //设置主键
            table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "id", "", "");

            table.Columns.Append("xh", DataTypeEnum.adInteger, 9); //整型9位
            table.Columns.Append("cpfl", DataTypeEnum.adVarWChar, 100); //文本50位
            table.Columns.Append("cpmc", DataTypeEnum.adVarWChar, 100); //文本50位
            table.Columns.Append("ly", DataTypeEnum.adVarWChar, 100); //文本50位
            table.Columns.Append("qx", DataTypeEnum.adVarWChar, 100); //文本50位
            table.Columns.Append("chh", DataTypeEnum.adVarWChar, 100); //文本50位
            table.Columns.Append("rq", DataTypeEnum.adDate);
            table.Columns.Append("ph", DataTypeEnum.adVarWChar, 100);

            ADOX.Column mzc = new ADOX.Column();
            mzc.Type = ADOX.DataTypeEnum.adDouble; // 必须先设置字段类型
            mzc.Name = "mz";
            mzc.NumericScale = 2;
            mzc.Attributes = ColumnAttributesEnum.adColNullable;
            table.Columns.Append(mzc, DataTypeEnum.adDouble, 10);

            ADOX.Column pzc = new ADOX.Column();
            pzc.Type = ADOX.DataTypeEnum.adDouble; // 必须先设置字段类型
            pzc.Name = "pz";
            pzc.NumericScale = 2;
            pzc.Attributes = ColumnAttributesEnum.adColNullable;
            table.Columns.Append(pzc, DataTypeEnum.adDouble, 10);

            ADOX.Column jzc = new ADOX.Column();
            jzc.Type = ADOX.DataTypeEnum.adDouble; // 必须先设置字段类型
            jzc.Name = "jz";
            jzc.NumericScale = 2;
            jzc.Attributes = ColumnAttributesEnum.adColNullable;
            table.Columns.Append(jzc, DataTypeEnum.adDouble, 10);


            table.Columns.Append("jly", DataTypeEnum.adVarWChar, 100);
            table.Columns.Append("sj", DataTypeEnum.adVarWChar, 100);

            ADOX.Column column1 = new ADOX.Column();
            column1.Type = ADOX.DataTypeEnum.adDouble; // 必须先设置字段类型
            column1.Name = "fjpz";
            column1.NumericScale = 2;
            column1.Attributes = ColumnAttributesEnum.adColNullable;
            table.Columns.Append(column1, DataTypeEnum.adDouble, 10);

            table.Columns.Append("bzh", DataTypeEnum.adVarWChar, 100);
            table.Columns.Append("bz", DataTypeEnum.adVarWChar, 100);

            ADOX.Column column2 = new ADOX.Column();
            column2.Type = ADOX.DataTypeEnum.adDouble; // 必须先设置字段类型
            column2.Name = "cpz";
            column2.NumericScale = 2;
            column2.Attributes = ColumnAttributesEnum.adColNullable;
            table.Columns.Append(column2, DataTypeEnum.adDouble, 10);

            ADOX.Column column3 = new ADOX.Column();
            column3.Type = ADOX.DataTypeEnum.adDouble; // 必须先设置字段类型
            column3.Name = "shl";
            column3.NumericScale = 2;
            column3.Attributes = ColumnAttributesEnum.adColNullable;
            table.Columns.Append(column3, DataTypeEnum.adDouble, 10);

            /*
            ADOX.Column column4 = new ADOX.Column();
            column4.Type = DataTypeEnum.adBoolean;
            column4.Name = "cjbz";
            object obj = column4.Properties;//["Default"].Value = false;
            table.Columns.Append(column4, DataTypeEnum.adBoolean);
            //*/
            table.Columns.Append("cjbz", DataTypeEnum.adBoolean,1);

            //weighing
            ADOX.Column column5 = new ADOX.Column();
            column5.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
            column5.Name = "cpbz";
            column5.Attributes = ColumnAttributesEnum.adColNullable;
            table.Columns.Append(column5, DataTypeEnum.adInteger, 10);
            try
            {
                catalog.Tables.Append(table);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //此处一定要关闭连接，否则添加数据时候会出错

            table = null;
            //catalog = null;
            cn.Close();

            try
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog.ActiveConnection);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog);
                catalog = null;
            }
            catch 
            {
                catalog = null;
            }
            System.GC.Collect();
        }


        private void AddData(string fileName, string pwd)
        {
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Persist Security Info=False;Jet OLEDB:Database password=" + pwd;
            DataSet ds = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(strConnection)) 
            {
                conn.Open();

                string strDel = "delete from biaoqian";
                OleDbCommand cmd = new OleDbCommand(strDel, conn);
                cmd.ExecuteNonQuery();

                string strSql = "select * from biaoqian";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);

                
                da.Fill(ds, "biaoqian");

                try
                {
                    foreach (UltraGridRow ugr in ultraGrid1.Rows)
                    {
                        if (ugr.Cells["UNCH"].Value.ToString().ToUpper() == "TRUE")
                        {
                            DataRow dr = ds.Tables["biaoqian"].NewRow();
                            if (ugr.Cells["xh"].Text.Trim() != "")
                                dr["xh"] = Convert.ToInt32(ugr.Cells["xh"].Text.Trim());

                            dr["cpfl"] = ugr.Cells["cpfl"].Text.Trim();
                            dr["cpmc"] = ugr.Cells["cpmc"].Text.Trim();
                            dr["ly"] = ugr.Cells["ly"].Text.Trim();
                            dr["qx"] = ugr.Cells["qx"].Text.Trim();
                            dr["chh"] = ugr.Cells["chh"].Text.Trim();

                            if (ugr.Cells["rq"].Text.Trim() != "")
                                dr["rq"] = ugr.Cells["rq"].Text.Trim().Split(' ')[0];

                            dr["ph"] = ugr.Cells["ph"].Text.Trim();

                            if (ugr.Cells["mz"].Text.Trim() != "")
                                dr["mz"] = Convert.ToDouble(ugr.Cells["mz"].Text.Trim());
                            if (ugr.Cells["pz"].Text.Trim() != "")
                                dr["pz"] = Convert.ToDouble(ugr.Cells["pz"].Text.Trim());
                            if (ugr.Cells["jz"].Text.Trim() != "")
                                dr["jz"] = Convert.ToDouble(ugr.Cells["jz"].Text.Trim());

                            dr["jly"] = ugr.Cells["cpfl"].Text.Trim();
                            if (ugr.Cells["sj"].Text.Trim() != "")
                                dr["sj"] = ugr.Cells["sj"].Text.Trim().Split(' ')[1];

                            if (ugr.Cells["fjpz"].Text.Trim() != "" && ugr.Cells["fjpz"].Text.Trim() != "0")
                                dr["fjpz"] = Convert.ToDouble(ugr.Cells["fjpz"].Text.Trim());


                            dr["bzh"] = ugr.Cells["cpfl"].Text.Trim();
                            dr["bz"] = ugr.Cells["cpfl"].Text.Trim();

                            if (ugr.Cells["cpz"].Text.Trim() != "")
                                dr["cpz"] = Convert.ToDouble(ugr.Cells["cpz"].Text.Trim());
                            //dr["shl"] = ugr.Cells["cpfl"].Text.Trim();
                            //dr["cjbz"] = false;
                            //dr["cpbz"] = ugr.Cells["cpfl"].Text.Trim();

                            ds.Tables["biaoqian"].Rows.Add(dr);
                        }
                    }


                    OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                    da.Update(ds, "biaoqian");
                    ds.AcceptChanges();

                    da.Dispose();
                    cb.Dispose();
                    MessageBox.Show("导出完成");
                    //System.Environment.Exit(0);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    System.GC.Collect();
                }
            }
        }
        #endregion

        #region 控件数据设置
        public void RefreshAndAutoSize(UltraGrid ultraGrid)
        {
            try
            {
                ultraGrid.BeginUpdate();
                foreach (UltraGridBand band in ultraGrid.DisplayLayout.Bands)
                {
                    foreach (UltraGridColumn column in band.Columns)
                    {
                        column.PerformAutoResize(PerformAutoSizeType.AllRowsInBand, band.ColHeadersVisible ? true : false);
                    }
                }
            }
            catch { }
            ultraGrid.EndUpdate();
        }

        public void CopyDataToDatatable(ref DataTable src, ref DataTable dest, bool ClearExists)
        {
            if (src == null)
            {
                dest = null;
                return;
            }

            if (dest == null)
            {
                dest = new DataTable();
            }


            if (ClearExists)
            {
                if (dest != null) dest.Rows.Clear();
            }

            DataRow CurRow, NewRow;

            if (src.Rows.Count > 0)
                for (int i = 0; i < src.Rows.Count; i++)
                {
                    CurRow = src.Rows[i];
                    NewRow = dest.NewRow();

                    for (int j = 0; j < src.Columns.Count; j++)
                    {
                        try
                        {
                            if (dest.Columns.Contains(src.Columns[j].ColumnName))
                            {
                                NewRow[src.Columns[j].ColumnName] = CurRow[j];
                            }
                        }
                        catch { }
                    }

                    dest.Rows.Add(NewRow);
                }

            dest.AcceptChanges();
        }

        public void SetUltraGridRowFilter(ref UltraGrid myGrid1, bool bAllowFilter)
        {
            try
            {
                if (bAllowFilter)
                {
                    if (myGrid1.DisplayLayout.Override.FilterUIType != FilterUIType.FilterRow)
                        myGrid1.DisplayLayout.Override.FilterUIType = FilterUIType.FilterRow;

                    for (int i = 0; i < myGrid1.DisplayLayout.Bands.Count; i++)
                    {
                        for (int j = 0; j < myGrid1.DisplayLayout.Bands[i].Columns.Count; j++)
                        {
                            try
                            {
                                if (!myGrid1.DisplayLayout.Bands[i].Columns[j].Hidden && myGrid1.DisplayLayout.Bands[i].Columns[j].RowLayoutColumnInfo.LabelPosition != LabelPosition.LabelOnly)
                                {
                                    //string sKey = myGrid1.DisplayLayout.Bands[i].Columns[j].Key; //UNCK  UNCH
                                    myGrid1.DisplayLayout.Bands[i].Columns[j].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.True;
                                }
                            }
                            catch { }
                        }
                    }
                }
                else
                {
                    if (myGrid1.DisplayLayout.Override.FilterUIType != FilterUIType.HeaderIcons)
                        myGrid1.DisplayLayout.Override.FilterUIType = FilterUIType.HeaderIcons;

                    for (int i = 0; i < myGrid1.DisplayLayout.Bands.Count; i++)
                    {
                        try
                        {
                            myGrid1.DisplayLayout.Bands[i].ColumnFilters.ClearAllFilters();
                        }
                        catch { }

                        for (int j = 0; j < myGrid1.DisplayLayout.Bands[i].Columns.Count; j++)
                        {
                            try
                            {
                                myGrid1.DisplayLayout.Bands[i].Columns[j].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False;
                            }
                            catch { }
                        }
                    }
                }
            }
            catch { }
        }
        #endregion

        private void cbAll_CheckedChanged(object sender, EventArgs e)
        {
            foreach (UltraGridRow ugr in ultraGrid1.Rows) 
            {
                ugr.Cells["UNCH"].Value = cbAll.Checked;
            }
        }

        public DataTable ListToDataTable<T>(T[] items)
        {
            DataTable dataTable = new DataTable();

            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                dataTable.Columns.Add(prop.Name);
            }

            foreach (T obj in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    values[i] = Props[i].GetValue(obj, null);
                }
                dataTable.Rows.Add(values);
            }

            return dataTable;
        }

        private void frmCreateAccess_Load(object sender, EventArgs e)
        {
            SetUltraGridRowFilter(ref ultraGrid1, true);
        }
    }

    
}