C# SqlDataAdapter.Fill方法代码示例

 /// <summary>
 /// 返回数据集,要从外部进行数据集 ( ds = new dataset()) 的传递
 /// </summary>
 /// <param name="commandType"></param>
 /// <param name="commandText"></param>
 /// <param name="commandParameters"></param>
 /// <param name="ds"></param>
 /// <param name="tableName"></param>
 /// <returns></returns>
 public override DataSet ExecuteDataset(CommandType commandType, string commandText,
                                        QueryParameterCollection commandParameters, DataSet ds, string tableName)
 {
     try
     {
         var cmd = new SqlCommand();
         PrepareCommand(cmd, commandType, commandText, commandParameters);
         var adapter = new SqlDataAdapter(cmd);
         if (Equals(tableName, null) || (tableName.Length < 1))
         {
             adapter.Fill(ds);
         }
         else
         {
             adapter.Fill(ds, tableName);
         }
         base.SyncParameter(commandParameters);
         cmd.Parameters.Clear();
         return ds;
     }
     catch
     {
         exceptioned = true;
         throw;
     }
     finally
     {
         Close();
     }
 }

    private void BindGrid()
    {
        string cmd = "SELECT username [UserName],hash [Password Hash], auth_level [Authentication Level] FROM LOGIN";
        DataSet ds = new DataSet();
        SqlConnection = new SqlConnection(ConfigurationManager.AppSettings["SQLAddress"]);
        SqlConnection.Open();
        SqlCommand = new SqlCommand(cmd, SqlConnection);
        SqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(SqlCommand);

        SqlDataAdapter.Fill(ds, "tblMain");
        int totRecs = ds.Tables["tblMain"].Rows.Count;
        SqlDataAdapter.Fill(ds, startIndex, pageSize, "t1");

        GridView1.DataSource = ds.Tables["t1"];
        GridView1.DataBind();
        SqlDataAdapter.Dispose();
        SqlCommand.Dispose();

        System.Text.StringBuilder sbPager = new System.Text.StringBuilder();
        for (int i = 1; i <= totPages; i++)
        {
            sbPager.Append("<a href=\"About.aspx?pg=").Append(i.ToString()).Append("\">").Append(i.ToString()).Append("</a>");
            sbPager.Append("&nbsp;|&nbsp;");
        }

        divPager.InnerHtml = sbPager.ToString();
    }

 private void Frm_Main_Load(object sender, EventArgs e)
 {
     string ConnectionString = "server=(local); database=db_TomeTwo; uid=sa; pwd=6221131";//声明连接字符串
     using (SqlConnection Conn = new SqlConnection(ConnectionString))//创建数据库连接对象
     {
         string sqlstr = "select * from tb_Register";//定义查询语句
         SqlDataAdapter da = new SqlDataAdapter(sqlstr, Conn);//创建数据桥接器对象
         DataSet ds = new DataSet();//创建数据对象
         da.Fill(ds, "register");//填充第一个数据表数据到DataSet
         sqlstr = "select * from tb_Sale";//定义查询语句
         da.SelectCommand.CommandText = sqlstr;//指定第二条查询语句
         da.Fill(ds, "sale");//填充第二个数据表数据到DataSet
         //查询有销售记录的药品信息
         var result = from r in ds.Tables["register"].AsEnumerable()
                      join s in ds.Tables["sale"].AsEnumerable()
                      on r.Field<string>("药品编号") equals s.Field<string>("药品编号")
                      select new
                      {
                          drug_name = r["药品名称"].ToString(),
                          drug_factory = r["生产厂家"].ToString(),
                          drug_sale = s["销售额"].ToString()
                      };
         foreach (var item in result)			//遍历输出查询结果
         {
             richTextBox1.Text += "药品名称：" + item.drug_name + "******生产厂家：" + item.drug_factory + "******销售额：" + item.drug_sale + "\n";
         }
     }
 }

 private void buscarP_Click(object sender, EventArgs e)
 {
     if(producto.TextLength>0)
     {
     dtingresos.Clear();
     dtsalidas.Clear();
     using (SqlConnection con = new SqlConnection("Server=localhost; database=INVERSEC_3; integrated security=yes"))
     {
         try
         {
             SqlCommand comando = new SqlCommand("SELECT pro.nombre Proveedor,p.cod_pro Codigo,p.nombre Nombre,p.descripcion Descripcion,b.nombre Bodega, CONVERT(VARCHAR,f.fecha,105) Fecha, sum(fp.cantidad) Cantidad from PRODUCTO p join FACT_PRODUCTO fp on p.cod_pro=fp.cod_pro and [email protected] join FACTURA f on f.num_fact=fp.num_fact  join bodega b on p.cod_bodega=b.cod_bodega join PROVEEDOR pro on pro.rut_emp=f.rut_pro group by f.fecha,p.cod_pro,p.nombre,p.descripcion,b.nombre,pro.nombre", con);
             comando.Parameters.AddWithValue("@nombre", producto.Text);
             SqlDataAdapter da = new SqlDataAdapter(comando);
             da.Fill(dtingresos);
             dgvi.DataSource = dtingresos;
             comando.CommandText = "SELECT cli.nombre Cliente,p.cod_pro Codigo,p.nombre Nombre,p.descripcion Descripcion,b.nombre Bodega, CONVERT(VARCHAR,f.fecha,105) Fecha, sum(fp.cantidad) Cantidad from PRODUCTO p join FACT_PRODUCTO fp on p.cod_pro=fp.cod_pro and [email protected] join FACTURA f on f.num_fact=fp.num_fact join bodega b on p.cod_bodega=b.cod_bodega join Cliente cli on cli.rut_emp=f.rut_cli group by f.fecha,p.cod_pro,p.nombre,p.descripcion,b.nombre,cli.nombre ";
             da.Fill(dtsalidas);
             dgvs.DataSource = dtsalidas;
         }
         catch (Exception ev)
         {
             MessageBox.Show("Error al intentar consultar la base de datos: " + ev.ToString());
         }
     }
     }
     else
     {
         MessageBox.Show("Debe ingresar un nombre de prodcuto");
     }
 }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!(bool)Session["logedIn"])
            {
                Response.Redirect("Login.aspx");
            }

            lblKorisnik.Text = (string)Session["userName"];
            lblGrupa.Text = (string)Session["userGroup"];

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MojaKonekcija"].ConnectionString);

            var cmd = new SqlCommand("SELECT * FROM korisnici WHERE korisnicko_ime LIKE @username", con);

            cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = lblKorisnik.Text;

            SqlDataAdapter adptr = new SqlDataAdapter();

            adptr.SelectCommand = cmd;

            SqlCommandBuilder cmdBld = new SqlCommandBuilder(adptr);

            DataTable mojDataTable = new DataTable();
            DataSet mojDataSet = new DataSet();

            adptr.Fill(mojDataSet);
            adptr.Fill(mojDataTable);

               var email= (string)mojDataTable.Rows[0]["email"];
               txtMail.Text = email;
        }

        public void readsequentialy()
        {
            int iter = 0;
            int currentIndex = 0;
            int pageSize = 3000;

            string sql = ConfigurationManager.ConnectionStrings["t2"].ConnectionString;
            string sqltest = ConfigurationManager.ConnectionStrings["test"].ConnectionString;

            SqlConnection connection = new SqlConnection(sql);

            string orderSQL = "SELECT * FROM [dbo].[TransactionHistory] order by [TransactionID]";
            // Assumes that connection is a valid SqlConnection object.
            SqlDataAdapter adapter = new SqlDataAdapter(orderSQL, connection);

            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, currentIndex, pageSize, "Orders");
            DataTable dataTable = dataSet.Tables[0];
            while (dataTable.Rows.Count > 0)
            {
                Console.WriteLine("iteration: " + iter++);
                foreach (DataRow row in dataTable.Rows)
                {
                    row.SetAdded();
                }
                //BultInsert(pageSize, sqltest, dataTable);

                currentIndex += pageSize;
                dataSet.Tables["Orders"].Rows.Clear();

                adapter.Fill(dataSet, currentIndex, pageSize, "Orders");
                dataTable = dataSet.Tables[0];
            }
            Console.WriteLine(String.Format("done rows: {0} with iteration: {1}", iter * pageSize, iter));
        }

 public void GetData()
 {
     try
     {
         con = new SqlConnection(cs);
         con.Open();
         cmd = new SqlCommand("SELECT StockId as [Stock ID], (productName) as [Product Name],Features,sum(Quantity) as [Quantity],Price,sum(TotalPrice) as [Total Price] from Config,Stock where Config.ConfigID=Stock.ConfigID group by Stockid, productname,features,price having (sum(Quantity) > 0)  order by Productname", con);
         var myDA = new SqlDataAdapter(cmd);
         var myDataSet = new DataSet();
         myDA.Fill(myDataSet, "Stock");
         myDA.Fill(myDataSet, "Config");
         dataGridView1.DataSource = myDataSet.Tables["Stock"].DefaultView;
         dataGridView1.DataSource = myDataSet.Tables["Config"].DefaultView;
         con.Close();
         var header = new string[]
         { "รหัส", "ชื่อสินค้า", "รายละเอียด", "จำนวน", "ราคา", "รวม"
         };
         for (var i = 0; i < header.Length; i++)
         {
             dataGridView1.Columns[i].HeaderText = header[i];
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message, "ล้มเหลว", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
 }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
            con.Open();
            SqlCommand comm = new SqlCommand("select * from UserData",con);

            DataTable master = new DataTable();
            DataTable child = new DataTable();

            // Fill Table 2 with Data
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(master);

               // Fill Table1 with data
            comm = new SqlCommand("select * from UserDetail",con);
            da.Fill(child);

            con.Close();

            DataSet ds = new DataSet();

            //Add two DataTables  in Dataset
            ds.Tables.Add(master);
            ds.Tables.Add(child);

            // Create a Relation in Memory
            DataRelation relation = new DataRelation("",ds.Tables[0].Columns[0],ds.Tables[1].Columns[0],true);
            ds.Relations.Add(relation);
            dataGrid1.DataSource = ds.Tables[0];
        }

        public static DataSet ExecuteDataset(SqlConnection cn, SqlTransaction trans, string cmdText, string tableName, params SqlParameter[] sqlParams)
        {
            DataSet data = new DataSet();

            SqlCommand cmd = new SqlCommand(cmdText, cn);
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = trans;
            if (sqlParams != null)
            {
                AttachParameters(cmd, sqlParams);
            }
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            if (tableName != null && tableName != string.Empty)
            {
                adapter.Fill(data, tableName);
            }
            else
            {
                adapter.Fill(data);
            }
            adapter.Dispose();
            cmd.Parameters.Clear();
            cmd.Dispose();

            return data;
        }

        private void AbmCliente_Load(object sender, EventArgs e)
        {
            SqlConnection dbcon = new SqlConnection(GrouponDesktop.Properties.Settings.Default["conStr"].ToString());
            SqlCommand cmd = new SqlCommand(@"Select nombre, idCiudad
                                            from LOSGROSOS_RELOADED.Ciudad", dbcon);
            DataTable dt = new DataTable();
            DataTable dt2 = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            try
            {
                dbcon.Open();
                da.Fill(dt);
                da.Fill(dt2);

            }
            catch (Exception ex)
            {
                Support.mostrarError(ex.Message);
            }
            dbcon.Close();
            this.lstCiudadesElegidas.DataSource = dt;
            this.lstCiudadesElegidas.DisplayMember = "nombre";
            this.lstCiudadesElegidas.ValueMember = "idCiudad";
            this.cmbCiudades.DataSource = dt2;
            this.cmbCiudades.DisplayMember = "nombre";
            this.cmbCiudades.ValueMember = "idCiudad";

            DateTime fechaActual = Support.fechaConfig();
            this.monthCalendar1.MaxDate = fechaActual;
            this.monthCalendar1.TodayDate = fechaActual;
        }

        public DataSet GetDataSet()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection())
                {
                    string connectionString = WebConfigurationManager.AppSettings["connectionString"];
                    conn.ConnectionString = connectionString;

                    SqlDataAdapter adapter = new SqlDataAdapter();
                    DataSet ds = new DataSet();
                    adapter.SelectCommand = new SqlCommand("SELECT * FROM Student", conn);
                    adapter.Fill(ds, "Student");
                    adapter.SelectCommand = new SqlCommand("SELECT * FROM Course", conn);
                    adapter.Fill(ds, "Course");
                    adapter.SelectCommand = new SqlCommand("SELECT * FROM SC", conn);
                    adapter.Fill(ds, "SC");
                    return ds;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public override void LoadSelectedData(DataSet currentDataSet, long IDNivel, long IDTipoFRDBase, IDbConnection conn)
		{
            using (SqlCommand command = SqlSyntax.CreateSelectCommandWithNoDeletedRowsParam((SqlConnection)conn))
            using (SqlDataAdapter da = new SqlDataAdapter(command))
			{
                command.Parameters.AddWithValue("@IDNivel", IDNivel);
                command.Parameters.AddWithValue("@IDTipoFRDBase", IDTipoFRDBase);
                da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
                    "WHERE [email protected]");
                da.Fill(currentDataSet, "Nivel");

				da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
                    "WHERE [email protected] AND [email protected]");
				da.Fill(currentDataSet, "FRDBase");

                da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAut"],
                    "INNER JOIN NivelControloAut nca ON nca.IDControloAut = ControloAut.ID " +
                    "WHERE [email protected]");
                da.Fill(currentDataSet, "ControloAut");

                da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAutDatasExistencia"],
                    "INNER JOIN NivelControloAut nca ON nca.IDControloAut = ControloAutDatasExistencia.IDControloAut " +
                    "WHERE [email protected]");
                da.Fill(currentDataSet, "ControloAutDatasExistencia");

                da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelControloAut"],
                    "WHERE [email protected]");
                da.Fill(currentDataSet, "NivelControloAut");
			}
		}

	protected void Page_Load(object sender, System.EventArgs e)
	{
		// Create the Connection, DataAdapter, and DataSet.
		string connectionString = "Data Source=localhost;Initial Catalog=Northwind;" +
			"Integrated Security=SSPI";
		SqlConnection con = new SqlConnection(connectionString);

		string sqlCat = "SELECT CategoryID, CategoryName FROM Categories";
		string sqlProd = "SELECT ProductName, CategoryID FROM Products";

		SqlDataAdapter da = new SqlDataAdapter(sqlCat, con);
		DataSet ds = new DataSet();

		try
		{
			con.Open();

			// Fill the DataSet with the Categories table.
			da.Fill(ds, "Categories");

			// Change the command text and retrieve the Products table.
			// You could also use another DataAdapter object for this task.
			da.SelectCommand.CommandText = sqlProd;
			da.Fill(ds, "Products");
		}
		finally
		{
			con.Close();
		}

		// Define the relationship between Categories and Products.
		DataRelation relat = new DataRelation("CatProds",
			ds.Tables["Categories"].Columns["CategoryID"],
			ds.Tables["Products"].Columns["CategoryID"]);
		// Add the relationship to the DataSet.
		ds.Relations.Add(relat);

		// Loop through the category records and build the HTML string.
		StringBuilder htmlStr = new StringBuilder("");
		foreach (DataRow row in ds.Tables["Categories"].Rows)
		{
			htmlStr.Append("<b>");
			htmlStr.Append(row["CategoryName"].ToString());
			htmlStr.Append("</b><ul>");

			// Get the children (products) for this parent (category).
			DataRow[] childRows = row.GetChildRows(relat);
			// Loop through all the products in this category.
			foreach (DataRow childRow in childRows)
			{
				htmlStr.Append("<li>");
				htmlStr.Append(childRow["ProductName"].ToString());
				htmlStr.Append("</li>");
			}
			htmlStr.Append("</ul>");
		}

		// Show the generated HTML code.
		HtmlContent.Text = htmlStr.ToString();
	}

 protected void cvTitle_ServerValidate(object source, ServerValidateEventArgs args)
 {
     SqlDataAdapter da = new SqlDataAdapter("", connection);
     DataTable dt = new DataTable();
     DataTable dl = new DataTable();
     da.SelectCommand.CommandText = "SELECT * FROM Skills WHERE ID = @sxid";
     da.SelectCommand.Parameters.AddWithValue("@sxid", Request.QueryString["Sid"]);
     da.Fill(dl);
     //Consept : Check if TextBox' value has changed or not
     //If This part doesn't Exist -> the page won't be valid Because of the next part of Validation. WHY? ...
     //if the title isn't changed the next part cause invalidation beacuse the Title has already Exist
     if (dl.Rows[0]["Title"].ToString() != txtTitle.Text)
     {
         da.SelectCommand.CommandText = "SELECT * FROM Skills WHERE [email protected] AND [email protected]";
         da.SelectCommand.Parameters.AddWithValue("@t", txtTitle.Text);
         da.SelectCommand.Parameters.AddWithValue("@scid", ddlSkillCat.SelectedValue);
         da.Fill(dt);
         //If the TextBox' Value has changed ...
         //So , Now Check if The New Value has already Exist in Table or not
         if (dt.Rows.Count == 0)
         {
             args.IsValid = true;
         }
         else
             args.IsValid = false;
     }
 }

 private void FillDefaultData()
 {
     AccTypesTbl = new DataTable("FalseX");
     FinlAccTbl = new DataTable("FalseX");
     SqlDataAdapter da = new SqlDataAdapter("",FXFW.SqlDB.SqlConStr);
     try
     {
         //Load All Account Types
         da.SelectCommand.CommandText = "SELECT AccNatueID, AccNatueName FROM CDAccountNature";
         da.Fill(AccTypesTbl);
         //Load All Finall Accounts
         da.SelectCommand.CommandText = "SELECT KhtamiaccID, KhtamiaccName FROM CDKHTAMIACOUNT";
         da.Fill(FinlAccTbl);
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     LUEAccType.Properties.DataSource = AccTypesTbl;
     LUEAccType.Properties.DisplayMember = "AccNatueName";
     LUEAccType.Properties.ValueMember = "AccNatueID";
     LUEAccEndCount.Properties.DataSource = FinlAccTbl;
     LUEAccEndCount.Properties.DisplayMember = "KhtamiaccName";
     LUEAccEndCount.Properties.ValueMember = "KhtamiaccID";
 }