C# SqlCommand.Read方法代码示例

 private static object PullSql(string conn, string db, string query, string error, SQLReturn type)
 {
     var result = new object();
     try
     {
         using (var c = new SqlConnection(conn))
         {
             c.Open();
             c.ChangeDatabase(db);
             SqlDataReader reader = new SqlCommand(query, c).ExecuteReader();
             switch (type)
             {
                 case SQLReturn.Bool:
                     result = false;
                     while (reader.Read())
                     {
                         if (reader != null)
                         {
                             result = true;
                             break;
                         }
                     }
                     break;
                 case SQLReturn.Item:
                     result = String.Empty;
                     while (reader.Read())
                     {
                         if (reader != null)
                         {
                             result = reader[0].ToString();
                             break;
                         }
                     }
                     break;
                 case SQLReturn.List:
                     var Results = new List<string>();
                     while (reader.Read())
                     {
                         Results.Add(reader[0].ToString());
                     }
                     result = Results;
                     break;
             }
         }
     }
     catch (SqlException e)
     {
         Messaging.ThrowException(error, e);
     }
     catch (Exception e)
     {
         Messaging.ThrowException(error, e);
     }
     return result;
 }

        public List<AccountBase> FillAccountBaseFromSql()
        {
            var Accounts= new List<AccountBase>();

            using(var myConnection = new SqlConnection(Connect.sTalismanConStr))
            {
                myConnection.Open();
                string sSql = "Select top 1 * from existing_clients";
                var Reader = new SqlCommand(sSql, myConnection).ExecuteReader();
                while (Reader.Read())
                {
                    var AccountBase = new AccountBase();
                    AccountBase.sAccountName = Reader["sName"].ToString();
                    AccountBase.sAddressLine1 = Reader["sAddressLine1"].ToString();
                    AccountBase.sAddressLine2 = Reader["sAddressLine2"].ToString();
                    AccountBase.sAddressLine3 = Reader["sAddressLine3"].ToString();
                    AccountBase.sCustomerNumber = Reader["sClientNumber"].ToString();
                    AccountBase.sEmail = Reader["sEmail"].ToString();
                    AccountBase.sPostCode = Reader["sPostalCode"].ToString();
                    AccountBase.sTelephone = Reader["sTelephone"].ToString();
                    //MessageBox.Show(AccountBase.sAccountName);
                    //MessageBox.Show(AccountBase.sTelephone);
                    Accounts.Add(AccountBase);
                }
                Reader.Close();
                myConnection.Close();
            }

            return Accounts;
        }

        private List<string> GetNodeMatches(string lookupText)
        {
            List<string> foundNodes = new List<string>();

            string sConn = ConfigurationManager.ConnectionStrings["EEREDB"].ToString();
            sConn += System.Text.Encoding.UTF8.GetString(System.Convert.FromBase64String(ConfigurationManager.AppSettings["word"].ToString()));

            using (SqlConnection conn = new SqlConnection(sConn))
            {
                conn.Open();
                using (SqlDataReader reader = new SqlCommand("SELECT DrupalNode FROM dbo.NodeLookup WHERE WebURL LIKE '%" + lookupText + "%' ", conn).ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            foundNodes.Add(reader.GetString(0));
                        }
                    }
                }
                conn.Close();
            }

            return foundNodes;
        }

 public virtual object[] getDataAry(string argString)
 {
     this.rState = 0;
     string cmdText = argString;
     object[] objArray = null;
     try
     {
         SqlConnection connection = new SqlConnection(this.connStr);
         connection.Open();
         SqlDataReader reader = new SqlCommand(cmdText, connection).ExecuteReader();
         int fieldCount = reader.FieldCount;
         object[,] objArray2 = null;
         while (reader.Read())
         {
             objArray2 = new object[fieldCount, 2];
             for (int i = 0; i < fieldCount; i++)
             {
                 objArray2[i, 0] = reader.GetName(i);
                 objArray2[i, 1] = reader.GetValue(i);
             }
             objArray = cls.mergeAry(objArray, objArray2);
         }
         reader.Close();
         connection.Close();
     }
     catch (Exception exception)
     {
         this.rState = 1;
         objArray = null;
         this.eMessage = exception.Message;
     }
     return objArray;
 }

 protected override void PopulateVersionAndTypes()
 {
     base._sqlVersion = "10";
     List<KeyValuePair<int, byte>> list = new List<KeyValuePair<int, byte>>();
     using (SqlDataReader reader = new SqlCommand("select system_type_id, user_type_id, name from sys.types where system_type_id = user_type_id", base._cx).ExecuteReader())
     {
         while (reader.Read())
         {
             DbTypeInfo dbTypeInfo = SqlSchemaReader.GetDbTypeInfo(reader.GetString(2));
             if (!((dbTypeInfo == null) || base._sqlTypes.ContainsKey(reader.GetInt32(1))))
             {
                 base._sqlTypes.Add(reader.GetInt32(1), dbTypeInfo);
             }
             else
             {
                 list.Add(new KeyValuePair<int, byte>(reader.GetInt32(1), reader.GetByte(0)));
             }
         }
     }
     foreach (KeyValuePair<int, byte> pair in list)
     {
         if (base._sqlTypes.ContainsKey(pair.Value))
         {
             base._sqlTypes[pair.Key] = base._sqlTypes[pair.Value];
         }
     }
 }

 public Method1Form()
 {
     InitializeComponent();
     cboxGroup.SelectedIndex = 1;
     cboxGroup.SelectionChangeCommitted += new EventHandler(btnMethod1_Click);
     using (SqlConnection connection = new SqlConnection(MainForm.STRCONN))
     {
         connection.Open();
         string q = @"SELECT ID FROM dbo.Leagues WHERE Season='2011/12' ORDER BY GuessedPercent DESC";
         using (SqlDataReader reader = new SqlCommand(q, connection).ExecuteReader())
         {
             int i = 1;
             cboxTopLeagues.Items.Add("All Leagues");
             while (reader.Read()) cboxTopLeagues.Items.Add(String.Format("TOP {0}", i++));
             cboxTopLeagues.Items.RemoveAt(cboxTopLeagues.Items.Count - 1);
             if (cboxTopLeagues.Items.Count == 0) return;
             cboxTopLeagues.SelectedIndex = 0;
             cboxTopLeagues.SelectionChangeCommitted += new EventHandler(btnMethod1_Click);
         }
     }
     //browser.AllowWebBrowserDrop = false;
     //browser.IsWebBrowserContextMenuEnabled = false;
     //browser.WebBrowserShortcutsEnabled = false;
     browser.ObjectForScripting = this;
 }

        public void TestInsert()
        {
            var students = StudentHelper.CreateSample(10).ToArray();
            using (var sqlConnection = DatabaseHelper.OpenSqlConnection(DatabaseName))
            {
                var sqlUpserter = new SqlUpserter<Student>(StudentMapper, TableName, students);
                sqlUpserter.Execute(sqlConnection);

                var selectText = "SELECT " + string.Join(",", StudentMapper.Columns.Select(column => column.Name)) +
                                 " FROM " + TableName;
                var reader = new SqlCommand(selectText, sqlConnection).ExecuteReader();
                var row = 0;
                while (reader.Read())
                {
                    var student = students[row];
                    for (var column = 0; column < reader.FieldCount; column++)
                    {
                        var expected = StudentMapper.GetValueAt(column, student);
                        var actual = reader.GetValue(column);
                        Assert.AreEqual(expected, actual);
                    }
                    row++;
                }
            }
        }

        public static void Tweak()
        {
            List<Item> items = new List<Item>();
            HashSet<int> recipes = new HashSet<int>();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TdbConnectionString"].ConnectionString))
            {
                conn.Open();

                using (SqlDataReader reader = new SqlCommand("SELECT * FROM Items WHERE JSON IS NOT NULL", conn).ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["Name"].ToString());
                        using (MemoryStream m = new MemoryStream(Encoding.UTF8.GetBytes(reader["JSON"].ToString())))
                        {
                            DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(Item));
                            items.Add((Item)ser.ReadObject(m));
                        }
                    }
                }

                using (SqlDataReader reader = new SqlCommand("SELECT RecipeId FROM Recipes", conn).ExecuteReader())
                {
                    while (reader.Read())
                    {
                        recipes.Add((int)reader["RecipeId"]);
                    }
                }
            }

            using (SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["TdbConnectionString"].ConnectionString))
            {
                conn2.Open();
                foreach (Item i in items)
                {
                    using (SqlCommand cmd = new SqlCommand("UPDATE Items SET Name = @Name, BindOnPickup = @BindOnPickup, Icon = @Icon, Quality = @Quality, SourceId = @SourceId, SourceType = @SourceType, RecipeId = @RecipeId WHERE ItemId = @ItemId", conn2))
                    {
                        cmd.Parameters.AddWithValue("Name", i.Name);
                        cmd.Parameters.AddWithValue("BindOnPickup", i.ItemBind);
                        cmd.Parameters.AddWithValue("Icon", i.ItemSpells.Count > 0 ? (object)i.ItemSpells.Last().Spell.Icon : !string.IsNullOrWhiteSpace(i.Icon) ? (object)i.Icon : DBNull.Value);
                        cmd.Parameters.AddWithValue("Quality", i.Quality);
                        cmd.Parameters.AddWithValue("SourceId", i.ItemSource.SourceId);
                        cmd.Parameters.AddWithValue("SourceType", i.ItemSource.SourceType);
                        cmd.Parameters.AddWithValue("RecipeId", i.ItemSpells.Any(a => a.Trigger == "ON_LEARN") && recipes.Contains(i.ItemSpells.First(b => b.Trigger == "ON_LEARN").SpellId) ? (object)i.ItemSpells.First(b => b.Trigger == "ON_LEARN").SpellId : DBNull.Value);
                        cmd.Parameters.AddWithValue("ItemId", i.ItemId);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

 protected void Page_Load(object sender, EventArgs e)
 {
     Response.ContentType = "text/plain";
     using (SqlConnection conn = new SqlConnection(profilesdb))
     {
         conn.Open();
         using (SqlDataReader dbreader = new SqlCommand("SELECT internalusername, nodeid from [UCSF.].vwPerson", conn).ExecuteReader(CommandBehavior.CloseConnection))
         {
             while (dbreader.Read())
             {
                 Response.Write(dbreader[0].ToString() + ", " + dbreader[1].ToString() + Environment.NewLine);
             }
         }
     }
 }

        public void readFromDataBase()
        {
            items.Clear();
            SqlDataReader reader = new SqlCommand(string.Format("SELECT * FROM Items ORDER BY Id;SELECT * FROM Jobs ORDER BY Item;SELECT * FROM Jobs ORDER BY Item;"), cn).ExecuteReader();
            //читаем лист items
            while (reader.Read())
            {
                item newItem = new item();
                newItem.id = reader.GetInt32(0);
                newItem.firstName = reader.GetString(1);
                newItem.lastName = reader.GetString(2);
                items.Add(newItem);
            }

            int i;
            int lastid;

            //читаем лист jobs
            reader.NextResult();
            lastid = i = -1;
            while (reader.Read())
            {
                int k = reader.GetInt32(4);
                if (lastid != k)
                {
                    do ++i;
                    while (k != items[i].id);
                    lastid = reader.GetInt32(4);
                }
                items[i].jobs.Add(new job(reader.GetDateTime(0), reader.GetString(1), reader.GetString(2), reader.GetString(3)));
            }

            //читаем лист positions
            reader.NextResult();
            lastid = i = -1;
            while (reader.Read())
            {
                if (lastid != reader.GetInt32(4))
                {
                    do i++;
                    while (reader.GetInt32(4) != items[i].id);
                    lastid = reader.GetInt32(4);
                }
                items[i].positions.Add(new position(reader.GetInt64(0),reader.GetInt64(1),reader.GetInt32(2),reader.GetDateTime(3)));
            }
            reader.Close();
        }

        private void btnAlterar_Click(object sender, EventArgs e)
        {
            try
            {
                var connection = DataBaseConnection.GetConnection();
                connection.Open();
                var value = gridClientes.SelectedRows[0].Cells[0].Value;
                var query = string.Format(" SELECT * FROM tb_clientes where codigo = {0} ", value);
                var reader = new SqlCommand(query, connection).ExecuteReader();
                if (reader.Read())
                {
                    txtCodCliente.Text = reader["codigo"].ToString();
                    txtNome.Text = reader["nome"].ToString();
                    if (reader["sexo"].ToString() == "M")
                        cbSexo.SelectedIndex = 0;
                    else
                        cbSexo.SelectedIndex = 1;
                    txtNacional.Text = reader["nacionalidade"].ToString();
                    txtProfissao.Text = reader["profissao"].ToString();
                    txtDataNasc.Text = reader["data_nascimento"].ToString();
                    if (reader["indicador_fisica_juridica"].ToString() == "F")
                    {
                        rbCpf.Checked = true;
                        txtIdent.Mask = "000,000,000-00";
                    }

                    else
                    {
                        rbCnpj.Checked = true;
                        txtIdent.Mask = "00,000,000/0000-00";
                    }
                    txtIdent.Text = reader["numero_cpf_cnpj"].ToString();
                }
               connection.Close();
               PreencherTelefones(value.ToString());
               PreencherEnderecos(value.ToString());
                LiberaCancelarConfirmar();
                groupCad.Enabled = true;
                EnabledComponent(true);
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void TestUpdate()
        {
            var students = StudentHelper.CreateSample(10).ToArray();
            using (var sqlConnection = DatabaseHelper.OpenSqlConnection(DatabaseName))
            {
                var sqlUpserter1 = new SqlUpserter<Student>(StudentMapper, TableName, students);
                sqlUpserter1.Execute(sqlConnection);

                var expectedChanges = 0;
                foreach (var student in students)
                {
                    if (student.Id%2 == 0)
                    {
                        student.FirstName = student.FirstName + "_Changed";
                        expectedChanges++;
                    }
                }

                var sqlUpserter2 = new SqlUpserter<Student>(StudentMapper, TableName, students);
                sqlUpserter2.Execute(sqlConnection);

                var selectText = "SELECT " + string.Join(",", StudentMapper.Columns.Select(column => column.Name)) +
                                 " FROM " + TableName;
                var reader = new SqlCommand(selectText, sqlConnection).ExecuteReader();
                var row = 0;
                var currentChanges = 0;
                while (reader.Read())
                {
                    var student = students[row];
                    for (var column = 0; column < reader.FieldCount; column++)
                    {
                        var expected = StudentMapper.GetValueAt(column, student);
                        var actual = reader.GetValue(column);
                        if (actual.ToString().EndsWith("_Changed"))
                        {
                            currentChanges++;
                        }
                        Assert.AreEqual(expected, actual);
                    }
                    row++;
                }
                Assert.AreEqual(expectedChanges, currentChanges);
            }
        }

 private static string[] GetEnabledTables(string connectionString)
 {
     SqlDataReader reader = null;
     SqlConnection connection = null;
     ArrayList list = new ArrayList();
     try
     {
         connection = new SqlConnection(connectionString);
         connection.Open();
         reader = new SqlCommand("dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure", connection) { CommandType = CommandType.StoredProcedure }.ExecuteReader();
         while (reader.Read())
         {
             list.Add(reader.GetString(0));
         }
     }
     catch (Exception exception)
     {
         SqlException exception2 = exception as SqlException;
         if ((exception2 != null) && (exception2.Number == 0xafc))
         {
             throw new DatabaseNotEnabledForNotificationException(System.Web.SR.GetString("Database_not_enabled_for_notification", new object[] { connection.Database }));
         }
         throw new HttpException(System.Web.SR.GetString("Cant_get_enabled_tables_sql_cache_dep"), exception);
     }
     finally
     {
         try
         {
             if (reader != null)
             {
                 reader.Close();
             }
             if (connection != null)
             {
                 connection.Close();
             }
         }
         catch
         {
         }
     }
     return (string[]) list.ToArray(Type.GetType("System.String"));
 }

        public bool CheckLoginData(string username, string password, ref Role role)
        {
            Connect();
            try
            {
                SqlDataReader dataReader = new SqlCommand("SELECT * FROM tbl_login", con).ExecuteReader();

                while (dataReader.Read())
                {
                    if (dataReader["username"].ToString().Trim() == username && dataReader["password"].ToString().Trim() == password)
                    {
                        MessageBox.Show("Login succes");

                        switch((int)dataReader["id"])
                        {
                            case 0:
                                role = Role.Development; break;
                            case 1:
                                role = Role.Finance; break;
                            case 2:
                                role = Role.Sales; break;
                        }

                        dataReader.Close();

                        return true;
                    }
                }
                dataReader.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }

            MessageBox.Show("Wrong username and/or wrong password");
            return false;
        }

        public DateTime GetDateModifiedUtc(int id, string filename)
        {
            //this.FireAuthorizeEvent(id);
            SqlConnection connectionObj = this.GetConnectionObj();
            connectionObj.Open();
            DateTime result;

            using (connectionObj)
            {
                SqlDataReader sqlDataReader = new SqlCommand(this.modifiedDateQuery, connectionObj)
                {
                    Parameters =
                    {
                        CreateParameter("id", id, SqlDbType.Int),
                        CreateParameter("name", filename, SqlDbType.NVarChar)
                    }
                }.ExecuteReader();

                using (sqlDataReader)
                {
                    if (!sqlDataReader.Read())
                    {
                        result = DateTime.MinValue;
                        return result;
                    }
                    for (var i = 0; i < sqlDataReader.FieldCount; i++)
                    {
                        if (!sqlDataReader.IsDBNull(i) && sqlDataReader.GetValue(i) is DateTime)
                        {
                            result = (DateTime)sqlDataReader.GetValue(i);
                            return result;
                        }
                    }
                }
            }

            return DateTime.MinValue;
        }