C# WebControls.SqlDataSource类代码示例

 internal static void Show(GridView ListTests, string UserName)
 {
     SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
     string str = "select d.Discipline_name, c.categories_name, t.name, ct.points, ct.dateComplite " +
         " from Complite_test as ct inner join test as t on ct.test_id = t.test_id " +
         " inner join Categories as c on c.cat_id = t.cat_id " +
         " inner join discipline as d on d.discipline_id = c.discipline_id " +
         " inner join users as u on u.user_id = ct.user_id where u.login = @login";
     SqlCommand cmd = new SqlCommand(str, con);
     cmd.Parameters.AddWithValue("login", UserName);
     try
     {
         con.Open();
         SqlDataSource ds = new SqlDataSource(con.ConnectionString, str);
         Parameter p = new Parameter("login", System.Data.DbType.String, UserName);
         ds.SelectParameters.Add(p);
         ListTests.DataSource = ds;
         ListTests.DataBind();
     }
     catch (Exception)
     {
         throw new ApplicationException("Не удается отобразить список завершенных тестов");
     }
     finally {
         con.Close();
     }
 }

		public void Defaults ()
		{
			SqlDataSource ds = new SqlDataSource ();
			SqlViewPoker sql = new SqlViewPoker (ds, "DefaultView", null);

			Assert.IsTrue (sql.CancelSelectOnNullParameter, "A1");
			Assert.IsFalse (sql.CanDelete,"A2");
			Assert.IsFalse (sql.CanInsert,"A3");
			Assert.IsFalse (sql.CanPage,"A4");
			Assert.IsFalse (sql.CanRetrieveTotalRowCount,"A5");
			Assert.IsTrue (sql.CanSort,"A6");
			Assert.IsFalse (sql.CanUpdate,"A7");
			Assert.AreEqual (ConflictOptions.OverwriteChanges, sql.ConflictDetection, "A8");
			Assert.AreEqual ("", sql.DeleteCommand, "A9");
			Assert.AreEqual (SqlDataSourceCommandType.Text, sql.DeleteCommandType, "A10");
			Assert.IsNotNull (sql.DeleteParameters, "A11");
			Assert.AreEqual (0, sql.DeleteParameters.Count, "A12");
			Assert.AreEqual ("", sql.FilterExpression, "A13");
			Assert.IsNotNull (sql.FilterParameters, "A14");
			Assert.AreEqual (0, sql.FilterParameters.Count, "A15");
			Assert.AreEqual ("", sql.InsertCommand, "A16");
			Assert.AreEqual (SqlDataSourceCommandType.Text, sql.InsertCommandType, "A17");
			Assert.IsNotNull (sql.InsertParameters, "A18");
			Assert.AreEqual (0, sql.InsertParameters.Count, "A19");
			Assert.AreEqual ("{0}", sql.OldValuesParameterFormatString, "A20");
			Assert.AreEqual ("", sql.SelectCommand, "A21");
			Assert.AreEqual (SqlDataSourceCommandType.Text, sql.SelectCommandType, "A22");
			Assert.IsNotNull (sql.SelectParameters, "A23");
			Assert.AreEqual (0, sql.SelectParameters.Count, "A24");
			Assert.AreEqual ("", sql.SortParameterName, "A25");
			Assert.AreEqual ("", sql.UpdateCommand, "A26");
			Assert.AreEqual (SqlDataSourceCommandType.Text, sql.UpdateCommandType, "A27");
			Assert.IsNotNull (sql.UpdateParameters, "A28");
			Assert.AreEqual (0, sql.UpdateParameters.Count, "A29");
		}

        protected void Button1_Click(object sender, EventArgs e)
        {
            // verify login credentials
            string EmailAddress = TextBox1.Text;
            string Password = TextBox2.Text;

            string connstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User [email protected];Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            string selstring = "SELECT * FROM SENDER";
            SqlDataSource db = new SqlDataSource(connstring, selstring);
            DataView dv = (DataView)db.Select(DataSourceSelectArguments.Empty);
            int count = dv.Table.Rows.Count;
            string email = (string)dv.Table.Rows[0][1];
            string password = (string)dv.Table.Rows[0][2];
            int i = 0;
            bool status = false;
            while (i < count)
            {
                email = (string)dv.Table.Rows[i][1];
                password = (string)dv.Table.Rows[i][2];
                if (EmailAddress == email && Password == password)
                {
                    Session["username"] = EmailAddress;
                    Response.Redirect("text-message-page.aspx");
                    status = true;
                }
                else status = false;
                i++;
            }
            if (status == false)
            {
                Response.Redirect("SendMessagePage.aspx");
            }
        }

        protected void BindData()
        {
            string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            string sqlCommand;
            if (!isSort)
                sqlCommand = @"SELECT Id, Surname, Name, ThirdName, DisciplineName, Mark
                               FROM Students JOIN Discipline ON Students.DisciplineID = Discipline.DisciplineId";
            else
            {
                sqlCommand = string.Format(@"SELECT Id, Surname, Name, ThirdName, DisciplineName, Mark
                                            FROM Students JOIN Discipline ON Students.DisciplineID = Discipline.DisciplineId
                                            ORDER BY {0} {1}", sortExpr, order);

                for (int i = 0; i < studGrid.Columns.Count; i++)
                {
                    if (studGrid.Columns[i].SortExpression == sortExpr)
                        studGrid.Columns[i].HeaderStyle.CssClass =
                          "gridHeader" + order;
                    else
                        studGrid.Columns[i].HeaderStyle.CssClass = "gridHeader";
                }
            }
            SqlDataSource dataSource = new SqlDataSource(connectionStr, sqlCommand);
            studGrid.DataSource = dataSource;
            studGrid.DataBind();
        }

 public SqlConnection InitialConnect(SqlDataSource sqlDS, SqlConnection Connection)
 {
     sqlDS.ConnectionString = ConfigurationManager.ConnectionStrings["LFCRMConnectionString"].ConnectionString;
     sqlDS.SelectCommandType = SqlDataSourceCommandType.Text;
     Connection = new SqlConnection(sqlDS.ConnectionString);
     return Connection;
 }

        public void bindgrid()
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            if (OboutDropDownList1.SelectedValue == "Clients")
            {
                dt = Functions.DB.GetCustomers();
            }
            else if (OboutDropDownList1.SelectedValue == "Sales Reps")
            {
                dt = Functions.DB.GetSalesReps();
            }

            Grid1.DataSource = dt;
            Grid1.DataBind();
            string ID = string.Empty;
            SqlDataSource src = new SqlDataSource();
            if (dt.Rows.Count > 0)
            {
                System.Collections.Hashtable al = Grid1.Rows[0].ToHashtable();
                ID = al["ID"].ToString();
            }
            if (OboutDropDownList1.SelectedValue == "Clients")
            {
                src = Functions.DB.GetSQLDataSource2("SELECT top 1 *  FROM CLIENT_DAT Where ID='" + ID + "'");
            }
            else if (OboutDropDownList1.SelectedValue == "Sales Reps")
            {
                src = Functions.DB.GetSQLDataSource("SELECT top 1 *  FROM SalesReps Where ID=" + ID);
            }

            // SuperForm1.DefaultMode = DetailsViewMode.ReadOnly;
            SuperForm1.DataSource = src;
            SuperForm1.DataBind();
        }

        protected void BindData()
        {
            string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            string sqlCommand;

            if (ViewState["isSort"] == null || !(bool)ViewState["isSort"])
                /* sqlCommand = @"SELECT Id, Surname, Name, ThirdName, DisciplineName, Mark
                               FROM Students JOIN Discipline ON Students.DisciplineID = Discipline.DisciplineId"; */
                sqlCommand = string.Format("SELECT {0} FROM {1}", fieldsStr, selectFrom);
            else
            {
                sqlCommand = string.Format(@"SELECT {0} FROM {1} ORDER BY {2} {3}",
                    fieldsStr, selectFrom, ViewState["sortExpr"].ToString(), ViewState["order"].ToString());

                for (int i = 0; i < grid.Columns.Count; i++)
                {
                    if (grid.Columns[i].SortExpression == ViewState["sortExpr"].ToString())
                        grid.Columns[i].HeaderStyle.CssClass =
                          "gridHeader" + ViewState["order"].ToString();
                    else
                        grid.Columns[i].HeaderStyle.CssClass = "gridHeader";
                }
            }
            SqlDataSource dataSource = new SqlDataSource(connectionStr, sqlCommand);
            grid.DataSource = dataSource;
            grid.DataBind();
        }

 public SqlDataSourceDataConnectionChooserPanel(SqlDataSourceDesigner sqlDataSourceDesigner, IDataEnvironment dataEnvironment) : base(sqlDataSourceDesigner)
 {
     this._sqlDataSourceDesigner = sqlDataSourceDesigner;
     this._sqlDataSource = (SqlDataSource) this._sqlDataSourceDesigner.Component;
     this._dataEnvironment = dataEnvironment;
     this.InitializeComponent();
     this.InitializeUI();
     DesignerDataConnection conn = new DesignerDataConnection(System.Design.SR.GetString("SqlDataSourceDataConnectionChooserPanel_CustomConnectionName"), this._sqlDataSource.ProviderName, this._sqlDataSource.ConnectionString);
     ExpressionBinding binding = this._sqlDataSource.Expressions["ConnectionString"];
     if ((binding != null) && string.Equals(binding.ExpressionPrefix, "ConnectionStrings", StringComparison.OrdinalIgnoreCase))
     {
         string expression = binding.Expression;
         string str2 = "." + "ConnectionString".ToLowerInvariant();
         if (expression.ToLowerInvariant().EndsWith(str2, StringComparison.Ordinal))
         {
             expression = expression.Substring(0, expression.Length - str2.Length);
         }
         ICollection connections = this._dataEnvironment.Connections;
         if (connections != null)
         {
             foreach (DesignerDataConnection connection2 in connections)
             {
                 if (connection2.IsConfigured && string.Equals(connection2.Name, expression, StringComparison.OrdinalIgnoreCase))
                 {
                     conn = connection2;
                     break;
                 }
             }
         }
     }
     this.SetConnectionSettings(conn);
 }

 public void FilterDeliveryReceiptList(SqlDataSource datasource, string filter_field_type)
 {
     string CommandText = string.Empty;
     switch (filter_field_type)
     {
         case "POSTED":
             CommandText = "SELECT [ID], [DRNo], [DRDate], [PLNo], [CustNo], [DeliveredTo], [CustAddr], [TotalQty], [TotalAmt], [ItemStatus] FROM [DR] WHERE ynPosted=1 AND ynCancelled=0 ORDER BY DRDate DESC";
             datasource.SelectCommand = CommandText;
             datasource.DataBind();
             break;
         case "UNPOSTED":
             CommandText = "SELECT [ID], [DRNo], [DRDate], [PLNo], [CustNo], [DeliveredTo], [CustAddr], [TotalQty], [TotalAmt], [ItemStatus] FROM [DR] WHERE ynPosted=0 AND ynCancelled=0 ORDER BY DRDate DESC";
             datasource.SelectCommand = CommandText;
             datasource.DataBind();
             break;
         case "CANCELLED":
             CommandText = "SELECT [ID], [DRNo], [DRDate], [PLNo], [CustNo], [DeliveredTo], [CustAddr], [TotalQty], [TotalAmt], [ItemStatus] FROM [DR] WHERE ynCancelled=1 ORDER BY DRDate DESC ";
             datasource.SelectCommand = CommandText;
             datasource.DataBind();
             break;
         //default:
         //    CommandText = "SELECT [ID], [DRNo], [DRDate], [PLNo], [CustNo], [DeliveredTo], [CustAddr], [TotalQty], [TotalAmt], [ItemStatus] FROM [DR] ";
         //    datasource.SelectCommand = CommandText;
         //    datasource.DataBind();
         //    break;
     }
 }

        protected void Page_Load(object sender, EventArgs e)
        {
            Load_Logo();

            string connectionstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User [email protected];Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            SqlDataSource1.SelectCommand = "Select LIST_NAME from LIST";
            SqlDataSource1.Select(DataSourceSelectArguments.Empty);
            //SqlConnection con = new SqlConnection(@"Data Source=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User [email protected];Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
            //SqlCommand cmd = new SqlCommand("Select LIST_NAME from LIST", con);
            //SqlDataAdapter adp = new SqlDataAdapter(cmd);
            //DataTable dt = new DataTable();
            //adp.Fill(dt);
            //listCheckBox.DataSource = dt;
            //listCheckBox.DataValueField = "LIST_NAME";
            //listCheckBox.DataBind();

            connectionstring = "Server=tcp:evansvilledayschoolserver.database.windows.net,1433;Database=EvansvilleDaySchoolDatabase;User [email protected];Password=Quokka12;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            string selectstring = "SELECT ADMIN_SECURITYCODE FROM ADMINISTRATOR WHERE USER_ID = 0";
            SqlDataSource database = new SqlDataSource(connectionstring, selectstring);
            DataView dv = (DataView)database.Select(DataSourceSelectArguments.Empty);
            string code = (string)dv.Table.Rows[0][0];

            if (code == "No Code Set")
            {
                organizationCodeBox.Visible = false;
                Label1.Visible = false;
            }
        }

 public SqlDataSource SearchOutletDataSource(SqlDataSource sql_data_source, string search_parameter, string Brand)
 {
     //if (Brand != "ALL")
     //{
         //if (search_parameter != string.Empty)
     //    {
     //        sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [brand] FROM [CustInfo] where brand ='"+ Brand +"' and CompName Like '%" +
     //            search_parameter + "%'";
     //    }
     //    else
     //    {
     //        sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [brand] FROM [CustInfo] where brand ='" + Brand + "' ";
     //    }
     //}
     //else
     //{
         if (search_parameter != string.Empty)
         {
             sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [brand] FROM [CustInfo] where CompName Like '%" +
                 search_parameter + "%'";
         }
         else
         {
             sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [brand] FROM [CustInfo] ";
         }
     //}
     sql_data_source.DataBind();
     return sql_data_source;
 }

        protected void btnGetTransactions_Click(object sender, EventArgs e)
        {
            if (Convert.ToDateTime(txtBeginDate.Text) == Convert.ToDateTime(txtEndDate.Text))
            {
                ReportViewer1.LocalReport.DataSources.Clear();
                sdsCreditSalesDateRange = new SqlDataSource(ConfigurationManager.ConnectionStrings["HotelDB"].ConnectionString, getDateRange());
                ReportDataSource rds = new ReportDataSource("reportDatasource", sdsCreditSalesDay);
                ReportViewer1.LocalReport.DataSources.Clear();
                ReportViewer1.LocalReport.DataSources.Add(rds);
                ReportViewer1.LocalReport.DisplayName = "reportDatasource";
                ReportViewer1.DataBind();
                ReportViewer1.Visible = true;
            }
            else if (Convert.ToDateTime(txtBeginDate.Text) <= Convert.ToDateTime(txtEndDate.Text))
            {
                ReportViewer1.LocalReport.DataSources.Clear();
                sdsCreditSalesDateRange = new SqlDataSource(ConfigurationManager.ConnectionStrings["HotelDB"].ConnectionString, getDateRange());

                ReportDataSource rds = new ReportDataSource("reportDatasource", sdsCreditSalesDateRange);
                ReportViewer1.LocalReport.DataSources.Add(rds);
                ReportViewer1.LocalReport.DisplayName = "reportDatasource";
                ReportViewer1.DataBind();
                ReportViewer1.Visible = true;
            }
            else
            {
                ReportViewer1.Visible = false;
            }
        }

		public SqlDataSourceView (SqlDataSource owner, string name, HttpContext context)
			: base (owner, name)
		{
			this.owner = owner;
			this.name = name;
			this.context = context;
		}

 protected void SuperForm1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
 {
     SqlDataSource src = new SqlDataSource();
     if (OboutDropDownList1.SelectedValue == "Clients")
     {
         BusinessObjects.Client c = new BusinessObjects.Client();
         c.ClientID = e.NewValues["ID"].ToString();
         c.Type = e.NewValues["Type"].ToString();
         c.Name = e.NewValues["Name"].ToString();
         c.Contact = e.NewValues["Contact"].ToString();
         c.Phone = e.NewValues["Phone"].ToString();
         c.Fax = e.NewValues["Fax"].ToString();
         c.ReferenceNo = e.NewValues["ReferenceNo"].ToString();
         Functions.DB.SaveClient(c);
         ID = e.NewValues["ID"].ToString();
     }
     else if (OboutDropDownList1.SelectedValue == "Sales Reps")
     {
         BusinessObjects.SalesRep s = new BusinessObjects.SalesRep();
         int id; int.TryParse(e.NewValues["ID"].ToString(),out id);
         s.ID = id;
         s.Code = e.NewValues["Code"].ToString();
         s.Name = e.NewValues["Name"].ToString();
         bool act; bool.TryParse(e.NewValues["Active"].ToString(),out act);
         s.Active = act;
         Functions.DB.SaveSalesRep(s);
         ID = e.NewValues["ID"].ToString();
     }
     bindgrid();
 }

        public void LoadFilteredDRDetails(SqlDataSource DRDetailsDataSource, int DRID_S, int DRID_E, int CustomerNumber)
        {
            StringBuilder search_command = new StringBuilder();
            search_command.Append(" SELECT DR.DRNo, DRDtl.SKU, DRDtl.StyleNo, DR.ItemStatus, DRDtl.Quantity, DRDtl.UnitPrice FROM DR INNER JOIN DRDtl ON DR.ID = DRDtl.DRID ");
            search_command.Append(" where DR.ID between @ID_S and @ID_E and custno [email protected]_Number ");
            Parameter prmDRIDS = new Parameter
            {
                 DbType = System.Data.DbType.Int32 ,
                 Name = "ID_S",
                  DefaultValue = DRID_S.ToString(),
            };

            Parameter prmDRIDE = new Parameter
            {
                DbType = System.Data.DbType.Int32,
                Name = "ID_E",
                DefaultValue = DRID_E.ToString(),
            };
            Parameter prmCustomerNumber = new Parameter
            {
                DbType = System.Data.DbType.Int32,
                Name = "Customer_Number",
                DefaultValue = CustomerNumber.ToString(),
            };
            DRDetailsDataSource.SelectParameters.Add(prmDRIDS);
            DRDetailsDataSource.SelectParameters.Add(prmDRIDE);
            DRDetailsDataSource.SelectParameters.Add(prmCustomerNumber);
            DRDetailsDataSource.SelectCommand = search_command.ToString();
            DRDetailsDataSource.DataBind();
            //       SELECT DR.DRNo, DRDtl.SKU, DRDtl.StyleNo, DR.ItemStatus, DRDtl.Quantity, DRDtl.UnitPrice FROM DR
            //INNER JOIN DRDtl ON DR.ID = DRDtl.DRID
            //where DR.ID between @IDS and @IDE and custno [email protected]
        }