C# System.Data.DataTable.Load方法代码示例

        public void PopulateEmployerGroupDDL()
        {
            dbProcedures db = new dbProcedures();

            try
            {
                System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT DISTINCT EmployerGroup FROM tbl_Members ORDER BY EmployerGroup", new db().SqlConnection);
                sqlCmd.CommandType = System.Data.CommandType.Text;

                System.Data.DataTable loadReaderdata = new System.Data.DataTable();
                loadReaderdata.Load(sqlCmd.ExecuteReader());

                ddlEmployerGroup.DataSource = loadReaderdata;
                ddlEmployerGroup.DataTextField = "EmployerGroup";
                ddlEmployerGroup.DataValueField = "EmployerGroup";
                ddlEmployerGroup.DataBind();

                ddlEmployerGroup.Items.Insert(0, new ListItem("", ""));
                ddlEmployerGroup.SelectedIndex = 0;

            }
            catch (Exception ex)
            {
                db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString());
            }

            db.Close();
        }

        private System.Data.DataTable GetData(int city, string lang)
        {
            try
            {
                SqlConnection myConnection = new SqlConnection("user id=Uib;" +
                                     "password=Uib;server=localhost;" +
                                     "Trusted_Connection=yes;" +
                                     "database=Uib; " +
                                     "connection timeout=30");
                myConnection.Open();
                var command = new SqlCommand("GET_HOTELS_BY_CITY", myConnection);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@city", city);
                command.Parameters.AddWithValue("@lang", lang);
                SqlDataReader reader = command.ExecuteReader();
                var dataTable = new System.Data.DataTable();

                dataTable.Load(reader);
                reader.Close();
                myConnection.Close();
                return dataTable;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            return null;
        }

 /// <summary>
 /// Devuelve un datatable a partir de un excel, debemos seleccionar la pestaña e introducir la columna clave para eliminar filas en blanco
 /// </summary>
 /// <param name="strFile">Path al fichero</param>
 /// <param name="sheetName">Pestaña de la hoja de calculos</param>
 /// <param name="keyColumn">Columna clave se eliminaran todas las filas en las que esta casilla no este valorizada</param>
 /// <returns></returns>
 public static System.Data.DataTable GetDataTableFromExcel(string strFile, string sheetName, string keyColumn = "")
 {
     var con = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;TypeGuessRows=0;HDR=Yes;ImportMixedTypes=Text;'", strFile);
     using (OleDbConnection connection = new OleDbConnection(con))
     {
         connection.Open();
         var command = new OleDbCommand("Select * from [{0}$]".FormatWith(sheetName), connection);
         if (keyColumn.HasValue())
             command = new OleDbCommand("Select * from [{0}$] where {1} is not null".FormatWith(sheetName, keyColumn), connection);
         using (OleDbDataReader dr = command.ExecuteReader())
         {
             var table = new System.Data.DataTable();
             table.Load(dr);
             return table;
         }
     }
 }

        public void FilterResults(Object sender, EventArgs a)
        {
            dbProcedures db = new dbProcedures();

            try
            {
                System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM tbl_Members WHERE Firstname + ' ' + LastName like '%" + txtEmployeeName.Text + "%' AND EmployerGroup like '%" + ddlEmployerGroup.SelectedValue + "%' ORDER BY Username", new db().SqlConnection);
                sqlCmd.CommandType = System.Data.CommandType.Text;

                System.Data.DataTable loadReaderdata = new System.Data.DataTable();
                loadReaderdata.Load(sqlCmd.ExecuteReader());

                GridView1.DataSource = loadReaderdata;
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString());
            }

            db.Close();
        }

        public void MemberSearch(string strUsername)
        {
            dbProcedures db = new dbProcedures();

            try
            {
                System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM tbl_Members WHERE Username like '%" + strUsername + "%' ORDER BY Username", new db().SqlConnection);
                sqlCmd.CommandType = System.Data.CommandType.Text;

                System.Data.DataTable loadReaderdata = new System.Data.DataTable();
                loadReaderdata.Load(sqlCmd.ExecuteReader());

                GridView1.DataSource = loadReaderdata;
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                db.LogAction("", "Error", "Error loading Members UI page. " + ex.Message.ToString());
            }

            db.Close();
        }

        private static QueryData GetQueryData(string procName, string validName, string selectText, string descText, string number, string execText, string projectName, SqlConnection conn)
        {
            Log.Write("Start GetQueryData:" + execText);
            var obj = new QueryData();
            try
            {

                var lstColumns = new List<string>();
                var sql = execText;
                var cmd = new SqlCommand(sql.Replace("\\r\\n", "").Replace("\\r", ""), conn);
                if (cmd.CommandText.StartsWith("EXEC"))
                {
                    cmd.CommandTimeout = 200;
                }
                if (cmd.CommandText.Contains("check_patient"))
                {
                    cmd.CommandTimeout = 90;
                }
                cmd.CommandTimeout = 200;

                var rdrQD = cmd.ExecuteReader();

                if (rdrQD.HasRows)
                {
                    for (int i = 0; i < rdrQD.FieldCount; i++)
                    {
                        lstColumns.Add(rdrQD.GetName(i));
                    }

                    obj.FieldsName = lstColumns;
                    // Получаем сами данные
                    var dtData = new DataTable();
                    dtData.Load(rdrQD);

                    obj.Data = dtData;
                    obj.ProjectName = projectName;
                    obj.ValidationRule = validName;
                    obj.Description = descText;
                    obj.NameList = number;
                }
                else
                {
                    obj = null;
                }

                rdrQD.Close();
                rdrQD = null;

            }
            catch (Exception ex)
            {
                Log.Write(ex);
            }
            finally
            {
                Log.Write("Exit GetQueryData:" + execText);

            }
            return obj;
        }

 /// <summary>
 /// Get task from database
 /// </summary>
 /// <param name="task">Return result</param>
 /// <param name="guid">Task unique guid</param>
 /// <returns>Returns 'true' if operation was successful</returns>
 public bool GetTask(out DbCommon.Task task, Guid guid)
 {
     bool result = false;
     task = null;
     System.Data.DataTable dt = new System.Data.DataTable();
     try
     {
         if (this.OpenDb() &&
             guid != null)
         {
             SQLiteCommand myCommand = new SQLiteCommand(_dbConnection);
             myCommand.CommandText = String.Format("SELECT {0},{1},{2},{3} FROM {4} WHERE {5}='{6}'",
                                                   DbCommon.GUIN_COLUMN,         // {0}
                                                   DbCommon.TEXT_COLUMN,         // {1}
                                                   DbCommon.IMPORTANT_COLUMN,    // {2}
                                                   DbCommon.URGENT_COLUMN,       // {3}
                                                   DbCommon.TABLE_NAME,          // {4}
                                                   DbCommon.GUIN_COLUMN,         // {5}
                                                   guid.ToString());             // {6}
             SQLiteDataReader reader = myCommand.ExecuteReader();
             dt.Load(reader);
             reader.Close();
             result = _dbConnection.ResultCode() == SQLiteErrorCode.Ok;
             this.CloseDb();
             if (dt.Rows.Count > 0 &&
                 dt.Rows[0].ItemArray.Count() > 3)
             {
                 task = new DbCommon.Task(dt.Rows[0].ItemArray[0].ToString(),
                                          dt.Rows[0].ItemArray[1].ToString(),
                                          dt.Rows[0].ItemArray[2].ToString(),
                                          dt.Rows[0].ItemArray[3].ToString());
             }
         }
     }
     catch (Exception exc)
     {
         Console.WriteLine(exc.Message);
         result = false;
     }
     return result;
 }

        /// <summary>
        /// Get tasks from database
        /// </summary>
        /// <param name="important">If tasks are important</param>
        /// <param name="urgent">If tasks are urgent</param>
        /// <param name="tasks">Return result</param>
        /// <returns>Returns 'true' if operation was successful</returns>
        public bool GetAllTasks(out List<DbCommon.Task> tasks, bool? important = null, bool? urgent = null)
        {
            bool result = false;
            tasks = new List<DbCommon.Task>();
            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                if (this.OpenDb())
                {
                    string where = String.Empty;
                    if (important != null)
                    {
                        where =  String.Format(" WHERE {0}='{1}'",
                                               DbCommon.IMPORTANT_COLUMN,   // {0}
                                               important.Value ? "1" : "0");// {1}
                    }
                    if (urgent != null)
                    {
                        if (!String.IsNullOrWhiteSpace(where))
                        {
                            where = String.Format("{0} AND ",
                                                  where);
                        }
                        else
                        {
                            where = " WHERE ";
                        }
                        where = String.Format("{0}{1}='{2}'",
                                              where,                    // {0}
                                              DbCommon.URGENT_COLUMN,   // {1}
                                              urgent.Value ? "1" : "0");// {2}
                    }

                    SQLiteCommand myCommand = new SQLiteCommand(_dbConnection);
                    myCommand.CommandText = String.Format("SELECT {0},{1},{2},{3} FROM {4}{5}",
                                                          DbCommon.GUIN_COLUMN,         // {0}
                                                          DbCommon.TEXT_COLUMN,         // {1}
                                                          DbCommon.IMPORTANT_COLUMN,    // {2}
                                                          DbCommon.URGENT_COLUMN,       // {3}
                                                          DbCommon.TABLE_NAME,          // {4}
                                                          where);                       // {5}
                    SQLiteDataReader reader = myCommand.ExecuteReader();

                    dt.Load(reader);
                    reader.Close();
                    result = _dbConnection.ResultCode() == SQLiteErrorCode.Ok;
                    this.CloseDb();

                    IEnumerator<System.Data.DataRow> enumerator = (IEnumerator<System.Data.DataRow>)dt.Rows.GetEnumerator();
                    while(enumerator.MoveNext())
                    {
                        if (enumerator.Current.ItemArray.Count() > 3)
                        {
                            DbCommon.Task task = new DbCommon.Task(enumerator.Current.ItemArray[0].ToString(),
                                                                   enumerator.Current.ItemArray[1].ToString(),
                                                                   enumerator.Current.ItemArray[2].ToString(),
                                                                   enumerator.Current.ItemArray[3].ToString());
                            tasks.Add(task);
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.Message);
                result = false;
            }
            return result;
        }

        public static System.Data.DataTable FromCache(this DataAccessComponents.DataAccessComponent q, DbCommand commands)
        {
            try
            {
                bool UseCache = false;
                Boolean.TryParse(System.Configuration.ConfigurationManager.AppSettings["UseCache"],out UseCache);
                System.Data.SqlClient.SqlCommand command = commands as System.Data.SqlClient.SqlCommand;
                System.Data.DataTable objCache;
                if (!UseCache)
                {
                    objCache = new System.Data.DataTable();
                    objCache.Load(q.Database.ExecuteReader(command), System.Data.LoadOption.OverwriteChanges);
                    return objCache;
                }

                if (command == null) return null;
                string CacheId = command.CommandText + "?";
                foreach (System.Data.Common.DbParameter dbp in command.Parameters)
                {
                    CacheId += dbp.ParameterName + "=" + dbp.Value.ToString() + "&";
                }

                //if (Environment.StackTrace.Contains("Administrator\\"))
                //{
                //    objCache = q.ToList();
                //    return objCache;
                //}
                //else
                {
                    //    System.Threading.Thread.Sleep(500);
                    objCache = System.Web.HttpRuntime.Cache.Get(CacheId) as System.Data.DataTable;
                }
                if (objCache == null)
                {
                    List<string> tablesNames = q.TableNames; //command.Mapping.GetTables().Where(t => (t.TableNames.Contains("[")) ? CacheId.Contains(t.TableNames.Substring(4)) : CacheId.Contains("[" + t.TableNames.Substring(4) + "]")).Select(t => t.TableNames.Substring(4)).ToList();
                    string connStr = q.Database.ConnectionString; // System.Configuration.ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString;
                    using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr))
                    {
                        conn.Open();
                        System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr);
                        System.Web.Caching.SqlCacheDependency sqldep;
                        AggregateCacheDependency aggDep = new AggregateCacheDependency();
                        foreach (string tableName in tablesNames)
                        {
                            if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(tableName))
                                System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, tableName);
                            if (tableName.Contains("Comment") || tableName.Contains("FormBuilder"))
                                sqldep = new System.Web.Caching.SqlCacheDependency("NatiqNewsPoll", tableName);
                            else
                                sqldep = new System.Web.Caching.SqlCacheDependency("NatiqNews", tableName);

                            aggDep.Add(sqldep);
                        }
                        objCache = new System.Data.DataTable();
                        objCache.Load(q.Database.ExecuteReader(command), System.Data.LoadOption.OverwriteChanges);
                        if (objCache != null)
                            System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, aggDep, DateTime.Now.AddDays(1), System.Web.Caching.Cache.NoSlidingExpiration);
                    }

                }
                //Return the created (or cached) List
                return objCache;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 public static System.Data.DataTable Obtener(string sql, params object[] parametros)
 {
     Conectar();
     try
     {
         System.Data.DataTable dt = new System.Data.DataTable();
         System.Data.OleDb.OleDbCommand com = Comando(sql, parametros);
         com.Transaction = transaccion;
         System.Data.OleDb.OleDbDataReader r = com.ExecuteReader();
         dt.Load(r);
         return dt;
     }
     catch (System.Exception ex)
     {
         throw new System.Exception("Conexion: Error al obtener.", ex);
     }
     finally
     {
         Desconectar();
     }
 }

        public HttpResponseMessage GetDailyLog(int page, double UTCOffset, int rows, string sidx, string sord, string filters = "", string devicefilters = "", string deviceIds = "")
        {
            //condition
            string condition = null;
            string condition2 = null; //存放KEYWARD條件
            ArrayList al = null;

            if (!string.IsNullOrEmpty(devicefilters))
            {
                System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
                jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(devicefilters);
                al = new System.Collections.ArrayList(f.rules.Count);
                foreach (jqGridUtil.Rule rule in f.rules)
                {
                    if (!string.IsNullOrEmpty(rule.data))
                    {
                        al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op));
                    }
                }
                condition = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray());
            }
            else if(!string.IsNullOrEmpty(deviceIds))
            {
                condition = string.Format("DeviceID in ({0})", deviceIds);
            }

            if (!string.IsNullOrEmpty(filters))
            {
                System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
                jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(filters);
                al = new System.Collections.ArrayList(f.rules.Count);
                foreach (jqGridUtil.Rule rule in f.rules)
                {
                    if (!string.IsNullOrEmpty(rule.data))
                    {
                        al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op));
                    }
                }
                if ( al.Count>0)
                {
                    //condition = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray()) + " and " + condition;
                    condition2 = string.Join(string.Format(" {0} ", f.groupOp), al.ToArray());
                    
                }

                
                
            }

            //if (!string.IsNullOrEmpty(filters))
            //{
            //    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            //    jqGridUtil.Filters f = serializer.Deserialize<jqGridUtil.Filters>(filters);
            //    al = new System.Collections.ArrayList(f.rules.Count);
            //    foreach (jqGridUtil.Rule rule in f.rules)
            //    {
            //        if (!string.IsNullOrEmpty(rule.data))
            //        {
            //            al.Add(jqGridUtil.GetSearchString(rule.field, rule.data, rule.op));
            //        }
            //    }
            //    condition += string.Join(string.Format(" {0} ", f.groupOp), al.ToArray());

            //}

            try
            {
                al = new ArrayList(1);
                al.Add(condition);
                al.Add(condition2);//新增車機過濾條件
                int totalCount = DataProvider.Instance().GetCount_DailyLog(al);

                al = new ArrayList();
                al.Add(page);
                al.Add(rows);
                al.Add(sidx);
                al.Add(sord);
                al.Add(condition);
                al.Add(condition2);
                System.Data.IDataReader dr = DataProvider.Instance().FetchAll_DailyLog(al);
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Load(dr);

                int totalPage = 0;
                if (totalCount > 0)
                {
                    totalPage = totalCount / rows;
                    if ((totalCount % rows) > 0) totalPage += 1;
                }
                else { totalPage = 0; }
                if (page > totalPage) page = totalPage;

                // 產生JSON 字串
                List<Row> rowlist = new List<Row>() { };
                int i = 0;
                foreach (System.Data.DataRow item in dt.Rows)
                {
                    Row rowobj = new Row();
                    var stringList = new List<string>();
                    rowobj.id = i;
//.........这里部分代码省略.........

        //TODO Lobby Changed. From
        /*
        public System.Data.DataTable SelectCards(string[] conditions)
        {
          var sb = new StringBuilder();
          sb.Append("SELECT * FROM Card");
          if (conditions != null)
          {
        string connector = " WHERE ";
        foreach (string condition in conditions)
        {
          sb.Append(connector);
          sb.Append("(");
          sb.Append(condition);
          sb.Append(")");
          connector = " AND ";
        }
          }
          using (var conn = new VistaDB.Provider.VistaDBConnection(db))
          {
        var cmd = new VistaDB.Provider.VistaDBCommand();
        cmd.Connection = conn;
        cmd.CommandText = sb.ToString();
        var result = new System.Data.DataTable();
        result.Load(cmd.ExecuteReader());
        return result;
          }
        }
           */
        //TO
        public System.Data.DataTable SelectCards(string[] conditions)
        {
            var sb = new StringBuilder("SELECT * FROM Card");
            List<String> sets = new List<String>();

            if (conditions != null)
            {
            string connector;

            connector = " ( ";
            int i = 0;
            bool foundSet = false;
            sb = new StringBuilder();
            foreach (string condition in conditions)
            {
                if (condition.Substring(0, 10).Equals("Card.setId"))
                {
                    sb.Append(connector);
                    sb.Append("(");
                    sb.Append(condition);
                    sb.Append(")");
                    connector = " OR ";
                    conditions[i] = "";
                    foundSet = true;
                }
                i++;
            }
            sb.Append(")");
            if (foundSet)
            {
                Array.Resize<string>(ref conditions, conditions.Length + 1);
                conditions[conditions.Length - 1] = sb.ToString();
            }
            sb = new StringBuilder("SELECT * FROM Card");
            connector = " WHERE ";
            foreach (string condition in conditions)
            {
                if (!condition.Trim().Equals(""))
                {
                    sb.Append(connector);
                    sb.Append("(");
                    sb.Append(condition);
                    sb.Append(")");
                    connector = " AND ";
                }
            }
            }
            using (var conn = new VistaDB.Provider.VistaDBConnection(db))
            {
            var cmd = new VistaDB.Provider.VistaDBCommand();
            cmd.Connection = conn;
            cmd.CommandText = sb.ToString();
            var result = new System.Data.DataTable();
            result.Load(cmd.ExecuteReader());
            return result;
            }
        }

        /// <summary>
        /// Method that uploads the data from the MySqlDataReader that contains the data.
        /// </summary>
        /// <param name="reader">Data reader that contains the source data that to be uploaded</param>
        public void Upload(MySqlDataReader reader)
        {

            if (reader.HasRows)
            {
                System.Data.DataTable table = new System.Data.DataTable();
                table.Load(reader);
                Upload(table);
            }
        }

        public bool VendorCopyProduct(ArrayList execSql, ArrayList specs, string selMaster, string moveMaster, string movePrice)
        {
            MySqlCommand mySqlCmd = new MySqlCommand();
            MySqlConnection mySqlConn = new MySqlConnection(strConn);
            try
            {
                if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
                {
                    mySqlConn.Open();
                }
                mySqlCmd.Connection = mySqlConn;
                mySqlCmd.Transaction = mySqlConn.BeginTransaction();
                mySqlCmd.CommandType = System.Data.CommandType.Text;
                foreach (string str in execSql)
                {
                    mySqlCmd.CommandText = str;
                    mySqlCmd.ExecuteNonQuery();
                }
                SerialDao serialDao = new SerialDao("");
                foreach (string str in specs)
                {
                    mySqlCmd.CommandText = serialDao.Update(18);//規格編號
                    int specId = Convert.ToInt32(mySqlCmd.ExecuteScalar());

                    mySqlCmd.CommandText = string.Format(str, specId);
                    mySqlCmd.ExecuteNonQuery();
                }

                if (!string.IsNullOrEmpty(selMaster) && !string.IsNullOrEmpty(moveMaster) && !string.IsNullOrEmpty(movePrice))
                {
                    mySqlCmd.CommandText = selMaster;
                    MySqlDataReader reader = mySqlCmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                    System.Data.DataTable _dt = new System.Data.DataTable();
                    _dt.Load(reader);
                    if (_dt == null)
                    {
                        mySqlCmd.Transaction.Rollback();
                        return false;
                    }
                    else
                    {
                        foreach (System.Data.DataRow item in _dt.Rows)
                        {
                            mySqlCmd.CommandText = string.Format(moveMaster, item["price_master_id"]);
                            int masterId = Convert.ToInt32(mySqlCmd.ExecuteScalar());
                            mySqlCmd.CommandText = string.Format(movePrice, masterId, item["price_master_id"]);
                            mySqlCmd.ExecuteNonQuery();
                        }
                    }
                }
                mySqlCmd.Transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                mySqlCmd.Transaction.Rollback();
                throw new Exception("ProductTempDao.VendorCopyProduct-->" + ex.Message, ex);
            }
            finally
            {
                if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
                {
                    mySqlConn.Close();
                }
            }
        }

 public System.Data.DataTable SelectCards(string[] conditions)
 {
     var sb = new StringBuilder();
     sb.Append("SELECT * FROM Card");
     if(conditions != null)
     {
         string connector = " WHERE ";
         foreach(string condition in conditions)
         {
             sb.Append(connector);
             sb.Append("(");
             sb.Append(condition);
             sb.Append(")");
             connector = " AND ";
         }
     }
     using(var conn = new VistaDB.Provider.VistaDBConnection(db))
     {
         var cmd = new VistaDB.Provider.VistaDBCommand();
         cmd.Connection = conn;
         cmd.CommandText = sb.ToString();
         var result = new System.Data.DataTable();
         result.Load(cmd.ExecuteReader());
         return result;
     }
 }