/// <summary>
        /// Make a connection to the excel file using OleDB and read from it like a SQL database
        /// and then create a dataTable to be converted into a list of objects
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        internal static System.Data.DataTable ReadExcelFile(string filePath)
        {
            string sheetName = null;
            string connStr   = String.Format(_excelConnection, filePath);

            System.Data.DataTable tablesList         = new System.Data.DataTable();
            System.Data.DataTable excelData          = new System.Data.DataTable();
            OleDbCommand          oleExcelCommand    = default(OleDbCommand);
            OleDbDataReader       oleExcelReader     = default(OleDbDataReader);
            OleDbConnection       oleExcelConnection = default(OleDbConnection);

            //Connect to Excel File
            oleExcelConnection = new OleDbConnection(connStr);
            oleExcelConnection.Open();

            try
            {
                tablesList = oleExcelConnection.GetSchema("Tables");

                //Grab all the first sheet, which work as a table
                if (tablesList.Rows.Count > 0)
                {
                    sheetName = tablesList.Rows[0]["TABLE_NAME"].ToString();
                }

                tablesList.Clear();
                tablesList.Dispose();

                //If a sheet exists, then select all the content from it and load it into a data file
                if (!string.IsNullOrEmpty(sheetName))
                {
                    oleExcelCommand             = oleExcelConnection.CreateCommand();
                    oleExcelCommand.CommandText = "Select * From [" + sheetName + "]";
                    oleExcelCommand.CommandType = CommandType.Text;
                    oleExcelReader = oleExcelCommand.ExecuteReader();

                    excelData.Load(oleExcelReader);
                    oleExcelReader.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                tablesList.Clear();
                tablesList.Dispose();
                oleExcelConnection.Close();
            }

            return(excelData);
        }
Beispiel #2
0
        protected void Page_Load(object sender, EventArgs e)
        {
            #region 标签信息
            string strTagInfo = "";

            try
            {
                string sql = "select * from [tb_tag] where tag_ispublic=1";
                DataTable dt = new DataTable();
                DBHelper dbh = new DBHelper(config.DBConn);
                dt = dbh.ExecuteDataTable("", sql);

                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        strTagInfo += "<a href=\"default.aspx?id=" + dt.Rows[i]["tag_id"].ToString() + "\" class=\"tag\" target=\"_blank\">" + dt.Rows[i]["tag_name"].ToString() + "</a>";
                    }
                }

                dbh.Dispose();
                dt.Dispose();
            }
            catch (Exception ex)
            {
                strTagInfo = ex.ToString();
            }

            this.tagBall.InnerHtml = strTagInfo;

            #endregion
        }
Beispiel #3
0
        public DataTable GetCustomerByID(int id)
        {
            string connectionString = WebConfigurationManager.AppSettings["ConnectionString"];

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataTable dt = new DataTable();
                SqlCommand command = new SqlCommand(Resources.Resource.GetCustomerByID, connection);
                command.Parameters.AddWithValue("@id", id);

                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    dt.Load(reader);

                    return dt;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    dt.Dispose();
                    connection.Close();
                }

                return null;
            }
        }
        public DataTable compareBilling(int customerId, string month1, int year1, string month2, int year2)
        {
            string query_select1 = "select * from BILLING where customerId = " + customerId + "and billingMonth = '" + month1 + "' and billingYear = " + year1;
            string query_select2 = "select * from BILLING where customerId = " + customerId + "and billingMonth = '" + month2 + "' and billingYear = " + year2;

            try
            {
                using (connect = new SqlConnection(connString))
                {
                    connect.Open();
                    dt = new DataTable();
                    sda = new SqlDataAdapter(query_select1, connect);
                    sda.Fill(dt);
                    sda = new SqlDataAdapter(query_select2, connect);
                    sda.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sda.Dispose();
                dt.Dispose();
                connect.Close();
            }
            return dt;
        }
Beispiel #5
0
        private void MaterialRaisedButton2_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult x = MessageBox.Show(dataGridView1.CurrentRow.Cells[2].Value.ToString() + "Seçili kayıtı Silmek istiyormusnuz ?", "Uyarı", MessageBoxButtons.YesNo);
                if (x == DialogResult.Yes)
                {
                    baglan();
                    System.Data.DataTable dt1 = new System.Data.DataTable();
                    OleDbCommand Cmd1 = new OleDbCommand("Delete From Sayac where Kimlik=" + dataGridView1.CurrentRow.Cells[5].Value + "", baglanti);
                    OleDbDataAdapter da1 = new OleDbDataAdapter(Cmd1);
                    da1.Fill(dt1);
                    da1.Dispose();
                    dt1.Dispose();
                    baglanti.Close();
                    MessageBox.Show("Seçili Kayıt Silindi");
                    dataGridView1.DataSource = null;
                    dataGridView1.Rows.Clear();
                    dtst.Clear();
                    sqlcek();
                    grafikciz();
                    grafikciz2();

                }
                else
                {
                    MessageBox.Show("Silme İşlem, İptal edildi...");
                }
            }
            catch (Exception hata)
            {
            }
        }
Beispiel #6
0
    protected void add_catagroy()
    {
        ddl_complaint.Items.Clear();
        System.Data.DataTable dt_item  = new System.Data.DataTable();
        MySqlDataAdapter      cmd_item = new MySqlDataAdapter("SELECT  category FROM `pay_complaint_category` ", d.con);

        d.con.Open();
        try
        {
            cmd_item.Fill(dt_item);
            if (dt_item.Rows.Count > 0)
            {
                ddl_complaint.DataSource = dt_item;
                // ddl_category.DataTextField = dt_item.Columns[1].ToString();
                ddl_complaint.DataValueField = dt_item.Columns[0].ToString();
                ddl_complaint.DataBind();
            }
            dt_item.Dispose();

            d.con.Close();
            ddl_complaint.Items.Insert(0, "Select");
        }
        catch (Exception ex) { throw ex; }
        finally
        {
            d.con.Close();
        }
    }
Beispiel #7
0
        public void AddIventoryItem()
        {
            string connectionString = WebConfigurationManager.AppSettings["ConnectionString"];

            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                DataTable dt = new DataTable();
                SqlCommand command = new SqlCommand(Resources.Resource.AddOnInventory, connection);
                command.Parameters.AddWithValue("@Categoria", Categoria);
                command.Parameters.AddWithValue("@NomeFornecedor", NomeFornecedor);
                command.Parameters.AddWithValue("@NumeroDoc", NumeroDoc);
                command.Parameters.AddWithValue("@NomeProduto", NomeProduto);
                command.Parameters.AddWithValue("@Valor", Valor);
                command.Parameters.AddWithValue("@Observacao", Observacao);
                command.Parameters.AddWithValue("@DataCriacao", DateTime.Now);

                try
                {
                    connection.Open();
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    dt.Dispose();
                    connection.Close();
                }
            }
        }
        public void LlenarGridPermisos()
        {
            /*SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("strConnection"));*/
            string conexion = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;

            SqlConnection cnn;
            cnn = new SqlConnection(conexion);
            cnn.Open();

            SqlCommand comando = new SqlCommand("SP_Catalgo_Permisos", cnn);
            comando.CommandType = CommandType.StoredProcedure;
            comando.Parameters.AddWithValue("@Opcion", "LISTADO");

            SqlDataAdapter da = new SqlDataAdapter(comando);
            DataTable ds = new DataTable();
            da.Fill(ds);
            Grid_Permisos.Visible = true;
            Grid_Permisos.DataSource = ds;
            Grid_Permisos.Columns[0].Visible = true;
            Grid_Permisos.Columns[1].Visible = true;
            Grid_Permisos.DataBind();
            Grid_Permisos.Columns[0].Visible = false;
            ds.Dispose();
            da.Dispose();
        }
        protected void ButtonSubmit_Click(object sender, EventArgs e)
        {
            string InstanceName = TextBoxSource.Text;

            //List Catalogs(DBs)
            string strMDX = "select * from $system.DBSCHEMA_CATALOGS";
            string strConnString = "Datasource=" + InstanceName;

            System.Data.DataTable dtDB = GetDataTable(strConnString, strMDX); //returns data table with DB details

            //Enumerate cubes in each database
            ArrayList DBList = ReturnValueFromDataTable(dtDB, 0); //Get DB Name (Catalog_Name) from data table
            dtDB.Dispose();

            System.Data.DataTable dtCubes = new System.Data.DataTable();
            System.Data.DataTable dtPartial = new System.Data.DataTable();

            foreach (string DBName in DBList)
            {
                strMDX = "SELECT * FROM $system.MDSchema_Cubes WHERE CUBE_SOURCE=1";
                strConnString = "Datasource=" + TextBoxSource.Text + ";Catalog=" + DBName;
                dtPartial = GetDataTable(strConnString, strMDX); //get all Cubes for the DB DBName
                DataColumn CubeLink = dtPartial.Columns.Add("Link"); // Add a column "Link" in the data table
                CubeLink.SetOrdinal(0); //make new column as first column in table

                dtCubes.Merge(dtPartial);
                dtPartial.Dispose();
            }

            GridViewResults.AutoGenerateColumns = true;
            GridViewResults.DataSource = dtCubes; //returns data table with DB details
            GridViewResults.DataBind();
            dtCubes.Dispose();
        }
Beispiel #10
0
 /// <summary>
 /// 获取Excel工作薄中Sheet页(工作表)名集合
 /// </summary>
 /// <param name="excelFile">Excel文件名及路径,EG:C:\Users\JK\Desktop\导入测试.xls</param>
 /// <returns>Sheet页名称集合</returns>
 private String[] GetExcelSheetNames(string fileName)
 {
     // 清理
     if (objConn != null)
     {
         objConn.Close();
         objConn.Dispose();
     }
     System.Data.DataTable dt = null;
     try
     {
         string connString = string.Empty;
         string FileType   = fileName.Substring(fileName.LastIndexOf("."));
         if (FileType == ".xls")
         {
             connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                          "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
         }
         else//.xlsx
         {
             connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
         }
         // 创建连接对象
         objConn = new OleDbConnection(connString);
         // 打开数据库连接
         objConn.Open();
         // 得到包含数据架构的数据表
         dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
         if (dt == null)
         {
             return(null);
         }
         String[] excelSheets = new String[dt.Rows.Count];
         int      i           = 0;
         // 添加工作表名称到字符串数组
         foreach (DataRow row in dt.Rows)
         {
             string strSheetTableName = row["TABLE_NAME"].ToString();
             //过滤无效SheetName
             if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
             {
                 excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
             }
             i++;
         }
         return(excelSheets);
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.ToString());
         return(null);
     }
     finally
     {
         if (dt != null)
         {
             dt.Dispose();
         }
     }
 }
        private void button2_Click(object sender, EventArgs e)
        {
            DataTable tb = new DataTable();

            SQLiteDataAdapter ad = new SQLiteDataAdapter("select distinct max(serialNo) + 1 from StockPrice", ConfigurationSettings.AppSettings["connectionString"]);

            ad.Fill(tb);
            string SerialNo = tb.Rows[0][0].ToString().Trim();
            tb.Dispose();
            tb = new DataTable();
            ad = new SQLiteDataAdapter("select StockId from Stock",ConfigurationSettings.AppSettings["connectionString"]);

            ad.Fill(tb);

            StockProcess  s = null;
            System.Threading.Thread t = null;

            foreach (DataRow r in tb.Rows)
            {
                s = new StockProcess();
                s.SerialNo = SerialNo;
                s.股票代號 = r["StockId"].ToString().Trim();
                t = new System.Threading.Thread(s.Process);
                t.Start();
                System.Threading.Thread.Sleep(200);
            }
        }
Beispiel #12
0
 public int AddInfoModel(DataTable dt, string TableName)
 {
     int num;
     string str = "";
     string str2 = "";
     for (num = 0; num < dt.Rows.Count; num++)
     {
         if ((dt.Rows.Count - 1) == num)
         {
             str = str + "[" + dt.Rows[num]["FieldName"].ToString() + "])";
             str2 = str2 + "@" + dt.Rows[num]["FieldName"].ToString() + ");select scope_identity()";
         }
         else
         {
             str = str + "[" + dt.Rows[num]["FieldName"].ToString() + "],";
             str2 = str2 + "@" + dt.Rows[num]["FieldName"].ToString() + ",";
         }
     }
     StringBuilder builder = new StringBuilder();
     builder.Append("insert into " + TableName + "(");
     builder.Append(str);
     builder.Append(" values (");
     builder.Append(str2);
     SqlParameter[] commandParameters = new SqlParameter[dt.Rows.Count];
     for (num = 0; num < dt.Rows.Count; num++)
     {
         commandParameters[num] = new SqlParameter("@" + dt.Rows[num]["FieldName"].ToString(), dt.Rows[num]["FieldValue"].ToString());
     }
     int num2 = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringKy, CommandType.Text, builder.ToString(), commandParameters));
     dt.Clear();
     dt.Dispose();
     return num2;
 }
Beispiel #13
0
        public void AddOrder() 
        {
            string connectionString = WebConfigurationManager.AppSettings["ConnectionString"];

            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                DataTable dt = new DataTable();
                SqlCommand command = new SqlCommand(Resources.Resource.InsertOrder, connection);
                command.Parameters.AddWithValue("@ID", ID);
                command.Parameters.AddWithValue("@Cliente", Cliente);
                command.Parameters.AddWithValue("@Telefone", Telefone);
                command.Parameters.AddWithValue("@Pedido", Pedido);
                command.Parameters.AddWithValue("@DataPedido", DateTime.Now);

                try
                {
                    connection.Open();
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    dt.Dispose();
                    connection.Close();
                }
            }
        }
Beispiel #14
0
    //''' <summary>
    //''' 檢查帳號是否已存在
    //''' </summary>
    //''' <param name="Uid"></param>
    //''' <param name="CurrentCde"></param>
    //''' <returns></returns>
    //''' <remarks></remarks>
    Boolean ChkUsrUidExist(String Uid, String CurrentCde)
    {
        IzDataSource IzDataSource = new IzDataSource();
        Boolean      Tmp          = false;

        if (CurrentCde == "")
        {
            IzDataSource.SelectString = "select tbUsrCde from tbUsr where tbUsrUid=@tbUsrUid";
            IzDataSource.ParametersAdd("tbUsrUid", Uid);
            System.Data.DataTable tb = IzDataSource.SelectDataTable();
            if (tb.Rows.Count > 0)
            {
                Tmp = true;
            }
            tb.Dispose();
        }
        else
        {
            IzDataSource.SelectString = "select tbUsrCde from tbUsr where tbUsrUid=@tbUsrUid and tbUsrCde<>@tbUsrCde";
            IzDataSource.ParametersAdd("tbUsrUid", Uid);
            IzDataSource.ParametersAdd("tbUsrCde", CurrentCde);
            System.Data.DataTable tb = IzDataSource.SelectDataTable();
            if (tb.Rows.Count > 0)
            {
                Tmp = true;
            }
            tb.Dispose();
        }

        IzDataSource.Dispose();
        return(Tmp);
    }
Beispiel #15
0
    protected void ddl_state_labour_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddl_labour_branch.Items.Clear();
        System.Data.DataTable dt_item  = new System.Data.DataTable();
        MySqlDataAdapter      cmd_item = new MySqlDataAdapter("Select CONCAT((SELECT DISTINCT(STATE_CODE) FROM pay_state_master WHERE STATE_NAME = pay_unit_master.STATE_NAME),'_',UNIT_NAME,'_',UNIT_ADD1) as UNIT_NAME, unit_code,flag from pay_unit_master where state_name = '" + ddl_labour_state.SelectedValue + "' and comp_code='" + Session["comp_code"] + "' and client_code = '" + ddl_labour_client.SelectedValue + "' AND UNIT_CODE in(select UNIT_CODE from pay_client_state_role_grade where  COMP_CODE='" + Session["COMP_CODE"].ToString() + "' AND  EMP_CODE in (" + Session["REPORTING_EMP_SERIES"].ToString() + ") AND client_code='" + ddl_labour_client.SelectedValue + "') AND branch_status = 0  ORDER BY 1", d.con);

        d.con.Open();
        try
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "CallMyFunction", "unblock()", true);
            cmd_item.Fill(dt_item);
            if (dt_item.Rows.Count > 0)
            {
                ddl_labour_branch.DataSource     = dt_item;
                ddl_labour_branch.DataTextField  = dt_item.Columns[0].ToString();
                ddl_labour_branch.DataValueField = dt_item.Columns[1].ToString();
                ddl_labour_branch.DataBind();
            }
            dt_item.Dispose();
            d.con.Close();
            //vikas
            ddl_labour_branch.Items.Insert(0, "Select");
        }
        catch (Exception ex) { throw ex; }
        finally
        {
            d.con.Close();
        }
    }
        public static DataRowCollection GetQueryResults(string query, Dictionary<string, object> parameters, bool log = true)
        {
            DataRowCollection retResults;

            using(SqlConnection s = GetActiveConnection())
            {
                SqlCommand sQuery = new SqlCommand();
                sQuery.Connection = s;
                sQuery.CommandText = query;
                sQuery.CommandType = CommandType.Text;

                foreach(KeyValuePair<string, object> i in parameters)
                {
                    sQuery.Parameters.AddWithValue(i.Key, i.Value);
                }

                SqlDataReader sReader = sQuery.ExecuteReader();
                DataTable dTable = new DataTable();

                dTable.Load(sReader);
                retResults = dTable.Rows;

                sReader.Close();
                dTable.Dispose();
            }

            if(log) Logging.Log("Query {0} successfully executed ({1} Parameters)", query, parameters.Count);
            return retResults;
        }
Beispiel #17
0
        /// <summary>
        /// Execute Array List
        /// </summary>
        /// <param name="commandType">Command Type</param>
        /// <param name="mySqlList">Sql List</param>
        /// <returns></returns>
        public static ArrayList ExecuteArrayList(CommandType cmdType, List<SqlList> mySqlList)
        {
            SqlCommand cmd = new SqlCommand();
            ArrayList al = new ArrayList();
            DataTable dt = null;

            int i = 0;

            using (SqlConnection con = new SqlConnection(sqlconn))
            {
                foreach (SqlList item in mySqlList)
                {
                    PrepareCommand(cmd, con, null, cmdType, item.strSQL, item.myPrams);
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);

                    try
                    {
                        dt = new DataTable();
                        adp.Fill(dt);
                        i = i + 1;
                        if (dt.Rows.Count > 0)
                            al.Add(dt.Rows[0].ItemArray);
                        dt.Dispose();
                        cmd.Parameters.Clear();
                    }
                    finally
                    {
                        adp.Dispose();
                    }
                }
            }
            return al;
        }
Beispiel #18
0
        public static async Task<List<double>> RetriveTaxesByType(string taxName)
        {
            List<double> taxList = new List<double>();
            string sqlCmdString = "Select tax_amount From dbo.taxes Where tax_name=@tax_name";
            Database myDb = new Database();
            SqlCommand sqlCmd = new SqlCommand(sqlCmdString, myDb.Connection);
            SqlDataAdapter sqlAdapt = new SqlDataAdapter(sqlCmd);
            DataTable table = new DataTable();
            double tax;

            await Task.Run(() =>
            {
                try
                {
                    myDb.OpenConnection();
                    sqlCmd.Parameters.Add(new SqlParameter("@tax_name", taxName));
                    sqlAdapt.Fill(table);

                    foreach (DataRow row in table.Rows)
                    {
                        tax = Convert.ToDouble(row["tax_amount"]);
                        taxList.Add(tax);
                    }
                }
                finally
                {
                    sqlAdapt.Dispose();
                    table.Dispose();
                    myDb.CloseConnection();
                }
            });
            
            return taxList;
        }
Beispiel #19
0
 public System.Data.DataRow GetDataRow(DbCommand SelectCommand)
 {
     System.Data.DataRow   objDataRow   = null;
     System.Data.DataTable objDataTable = null;
     try
     {
         objDataTable = this.GetDataTable(SelectCommand);
         if (objDataTable != null)
         {
             if (objDataTable.Rows.Count > 0)
             {
                 objDataRow = objDataTable.Rows[0];
             }
         }
     }
     catch (Exception ex)
     {
         throw new Exception("GetDataRow: " + ex.Message);
     }
     finally
     {
         if (objDataTable != null)
         {
             objDataTable.Dispose(); objDataTable = null;
         }
     }
     return(objDataRow);
 }
        protected void cbp_SaveOrg_Callback(object sender, CallbackEventArgsBase e)
        {
            FormsIdentity id = (FormsIdentity)HttpContext.Current.User.Identity;
            FormsAuthenticationTicket ticket = id.Ticket;

            if (!ticket.Expired)
            {
                DataTable Dt = new DataTable();

                try
                {
                    FormTVP(ref Dt);
                    PopulateTVP(ref Dt);

                    string err = OrganisasiMethods.SP_LoadUpdate_Organisation(ref Dt, ticket.Name, "SAVE");

                    if (err == "")
                    { cbp_SaveOrg.JSProperties["cpErrMsg"] = string.Empty; }
                    else
                    { cbp_SaveOrg.JSProperties["cpErrMsg"] = ConfigurationManager.AppSettings["ErrorMessageStaringSentenceDBError"].ToString() + " " + err; }
                }
                catch (Exception err)
                { cbp_SaveOrg.JSProperties["cpErrMsg"] = ConfigurationManager.AppSettings["ErrorMessageStaringSentenceCaughtByExceptions"].ToString() + " " + err.Message; }
                finally
                { Dt.Dispose(); }
            }
            else
            { cbp_SaveOrg.JSProperties["cpErrMsg"] = "Session Expired."; }
        }
Beispiel #21
0
    //''' <summary>
    //''' 取排序最新一碼
    //''' </summary>
    //''' <param name="TableNme">Table 名稱</param>
    //''' <param name="SrtNme">排序欄位名稱</param>
    //''' <param name="Typ">A:全欄位,B:前幾碼,"":不選</param>
    //''' <param name="TypFdNme">篩選欄位</param>
    //''' <param name="TypFdVal">篩選資料</param>
    //''' <returns></returns>
    //''' <remarks></remarks>
    public Int32 getNextSrt(string TableNme, string SrtNme, string Typ = "", string TypFdNme = "", string TypFdVal = "")
    {
        int          Tmp          = 1;
        IzDataSource IzDataSource = new IzDataSource();

        if (Typ == "A")
        { //'全欄位
            IzDataSource.SelectString = "select top 1 " + SrtNme + " from " + TableNme + " where " + TypFdNme + "='" + TypFdVal + "' order by " + SrtNme + " desc";
        }
        else if (Typ == "B")
        { //'前幾碼
            int Num = TypFdVal.Length;
            IzDataSource.SelectString = "select top 1 " + SrtNme + " from " + TableNme + " where left(" + TypFdNme + "," + Num + ")='" + TypFdVal + "' order by " + SrtNme + " desc";
        }
        else
        {
            IzDataSource.SelectString = "select top 1 " + SrtNme + " from " + TableNme + " order by " + SrtNme + " desc";
        }

        System.Data.DataTable tb = IzDataSource.SelectDataTable();
        if (tb.Rows.Count > 0)
        {
            Tmp = Convert.ToInt32(tb.Rows[0][0]) + 10;
        }
        tb.Dispose();
        IzDataSource.Dispose();

        return(Tmp);
    }
Beispiel #22
0
    //''' <summary>
    //''' 產生下一位數類別編號
    //''' </summary>
    //''' <param name="Tid">前三碼</param>
    //''' <param name="TidNum">數字位數</param>
    //''' <returns></returns>
    //''' <remarks></remarks>
    public String getTypNexTid(String Tid, int TidNum)
    {
        IZCls.StringAccess StringAccess = new IZCls.StringAccess();
        String             StrTmp       = "";
        IzDataSource       IzDataSource = new IzDataSource();

        IzDataSource.SelectString = "select top 1 tbTypTid from tbTyp where left(tbTypTid," + Tid.Length + ")=@tbTypTopCde order by tbTypTid desc";
        IzDataSource.ParametersClear();
        IzDataSource.ParametersAdd("tbTypTopCde", Tid);
        System.Data.DataTable tb = IzDataSource.SelectDataTable();
        if (tb.Rows.Count > 0)
        {
            //String TidTNum  =  Right("" & tb.Rows(0).Item("tbTypTid"), Len("" & tb.Rows(0).Item("tbTypTid")) - Len(Tid));
            String TidTNum = tb.Rows[0]["tbTypTid"].ToString().Substring(tb.Rows[0]["tbTypTid"].ToString().IndexOf(Tid) + Tid.Length);
            if (StringAccess.IsNum(TidTNum) == true)
            {
                StrTmp = Tid + StringAccess.addZeroBfStr(Convert.ToInt32(TidTNum) + 1, TidNum);
            }
            else
            {
                StrTmp = Tid + StringAccess.addZeroBfStr(1, TidNum);
            }
        }
        else
        {
            StrTmp = Tid + StringAccess.addZeroBfStr(1, TidNum);
        }
        tb.Dispose();
        IzDataSource.Dispose();
        return(StrTmp);
    }
        protected void cbp_UploadImage_Loader_Callback(object sender, DevExpress.Web.CallbackEventArgsBase e)
        {
            DataTable Dt = new DataTable();

            try
            {
                string err = ImageUploaderMethods.ImageInfo(RowId.Text, ref Dt);

                if (err == string.Empty)
                {
                    if (Dt.Rows.Count == 1)
                    {
                        ImageFileUserName.Text = Dt.Rows[0]["ImageFileUserName"].ToString();
                        UploadedBy.Text = Dt.Rows[0]["UploadedBy"].ToString();
                        Notes.Text = Dt.Rows[0]["Notes"].ToString();
                        UploadedDate.Text = Convert.ToDateTime(Dt.Rows[0]["UploadedDate"]).ToString("dd/MM/yyyy HH:mm");
                        CurrImage.ImageUrl = Path.Combine(ConfigurationManager.AppSettings["ImageUploadFolder"].ToString(), Dt.Rows[0]["ImageFileSystemName"].ToString());
                        cbp_UploadImage_Loader.JSProperties["cpErrMsg"] = string.Empty;
                    }
                    else
                    { cbp_UploadImage_Loader.JSProperties["cpErrMsg"] = "Sistem tidak dapat membuka info gambar yang telah dipilih."; }
                }
                else
                { cbp_UploadImage_Loader.JSProperties["cpErrMsg"] = err; }
            }
            catch (Exception err)
            {
                cbp_UploadImage_Loader.JSProperties["cpErrMsg"] = err.Message;
            }
            finally
            { Dt.Dispose(); }
        }
        protected void cbp_UploadDocument_Loader_Callback(object sender, DevExpress.Web.CallbackEventArgsBase e)
        {
            DataTable Dt = new DataTable();

            try
            {
                string err = DocumentUploaderMethods.DocumentInfo(RowId.Text, ref Dt);

                if (err == string.Empty)
                {
                    if (Dt.Rows.Count == 1)
                    {
                        DocumentFileUserName.Text = Dt.Rows[0]["DocumentFileUserName"].ToString();
                        UploadedBy.Text = Dt.Rows[0]["UploadedBy"].ToString();
                        Notes.Text = Dt.Rows[0]["Notes"].ToString();
                        UploadedDate.Text = Convert.ToDateTime(Dt.Rows[0]["UploadedDate"]).ToString("dd/MM/yyyy HH:mm");
                        cbp_UploadDocument_Loader.JSProperties["cpErrMsg"] = string.Empty;
                    }
                    else
                    { cbp_UploadDocument_Loader.JSProperties["cpErrMsg"] = "Sistem tidak dapat membuka info gambar yang telah dipilih."; }
                }
                else
                { cbp_UploadDocument_Loader.JSProperties["cpErrMsg"] = err; }
            }
            catch (Exception err)
            {
                cbp_UploadDocument_Loader.JSProperties["cpErrMsg"] = err.Message;
            }
            finally
            { Dt.Dispose(); }
        }
        public static void nseCompanyCodes()
        {
            StreamReader sr = new StreamReader(@"C:\\Documents and Settings\\Amey\\Desktop\\EQUITY_L.csv");
            DataTable nsedata = new DataTable();
            DataColumn companyName = new DataColumn("CompanyName");
            nsedata.Columns.Add(companyName);
            DataColumn nsecode = new DataColumn("NSE Code");
            nsedata.Columns.Add(nsecode);
            DataRow row;
            string[] values = null;
            sr.ReadLine();
            while (!sr.EndOfStream)
            {
                values = sr.ReadLine().Split(',');
                row = nsedata.NewRow();
                row[companyName] = values[1];
                row[nsecode] = values[0];
                nsedata.Rows.Add(row);

            }
            CommonFunctions.Write(nsedata, "nsecompanies.txt");
            sr.Close();
            nsedata.Dispose();
            companyName.Dispose();
            nsecode.Dispose();
        }
 public void Loaddata()
 {
     try
     {
         ConnectDB.MoKetNoi();
         da = new SqlDataAdapter("select * from GroupClothes", ConnectDB.connect);
         dt = new DataTable();
         da.Fill(dt);
         if (dt.Rows.Count > 0)
         {
             grvgroupclothes.DataSource = dt;
             grvgroupclothes.DataBind();
         }
         else
         {
             lblerror.Text = "Loi";
         }
     }
     catch (Exception ex)
     {
         lblerror.Text = "Loi" + ex.Message;
     }
     finally
     {
         ConnectDB.DongKetNoi();
         da.Dispose();
         dt.Dispose();
     }
 }
Beispiel #27
0
    //''' <summary>
    //''' 取功能權限字串
    //''' </summary>
    //''' <param name="UsrCde"></param>
    //''' <param name="MnuCde"></param>
    //''' <returns></returns>
    //''' <remarks></remarks>
    public String GetPow(String UsrCde, String MnuCde)
    {
        String tmp = "";

        if (UsrCde == "USR00000000000000001")
        {
            tmp = "BOW01,BOW02,BOW03,ADD,EDIT,DEL";
        }
        else
        {
            IzDataSource IzDataSource = new IzDataSource();

            IzDataSource.SelectString = "select * from rlGrpMnu where tbMnuCde=@tbMnuCde and tbGrpCde in (select tbGrpCde from rlUsrGrp where tbUsrCde=@tbUsrCde)";
            IzDataSource.ParametersClear();
            IzDataSource.ParametersAdd("tbMnuCde", MnuCde);
            IzDataSource.ParametersAdd("tbUsrCde", UsrCde);

            System.Data.DataTable tb = IzDataSource.SelectDataTable();
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                tmp += "" + tb.Rows[i]["tbMnuPow"].ToString().Trim() + ",";
            }
            tb.Dispose();
            IzDataSource.Dispose();
        }


        return(tmp);
    }
Beispiel #28
0
        /// <summary>
        /// 取得 VDS_CRM_ORDER_MAIN && VDS_CRM_ORDER_DETL 的 Data
        /// </summary>
        /// <param name="ParameterList"></param>
        /// <returns>回傳查詢結果</returns>
        public DataSet QUERY_CRM041_MAIN_AND_DETL(ArrayList ParameterList)
        {
            try
            {
                DataSet ds_Return = new DataSet();
                DataTable dt_Return_Main = new DataTable();
                DataTable dt_Return_Detl = new DataTable();

                DBO.VDS_CRM_ORDER_MAINDBO VCOM = new VDS_CRM_ORDER_MAINDBO(ref USEDB);
                DBO.VDS_CRM_ORDER_DETLDBO VCOD = new VDS_CRM_ORDER_DETLDBO(ref USEDB);

                dt_Return_Main = VCOM.QUERY_CRM041_MAIN(ParameterList);
                dt_Return_Main.TableName = "VDS_CRM_ORDER_MAIN";

                dt_Return_Detl = VCOD.QUERY_CRM041_DETL(ParameterList);
                dt_Return_Detl.TableName = "VDS_CRM_ORDER_DETL";

                ds_Return.Tables.Add(dt_Return_Main.Copy());
                ds_Return.Tables.Add(dt_Return_Detl.Copy());

                dt_Return_Main.Clear();
                dt_Return_Detl.Clear();
                dt_Return_Main.Dispose();
                dt_Return_Detl.Dispose();

                return ds_Return;

            }
            catch (Exception ex)
            { throw GetNewException(ex); }
        }
Beispiel #29
0
 private void Button2_Click(object sender, EventArgs e)
 {
     try
     {
         DialogResult x = MessageBox.Show(listBox1.Items[listBox1.SelectedIndex].ToString() + " Adlı Seçili kayıtı Silmek istiyormusnuz ?", "Uyarı", MessageBoxButtons.YesNo);
         if (x == DialogResult.Yes)
         {
             baglan();
             System.Data.DataTable dt1  = new System.Data.DataTable();
             OleDbCommand          Cmd1 = new OleDbCommand("Delete From Kisiler where Isim=" + listBox1.Items[listBox1.SelectedIndex].ToString() + "", baglanti);
             OleDbDataAdapter      da1  = new OleDbDataAdapter(Cmd1);
             da1.Fill(dt1);
             da1.Dispose();
             dt1.Dispose();
             baglanti.Close();
             MessageBox.Show("Seçili Kayıt Silindi");
         }
         else
         {
             MessageBox.Show("Silme İşlem, İptal edildi...");
         }
     }
     catch (Exception hata)
     {
     }
 }
Beispiel #30
0
        private void btnNew_Click( object sender, EventArgs e )
        {
            if( Program.UserType == UserType.Customer )
                return;

            AddDlg dlg = new AddDlg();
            if( dlg.ShowDialog( this ) != DialogResult.OK )
                return;

            RefillComboBoxes();

            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "select top 1 * from WedDress order by [ID] desc";
            cmd.Connection = Program.Database;
            cmd.CommandType = CommandType.Text;

            OleDbDataAdapter oda = new OleDbDataAdapter( cmd);
            DataTable dt = new DataTable();
            oda.Fill( dt );

            dsDress.Tables["WedDress"].Merge( dt );
            dt.Dispose();
            oda.Dispose();
            cmd.Dispose();
        }
Beispiel #31
0
		public static DataTable ConvertTableType(DataTable dt, Dictionary<string, Type> types)
		{
			DataTable newDt = dt.Clone();
			//convert all columns' datatype

			newDt.TableName = dt.TableName;

			foreach (DataColumn dc in newDt.Columns)
			{
				if (types.ContainsKey(dc.ColumnName))
				{
					dc.DataType = types[dc.ColumnName];
				}
			}

			//import data from original table
			//foreach (DataRow dr in dt.Rows)
			//{
			//	newDt.ImportRow(dr);
			//}

			FillData(dt, newDt);

			dt.Dispose();
			return newDt;
		}
        protected void Page_Load(object sender, System.EventArgs e)
        {
            // �ڴ˴������û������Գ�ʼ��ҳ��
            DataTable dt = new DataTable();
            string sql = "Select semployeeid,semployeename from FCS_EMPLOYEE ";
            DBConn myCn= new DBConn();

            myCn.DBOpen();
            OleDbCommand myCommand = new OleDbCommand(sql,new OleDbConnection(myCn.strConn));
            OleDbDataAdapter sdap = new OleDbDataAdapter(myCommand);
            sdap.Fill(dt);
            myCn.DBClose();

            StringBuilder sb = new StringBuilder();
            sb.Append("<table cellpadding=\"0\" id=\"tab\" name=\"tab\" cellspacing=\"0\"  height=\"25\" width=\"100%\" frame=border>");

            if (dt.Rows.Count >0)
            {
                foreach(DataRow iRow in dt.Rows)
                {
                    sb.Append("<tr class=\"alternate2\" >");
                    sb.Append("<td width = \"100px\">"+ iRow["semployeename"].ToString()+"</td>");
                    sb.Append("<td><input type='checkbox' id='chkDisabled' name='chkDisabled'>&nbsp;����&nbsp;&nbsp;&nbsp;&nbsp;<input type='checkbox' id='chkDisplay' name='chkDisplay'>&nbsp;����&nbsp;&nbsp;&nbsp;&nbsp;<input type='checkbox' id='chkRead' name='chkRead'>&nbsp;ֻ��</td>");
                    sb.Append("</tr>");
                }
            }
            sb.Append("</table>");
            if (dt != null)
                dt.Dispose();
            div1.InnerHtml = sb.ToString();
        }
Beispiel #33
0
        public void search_usr()
        {
            string query = "";

            if ((fname_txt.Text.Length == 0) && (lname_txt.Text.Length == 0))
            {
                query = "select id,user_id,first_name,last_name,usr_name,role_name,mob_no,usr_address,email_id from user_view where role_id=" + role_drop.SelectedIndex;
            }
            else if ((fname_txt.Text.Length != 0) && (role_drop.SelectedIndex != 0))
            {
                query = "select id,user_id,first_name,last_name,usr_name,role_name,mob_no,usr_address,email_id from user_view where first_name='" + fname_txt.Text.Trim() + "' and role_id=" + role_drop.SelectedIndex;
            }
            else if (fname_txt.Text.Length != 0)
            {
                query = "select id,user_id,first_name,last_name,usr_name,role_name,mob_no,usr_address,email_id from user_view where first_name='" + fname_txt.Text.Trim() + "'";
            }
            else
            {
                query = "select id,user_id,first_name,last_name,usr_name,role_name,mob_no,usr_address,email_id from user_view where first_name='" + fname_txt.Text.Trim() + "' and last_name='" + lname_txt.Text.Trim() + "' and role_id=" + role_drop.SelectedIndex;
            }

            try
            {
                SqlDataAdapter ad = new SqlDataAdapter(query, impfunc.cnn);
                DataTable      dt = new System.Data.DataTable();
                ad.Fill(dt);
                usr_grd.DataSource = dt;
                // this.dataGridView1.Columns[0].Visible = false;
                ad.Dispose(); dt.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Something goes wrong, please close the software and open again.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Beispiel #34
0
        protected void ButtonSubmit_Click(object sender, EventArgs e)
        {
            string InstanceName = TextBoxSource.Text;

            //List Catalogs(DBs)
            string strMDX        = "select * from $system.DBSCHEMA_CATALOGS";
            string strConnString = "Datasource=" + InstanceName;

            System.Data.DataTable dtDB = GetDataTable(strConnString, strMDX); //returns data table with DB details

            //Enumerate cubes in each database
            ArrayList DBList = ReturnValueFromDataTable(dtDB, 0); //Get DB Name (Catalog_Name) from data table

            dtDB.Dispose();

            System.Data.DataTable dtCubes   = new System.Data.DataTable();
            System.Data.DataTable dtPartial = new System.Data.DataTable();

            foreach (string DBName in DBList)
            {
                strMDX        = "SELECT * FROM $system.MDSchema_Cubes WHERE CUBE_SOURCE=1";
                strConnString = "Datasource=" + TextBoxSource.Text + ";Catalog=" + DBName;
                dtPartial     = GetDataTable(strConnString, strMDX); //get all Cubes for the DB DBName
                DataColumn CubeLink = dtPartial.Columns.Add("Link"); // Add a column "Link" in the data table
                CubeLink.SetOrdinal(0);                              //make new column as first column in table

                dtCubes.Merge(dtPartial);
                dtPartial.Dispose();
            }

            GridViewResults.AutoGenerateColumns = true;
            GridViewResults.DataSource          = dtCubes; //returns data table with DB details
            GridViewResults.DataBind();
            dtCubes.Dispose();
        }
        /// <summary>
        /// Authenticates the user.
        /// </summary>
        /// <param name="userName">Name of the user.</param>
        /// <param name="password">The password.</param>
        /// <returns></returns>
        public bool AuthenticateUser(string userName, string password)
        {
            bool success = false;
            DataTable requestTable = new DataTable();
            using (IDbConnection conn = UserDataProvider.GetAuthenticationConnection())
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select UserName, Password from users where UserName = @Username and Password = @Password";
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(UserDataProvider.AddParameterWithValue("@UserName",userName));
                cmd.Parameters.Add(UserDataProvider.AddParameterWithValue("@Password", password));
                IDataReader reader = cmd.ExecuteReader();

                using (reader)
                {
                    requestTable.Load(reader);                    
                }
            }
            if (requestTable.Rows.Count==1 )
            {
                success = true;
            }
            requestTable.Dispose();
            return success;
        }
Beispiel #36
0
        /// <summary>
        /// Devuelve un DataTable con la información del Archivo Xlsx y Hoja deseadas
        /// </summary>
        /// <param name="pRuta"></param>
        /// <param name="pHoja"></param>
        /// <returns></returns>
        public static System.Data.DataTable getDataXlsToDt(string pRuta, string pHoja, string pCols = "*", string pRngo = "")
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                if (!string.IsNullOrEmpty(pRuta) && !string.IsNullOrEmpty(pHoja))
                {
                    setConnection(pRuta);
                    setDataAdapter(pHoja, pCols, pRngo);
                    myDataAdapter.Fill(dt);
                }
                return(dt);
            }
            catch (Exception ex)
            {
                dt = new System.Data.DataTable();
                return(dt);

                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close(); conn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
Beispiel #37
0
 public string GetReturnString(string p_SqlString)
 {
     try
     {
         Open();
         SqlDataAdapter        l_DataAdapter = new SqlDataAdapter(p_SqlString, cn);
         System.Data.DataTable l_DataTable   = new System.Data.DataTable();
         l_DataAdapter.Fill(l_DataTable);
         l_DataAdapter.Dispose();
         if (cn.State == ConnectionState.Open)
         {
             Close();
         }
         if (l_DataTable.Rows.Count > 0)
         {
             string l_count = l_DataTable.Rows[0].ItemArray.GetValue(0).ToString();
             l_DataTable.Dispose();
             return(l_count);
         }
         else
         {
             return("");
         }
     }
     catch (Exception E)
     {
         writeTxtCls wtc = new writeTxtCls();
         wtc.writeTxtToFile("\r\n[" + System.DateTime.Now.ToString() + "]执行RunSqlReturn方法[" + p_SqlString + "]语句发生问题:" + E.Message);
         return("");
     }
 }
        private static void ImportIDs(long[] IDs, SqlCommand command)
        {
            long start = DateTime.Now.Ticks;

            DataTable t = new DataTable();
            DataColumn c = new DataColumn("ID", typeof(long));
            t.Columns.Add(c);
            DataColumn c2 = new DataColumn("seq_nr", typeof(long));
            c2.AutoIncrementSeed = 1;
            c2.AutoIncrementStep = 1;
            c2.AutoIncrement = true;
            t.Columns.Add(c2);

            foreach (long id in IDs)
            {
                DataRow dr = t.NewRow();
                dr[0] = id;
                t.Rows.Add(dr);
            }

            command.CommandText = "IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL " +
                                    "DROP TABLE #temp " +
                                    "CREATE TABLE #temp(ID BIGINT, seq_nr BIGINT); CREATE INDEX ix ON #temp (ID);";
            command.ExecuteNonQuery();

            SqlBulkCopy copy = new SqlBulkCopy(command.Connection, SqlBulkCopyOptions.UseInternalTransaction, null);
            copy.DestinationTableName = "#temp";
            copy.WriteToServer(t);

            t.Dispose();
            Debug.WriteLine("<<ImportIDs>> " + new TimeSpan(DateTime.Now.Ticks - start).ToString());
        }
Beispiel #39
0
        //讀取Excel中的所有表名
        //读取Excel文件时,可能一个文件中会有多个Sheet,因此获取Sheet的名称是非常有用的

        /// <summary>
        /// 根据Excel物理路径获取Excel文件中所有表名,列名是TABLE_NAME
        /// </summary>
        /// <param name="excelFile">Excel物理路径</param>
        /// <returns>DataTable</returns>
        public static System.Data.DataTable GetExcelSheetNames2DataTable(string excelFile)
        {
            OleDbConnection objConn = null;

            System.Data.DataTable dt = null;

            try
            {
                string strConn = GetExcelConnectionString(excelFile);
                objConn = new OleDbConnection(strConn);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return(null);
                }
                return(dt);
            }
            catch (Exception ee)
            {
                throw new Exception(ee.Message);
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
Beispiel #40
0
    public void client_list()
    {
        d.con1.Close();

        ddl_labour_client.Items.Clear();
        System.Data.DataTable dt_item  = new System.Data.DataTable();
        MySqlDataAdapter      cmd_item = new MySqlDataAdapter("SELECT DISTINCT pay_client_master.client_code, client_NAME FROM pay_client_master INNER JOIN pay_client_state_role_grade ON pay_client_master.COMP_CODE = pay_client_state_role_grade.COMP_CODE and pay_client_master.client_code = pay_client_state_role_grade.client_code WHERE pay_client_master.comp_code = '" + Session["COMP_CODE"].ToString() + "' AND pay_client_state_role_grade.emp_code IN (" + Session["REPORTING_EMP_SERIES"] + ")", d.con1);

        d.con1.Open();
        try
        {
            cmd_item.Fill(dt_item);
            if (dt_item.Rows.Count > 0)
            {
                ddl_labour_client.DataSource     = dt_item;
                ddl_labour_client.DataTextField  = dt_item.Columns[1].ToString();
                ddl_labour_client.DataValueField = dt_item.Columns[0].ToString();
                ddl_labour_client.DataBind();
            }
            dt_item.Dispose();
            d.con1.Close();

            ddl_labour_client.Items.Insert(0, "Select");
        }
        catch (Exception ex) { throw ex; }
        finally
        {
            d.con1.Close();
        }
    }
Beispiel #41
0
    /*
     *
     * private void ExportExcel1() {
     *  HttpResponse response = HttpContext.Current.Response;
     *  String FileName = "NewExcel.xls";
     *  response.Clear();
     *  response.ClearContent();
     *  response.ClearHeaders();
     *  response.Buffer = true;
     *  response.ContentType = "application/ms-excel";
     *  response.AddHeader("content-disposition", "attachment;filename="+ FileName );
     *  response.AddHeader("Content-Type", "application/Excel");
     *  response.Charset = "";
     *
     *  using (StringWriter sw = new StringWriter())
     *  {
     *      HtmlTextWriter hw = new HtmlTextWriter(sw);
     *
     *      //To Export all pages
     *      NewGeneratedBill.AllowPaging = false;
     *      // this.LoadLatestBillData();
     *
     *      NewGeneratedBill.HeaderRow.BackColor = System.Drawing.Color.White;
     *      foreach (TableCell cell in NewGeneratedBill.HeaderRow.Cells)
     *      {
     *          cell.BackColor = NewGeneratedBill.HeaderStyle.BackColor;
     *      }
     *      foreach (GridViewRow row in NewGeneratedBill.Rows)
     *      {
     *          row.BackColor = System.Drawing.Color.White;
     *          foreach (TableCell cell in row.Cells)
     *          {
     *              if (row.RowIndex % 2 == 0)
     *              {
     *                  cell.BackColor = NewGeneratedBill.AlternatingRowStyle.BackColor;
     *              }
     *              else
     *              {
     *                  cell.BackColor = NewGeneratedBill.RowStyle.BackColor;
     *              }
     *              cell.CssClass = "textmode";
     *          }
     *      }
     *
     *      NewGeneratedBill.RenderControl(hw);
     *
     *      //style to format numbers to string
     *      //string style = @"<style> .textmode { } </style>";
     *      //response.Write(style);
     *      response.Output.Write(sw.ToString());
     *      Response.Flush();
     *      Response.End();
     *  }
     * }
     *
     * private void ExportExcel2() {
     *  Response.Clear();
     *  Response.ClearContent();
     * //   Response.ContentType = "application/octet-stream";
     * //   Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xls");
     *
     *
     *  NewGeneratedBill.RenderControl(new HtmlTextWriter(Response.Output));
     *
     *  Response.Flush();
     *  Response.End();
     *
     * }
     *
     * private void ExportExcel3() {
     *
     * }
     */

    public void DataTableToExcel()
    {
        try
        {
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.ClearHeaders();
            response.ClearContent();
            response.Charset = Encoding.UTF8.WebName;
            response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");
            response.AddHeader("Content-Type", "application/Excel");
            response.ContentType = "application/vnd.xlsx";
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    tableGeneratedBill.TableName = "Sheet1";
                    GridView gridView = new GridView();
                    gridView.DataSource = tableGeneratedBill;
                    gridView.DataBind();
                    gridView.RenderControl(htw);
                    response.Write(sw.ToString());
                    gridView.Dispose();
                    tableGeneratedBill.Dispose();
                    response.End();
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception();
        }
    }
Beispiel #42
0
 DataTable IConsulta.CarregaDadosMae(string idUnit)
 {
     DataTable table2;
     StringBuilder builder = new StringBuilder();
     DataTable table = new DataTable();
     try
     {
         builder.Length = 0;
         builder.AppendLine("EXECUTE INTOUCH.DBO.[BIINTOUCH.CARREGAINFORMACOES_MAE] ");
         builder.AppendLine("@ID_UNIT = '" + idUnit + "' ");
         table = DbIntouch.ExecuteDataTable(builder.ToString());
         table2 = table.Copy();
     }
     catch
     {
         table2 = null;
     }
     finally
     {
         if (table != null)
         {
             table.Dispose();
         }
     }
     return table2;
 }
        private void timtheotenbn() // Tìm kiếm dữ liệu theo mã
        {
            Ketnoi(); // Tạo kết nối file Access
            // Tạo Command
            SqlCommand objcommand = new SqlCommand();
            objcommand.Connection = objcon;
            objcommand.CommandType = CommandType.Text;
            objcommand.CommandText = "Select * From tbbenhnhan Where tenbn LIKE '%" + txttimtheotenbn.Text + "%' ORDER BY tenbn";
            //Tạo đối tượng Adapter
            SqlDataAdapter objAdapter = new SqlDataAdapter();
            objAdapter.SelectCommand = objcommand;
            // Tạo DataTable nhận dữ liệu trả về
            DataTable objDataTable = new DataTable("abcd");
            objAdapter.Fill(objDataTable);
            // Gán dữ liệu vào dataGrid
            dgwtim.DataSource = objDataTable;
            // Hủy các đối tượng
            objcommand.Dispose();
            objcommand = null;
            objDataTable.Dispose();
            objDataTable = null;
            objAdapter.Dispose();
            objAdapter = null;
            // hủy kết nối

        }
Beispiel #44
0
        public Dictionary <string, string> getItems()
        {
            Dictionary <string, string> items = new Dictionary <string, string>();
            SQLiteConnection            conn  = getConnection();

            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                SQLiteCommand cmd;
                //conn.Open();  //Initiate connection to the db

                cmd             = conn.CreateCommand();
                cmd.CommandText = QUERY_ITEMS;
                ad = new SQLiteDataAdapter(cmd);
                ad.Fill(dt); //fill the datasource
                DataRow[] rows = dt.Select();
                for (int i = 0; i < rows.Length; i++)
                {
                    items.Add((string)rows[i][Constants.ITEMS_1], (string)rows[i][Constants.ITEMS_2]);
                }
                ad.Dispose();
                dt.Dispose();
                rows = null;
            }
            catch (SQLiteException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            sqlite = null;
            return(items);
        }
        private String[] GetExcelSheetNames(string excelFile)
        {
            OleDbConnection objConn = null;

            System.Data.DataTable dt = null;

            try
            {
                // Connection String. Change the excel file to the file you
                // will search.
                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                    "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
                // Create connection object by using the preceding connection string.
                objConn = new OleDbConnection(connString);
                // Open connection with the database.
                objConn.Open();
                // Get the data table containg the schema guid.
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return(null);
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int      i           = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                // Loop through all of the sheets if you want too...
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Query each excel sheet.
                }

                return(excelSheets);
            }
            catch (Exception ex)
            {
                return(null);
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
Beispiel #46
0
        public Dictionary <string, string[]> getAreas()
        {
            Dictionary <string, string[]> areas = new Dictionary <string, string[]>();
            SQLiteConnection conn = getConnection();

            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                SQLiteCommand cmd;
                //conn.Open();  //Initiate connection to the db

                cmd             = conn.CreateCommand();
                cmd.CommandText = QUERY_AREA;
                ad = new SQLiteDataAdapter(cmd);
                dt = new System.Data.DataTable();
                ad.Fill(dt); //fill the datasource
                DataRow[] rows = dt.Select();
                for (int i = 0; i < rows.Length; i++)
                {
                    string[] marca_agrup = new string[2];
                    marca_agrup[0] = (string)rows[i][Constants.AREA_2];
                    marca_agrup[1] = (string)rows[i][Constants.AREA_3];
                    areas.Add((string)rows[i][Constants.AREA_1], marca_agrup);
                }
                ad.Dispose();
                dt.Dispose();
            }
            catch (SQLiteException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            sqlite = null;

            return(areas);
        }
Beispiel #47
0
        public ArrayList getEERRs()
        {
            ArrayList        eerrs = new ArrayList();
            SQLiteConnection conn  = getConnection();

            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                SQLiteCommand cmd;
                //conn.Open();  //Initiate connection to the db

                cmd             = conn.CreateCommand();
                cmd.CommandText = QUERY_EERR;  //set the passed query
                ad = new SQLiteDataAdapter(cmd);
                dt = new System.Data.DataTable();
                ad.Fill(dt); //fill the datasource
                DataRow[] rows = dt.Select();
                for (int i = 0; i < rows.Length; i++)
                {
                    string[] prefix_desc = new string[2];
                    prefix_desc[0] = (string)rows[i][Constants.EERR_1];
                    prefix_desc[1] = (string)rows[i][Constants.EERR_2];
                    eerrs.Add(prefix_desc);
                }
                ad.Dispose();
                dt.Dispose();
            }
            catch (SQLiteException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            sqlite = null;
            return(eerrs);
        }
Beispiel #48
0
        /// <summary>
        /// 根据Excel物理路径获取Excel文件中所有表名
        /// </summary>
        /// <param name="excelFile">Excel物理路径</param>
        /// <returns>String[]</returns>
        public static String[] GetExcelSheetNames(string excelFile)
        {
            System.Data.DataTable dt = null;

            try
            {
                dt = GetExcelSheetNames2DataTable(excelFile);
                if (dt == null)
                {
                    return(null);
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int      i           = 0;
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                return(excelSheets);
            }
            catch (Exception ee)
            {
                throw new Exception(ee.Message);
            }
            finally
            {
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
Beispiel #49
0
    //''' <summary>
    //'''  取得指定類別的類別名稱組(以逗號分開)忽略啟用
    //''' </summary>
    //''' <param name="TopCdeS">TYP(前置三碼)</param>
    //''' <param name="TypTidStr">T001,T002,T003(可複選)</param>
    //''' <returns></returns>
    //''' <remarks></remarks>
    public String getTypSubA(String TopCdeS, String TypTidStr)
    {
        IZCls.WebFormBase WebFormBase = new IZCls.WebFormBase();
        IZCls.DataAccess  DataAccess  = new IZCls.DataAccess();
        TypTidStr = DataAccess.ClsSqlChr(TypTidStr);
        String TypTidSql = WebFormBase.DotToSqlStr(TypTidStr);


        IzDataSource IzDataSource = new IzDataSource();

        IzDataSource.SelectString = "select tbTypSub from tbTyp where left(tbTypCde,3)=@TopCdeS and tbTypTid in (" + TypTidSql + ")";
        IzDataSource.ParametersClear();
        IzDataSource.ParametersAdd("TopCdeS", TopCdeS);
        System.Data.DataTable tb = IzDataSource.SelectDataTable();
        String StrTmp            = "";

        for (int i = 0; i < tb.Rows.Count; i++)
        {
            if (StrTmp == "")
            {
                StrTmp = tb.Rows[i]["tbTypSub"].ToString();
            }
            else
            {
                StrTmp += "," + tb.Rows[i]["tbTypSub"].ToString();
            }
        }
        tb.Dispose();
        IzDataSource.Dispose();
        return(StrTmp);
    }
Beispiel #50
0
        public static void setPlanBerorFastighetCache()
        {
            //string conStr = ConfigurationManager.AppSettings["OracleOleDBConString"].ToString();
            string sql = string.Empty;

            sql = "SELECT TO_CHAR(plan_id) AS nyckel, fastighet_id AS nyckel_fastighet, UPPER(fastighet) AS fastighet " +
                  "FROM   gis_v_planberorfastighet";

            DataTable dtPlanBerorFastighet = new DataTable();
            //OleDbConnection con = new OleDbConnection(conStr);
            OleDbConnection con = UtilityDatabase.GetOleDbConncection();
            OleDbCommand com = new OleDbCommand(sql, con);
            OleDbDataReader dr;

            com.Connection.Open();
            dr = com.ExecuteReader();

            dtPlanBerorFastighet.Load(dr);

            dr.Close();
            dr.Dispose();

            DateTime cacheExpiration = setCacheExpiration();

            // Skapa cach av alla planer
            Cache cache = HttpRuntime.Cache;
            cache.Insert("PlanBerorFastighet", dtPlanBerorFastighet, null, cacheExpiration, Cache.NoSlidingExpiration);

            dtPlanBerorFastighet.Dispose();
            con.Close();
            con.Dispose();
        }
Beispiel #51
0
        public async Task<object> SelectWithDetail(oCurriculum_academic curriacadata)
        {
            DBConnector d = new DBConnector();
            if (!d.SQLConnect())
                return WebApiApplication.CONNECTDBERRSTRING;
            List<Questionare_set_detail> result = new List<Questionare_set_detail>();

            curri_id = curriacadata.curri_id;
            aca_year = curriacadata.aca_year;
            d.iCommand.CommandText = getSelectByCurriculumAcademicCommand();

            try
            {
                System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
                if (res.HasRows)
                {
                    DataTable data = new DataTable();
                    data.Load(res);
                    foreach (DataRow item in data.Rows)
                    {
                        int qid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]);
                        if (result.FirstOrDefault(t => t.questionare_set_id == qid) == null)
                        {
                                result.Add(new Questionare_set_detail
                                {
                                    aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
                                    name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
                                    personnel_id = item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]) : 0,
                                    questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]),
                                    date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
                                    curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
                                    t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
                                });
                        }
                        result.First(t => t.questionare_set_id == qid).target.Add(new User_type {
                            user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID].Ordinal]),
                            user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString()
                        });
                    }
                    data.Dispose();
                }
                else
                {
                    //Reserved for return error string
                }
                res.Close();
            }
            catch (Exception ex)
            {
                //Handle error from sql execution
                return ex.Message;
            }
            finally
            {
                //Whether it success or not it must close connection in order to end block
                d.SQLDisconnect();
            }
            return result;
        }
Beispiel #52
0
        /// <summary>
        /// 获取excel文件里面的所有的工作表名称
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        static List <string> GetExcelSheetNames(string filePath)
        {
            OleDbConnection connection = null;

            System.Data.DataTable dt = null;
            try
            {//获取文件扩展名
                string strExtension = System.IO.Path.GetExtension(filePath);
                switch (strExtension)
                {
                case ".xls":
                    connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                    break;

                case ".xlsx":
                    connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");
                    //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
                    //"HDR=yes;"是说Excel文件的第一行是列名而不是数,"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
                    break;

                default:
                    connection = null;
                    break;
                }
                connection.Open();
                dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return(new List <string>());
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int      i           = 0;
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString().Split('$')[0];
                    i++;
                }
                return(excelSheets.Distinct().ToList());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return(new List <string>());
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
 public string getAccountNo(string empID)
 {
     dTable = new DataTable();
     dTable = getData(empID);
     string getName = dTable.Rows[0]["BankAccNo"].ToString();
     dTable.Dispose();
     return getName;
 }
 public string getBloodGroup(string empID)
 {
     dTable = new DataTable();
     dTable = getData(empID);
     string getName = dTable.Rows[0]["BloodGroupName"].ToString();
     dTable.Dispose();
     return getName;
 }
Beispiel #55
0
        }//fim método

        #endregion

        #region Pega o nome das Folhas do Arquivo Excel, para aqueles casos que não localiza o nome das páginas internas do Excel
        // Utilize o namespace  System.Data.OleDb;
        public String[] PegarNomesFolhasExcel(string caminhoExcel)
        {
            OleDbConnection objConn = null;

            System.Data.DataTable dt = null;

            try
            {
                // Configura a Connection String
                String connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\";", caminhoExcel);

                // Cria o objeto de conexão usando a connection string
                objConn = new OleDbConnection(connString);

                // Abre a conexão com o banco de dados
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return(null);
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int      i           = 0;

                // Adiciona os nomes na array
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                // Loop através de todas as folhas se você quiser também..
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Consultar cada folha de excel
                }

                return(excelSheets);
            }
            catch (Exception ex)
            {
                return(null);
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
        //private void CreateActivationCode(string tableName, string filePath)
        //{
        //    XmlDataDocument xmlDoc = new XmlDataDocument();
        //    SqlCommand cmd = new SqlCommand();

        //    try
        //    {
        //        using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        //        {
        //            if (fs != null)
        //            {
        //                xmlDoc.Load(fs);
        //                XmlNodeList NodeList = xmlDoc.GetElementsByTagName("Item");
        //                for (int i = 0; i < NodeList.Count; i++)
        //                {
        //                    //string ID = NodeList[i];
        //                    cmd = SetParameters(tableName, NodeList[i]);
        //                    ExecuteInsertSQL(cmd);
        //                    if (!string.IsNullOrEmpty(this.Error.Text.ToString()))
        //                    {
        //                        continue;
        //                    }
        //                }
        //                if (string.IsNullOrEmpty(this.Error.Text.ToString()))
        //                {
        //                    MessageBox.Show("upload successfully..", "Upload Data", MessageBoxButton.OK);
        //                }
        //            }
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        this.Error.Text = ex.Message;
        //    }
        //    finally
        //    {
        //        cmd.Dispose();
        //    }
        //}


        private string CreateXml(string tableName, string path)
        {
            System.Data.DataTable dt = null;
            string sheetName         = Constants.Activation;

            dt = InitializedtSalesEngineerDataTable();
            if (dt != null)
            {
                //'Item' is tagName in xml
                dt.TableName = "Item";
            }

            InitializeWorkbook(path);
            ISheet sheet = xssfworkbook.GetSheet(sheetName);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();



            if (dt == null)
            {
                return(null);
            }
            //start at line 2
            rows.MoveNext();

            while (rows.MoveNext())
            {
                IRow    row = (XSSFRow)rows.Current;
                DataRow dr  = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                if (dr[0].ToString() != "")
                {
                    dr[0] = dr[0].ToString().Trim().ToLower();
                    dr[3] = str2HexStr(dr[0].ToString().Trim().ToLower() + " " + dr[1].ToString().Trim() + " " + dr[2].ToString().Trim());
                    dt.Rows.Add(dr);
                }
            }

            string xmlPath = @"E:\ActivationCodeCreator\" + tableName + DateTime.Now.ToString("yyyyMMdd") + ".xml";

            //create xml file to local
            dt.WriteXml(xmlPath);
            dt.Dispose();
            return(xmlPath);
        }
Beispiel #57
0
        public List<IncorporacionConsultaBE> obtenerPorId(int Id)
        {
            List<IncorporacionConsultaBE> listado = new List<IncorporacionConsultaBE>();

            DataTable dt = new DataTable("ListadoIncorporacionesConsultoras");
            SqlDataAdapter da = new SqlDataAdapter();
            SqlConnection cn = new SqlConnection(connection.getConnectionString());

            try
            {
                cn.Open();
                da = new SqlDataAdapter("usp_bel_incorporacion_obtener_porID", cn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add("@incorporacionID", SqlDbType.Char, 2).Value = Id;
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    IncorporacionConsultaBE ic;

                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        ic = new IncorporacionConsultaBE();

                        ic.incorporacionID = Convert.ToInt32(dt.Rows[j]["incorporacionID"]);
                        ic.consultoraID = (DBNull.Value.Equals(dt.Rows[j]["consultoraID"])) ? 0 : Convert.ToInt32(dt.Rows[j]["consultoraID"]);
                        ic.regionCodigo = Convert.ToString(dt.Rows[j]["region"]).Trim();
                        ic.zonaCodigo = Convert.ToString(dt.Rows[j]["zona"]).Trim();
                        //ic.fechaRegistro = Convert.ToString(dt.Rows[j]["fechaIncorporacion"]).Trim();
                        ic.fechaRegistro = Convert.ToString(dt.Rows[j]["fechaIncorporacion"]); //DateFormatter.getShortTime(Convert.ToDateTime(dt.Rows[j]["fechaIncorporacion"]));
                        ic.campanhaInscripcion = (DBNull.Value.Equals(dt.Rows[j]["campanhaInscripcion"])) ? "" : Convert.ToString(dt.Rows[j]["campanhaInscripcion"]).Trim();
                        ic.numeroDocumento = Convert.ToString(dt.Rows[j]["numeroDocumento"]).Trim();
                        ic.apellidoPaterno = Convert.ToString(dt.Rows[j]["apellidoPaterno"]).Trim();
                        ic.apellidoMaterno = Convert.ToString(dt.Rows[j]["apellidoMaterno"]).Trim();
                        ic.nombres = Convert.ToString(dt.Rows[j]["nombres"]).Trim();
                        ic.consultoraCodigo = (DBNull.Value.Equals(dt.Rows[j]["consultoraCodigo"])) ? "" : Convert.ToString(dt.Rows[j]["consultoraCodigo"]).Trim();
                        ic.estadoVerificado = Convert.ToBoolean(dt.Rows[j]["estadoVerificado"]);
                        ic.estadoActivo = Convert.ToBoolean(dt.Rows[j]["estadoActivo"]);
                        ic.tipo = Convert.ToChar(dt.Rows[j]["tipo"]);

                        listado.Add(ic);
                    }
                }
            }
            catch (Exception ex)
            {
                listado = null;
            }
            finally
            {
                da.Dispose();
                dt.Dispose();
                cn.Close();
                cn.Dispose();
            }

            return listado;
        }
Beispiel #58
0
        public static bool ReadExcel(ref System.Data.DataTable table, String filePath, String fileType)
        {
            String connStr = String.Empty;

            System.Data.DataTable tables = new System.Data.DataTable();
            if (fileType.Equals("xls"))
            {
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            }
            else
            {
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            }

            try
            {
                if (conn == null)
                {
                    //类似于获取数据库连接
                    conn = new OleDbConnection(connStr);
                    //打开连接
                    conn.Open();
                    //获取数据
                    tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { });
                    //判断该文件中是否有sheet
                    if (tables.Rows.Count == 0)
                    {
                        Console.WriteLine("文件中没有可用表");
                        MessageBox.Show("文件中没有可用表", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return(false);
                    }
                }

                //将读取到的数据存入DataTable
                String           firstTableName = tables.Rows[count]["TABLE_NAME"].ToString();
                OleDbCommand     cmd            = new OleDbCommand("select * from [" + firstTableName + "]", conn);
                OleDbDataAdapter apt            = new OleDbDataAdapter(cmd);
                table.Dispose();
                apt.Fill(table);



                count++;
                if (count > table.Rows.Count)
                {
                    conn.Close();
                    conn  = null;
                    count = 0;
                    return(false);
                }
                return(true);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return(false);
            }
        }
Beispiel #59
0
 public void ClearData()
 {
     fields = null;
     if (tblSrc != null)
     {
         tblSrc.Dispose();
         tblSrc = null;
     }
 }
Beispiel #60
0
        private void GetExcelSheetNames(string excelFile, string connString)
        {
            OleDbConnection objConn = null;

            System.Data.DataTable dt = null;

            try
            {
                Dgrid.DataSource = null;
                cboSheet.Items.Clear();
                cboSheet.Text = "";

                objConn = new OleDbConnection(connString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int      i           = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    cboSheet.Items.Add(row["TABLE_NAME"].ToString());
                    i++;
                }

                // Loop through all of the sheets if you want too...
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Query each excel sheet.
                }
                cboSheet.SelectedIndex = 0;
                return;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }