VPF::Execute sql command and display result from MS SQL - Форум программистов

Makpal

Дата 27.10.2011, 07:48 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 1
Регистрация: 27.10.2011

Репутация: нет
Всего: нет

Hello everyone!

I have a problem. I've been assigned to this assignment to create a basic web-application: there should be 2 user categories, administrator part should have an option to create/delete SQL statements, sroted in DB (i'm thinking about creation of separate DB for application and i already have a huge DB, where my Queries should be executed) and select/run stored queries. other users (like stuff members) should only have an option to select one of the STORED QUERIES (i need to store queries in table, because ONLY Administrator should see the sql statenent itself, and other users should only see the NAME of the query) and execute it. then, report according to sql statement should be displayed (i need this reports to be created according to sql, dynamically create number of rows and coulmns, so that i don't have to create different forms every query).

Help me please!!! i Don't know how to clue all this stuff((

Here is what i have for now, and it's not working :(

------------Logon.aspx------------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Logon.aspx.cs"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html

xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
     <title>SQL QueriesRun Page</title>
<script language="javascript" type="text/javascript">

</script>
</head>
<body>
     <form id="form1" runat="server">
     <div>
     <h3>
     <font face="Verdana"> Logon page </font>
     </h3>
     <table>
     <tr>
     <td>Email:</td>
     <td> <input id="txtUserName" type="text" runat="server" onclick="return txtUserName_onclick()" /></td>
     <td><asp:RequiredFieldValidator ControlToValidate="txtUserName"
     Display="Static" ErrorMessage="*" runat="server"
     ID="vUserName" /></td>
     </tr>
     <tr>
     <td> Password: </td>
     <td><input id="txtUserPass" type="password" runat="server" /></td>
     <td><asp:RequiredFieldValidator ControlToValidate="txtUserPass"
     Display="Static" ErrorMessage="*" runat="server"
     ID="vUserPass" /></td>
     </tr>
     <tr>
     <td> Persistent Cookie:</td>
     <td> <asp:CheckBox ID="chkPersistCookie" runat="server" AutoPostBack="false" /></td>
     <td></td>
     </tr>
     </table>
     <input type="submit" value="logon" runat="server" id="cmdLogin" onclick="return cmdLogin_onclick()" /> <p></p>
     <asp:Label ID="lblMsg" ForeColor="Red" Font-Name="Verdana" Font-Size="10" runat="server" />

    </div>
     </form>
</body>
</html>

------------------codeBehind="Logon.aspx.cs"----------------------------------

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

namespace MyProject

{

   public partial class Logon : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

      {

       }

        private bool ValidateUser(string userName, string password)

        {

            SqlConnection conn;

            SqlCommand cmd;

            string lookupPassword = null;

            //check for invalid username.

            //username must not be null and must be betewwn 1 and 25 characters

            if ((null == userName) || (0 == userName.Length) || (userName.Length > 15))

            {

                System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of userName failed.");

                return false;

            }

            //check for invalid pasword

            //password must not be null or not between 1 and 25 characters.

            if ((null == password) || (0 == password.Length) || (password.Length > 25))

            {

                System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of password failed.");

                return false;

            }

            try

            {

                conn = new SqlConnection("server=localhost; Integrated Security=SSPI; database=master");

                conn.Open();

                cmd = new SqlCommand("select pwd from users where uname=@userName", conn);

                cmd.Parameters.Add("@userName", SqlDbType.VarChar, 25);

                cmd.Parameters["@userName"].Value = userName;

                lookupPassword = (string)cmd.ExecuteScalar();

                cmd.Dispose();

                conn.Dispose();

            }

            catch (Exception ex)

            {

                System.Diagnostics.Trace.WriteLine("[ValidateUser] Exception " + ex.Message);

            }

            if (null == lookupPassword)

            {

                return false;

            }

            return (0 == string.Compare(lookupPassword, password, false));

        }

        private void cmdLogin_onclick(object sender, System.EventArgs e)

        {

            if (ValidateUser(txtUserName.Value, txtUserPass.Value))

            {

                FormsAuthenticationTicket tkt;

                string cookiestr;

                HttpCookie ck;

                tkt= new FormsAuthenticationTicket(1,txtUserName.Value, DateTime.Now,

                    DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your data");

                cookiestr=FormsAuthentication.Encrypt(tkt);

                ck= new HttpCookie(FormsAuthentication.FormsCookieName, cookiestr);

                if (chkPersistCookie.Checked)

                    ck.Expires=tkt.Expiration;

                            ck.Path=FormsAuthentication.FormsCookiePath;

                    Response.Cookies.Add(ck);

                string strRedirect;

                strRedirect = Request["ReturnUrl"];

                if (strRedirect==null)

                    strRedirect="default.aspx";

                    Response.Redirect(strRedirect, true);

                }

                else Response.Redirect("logon.aspx", true);

            }

    }

}

--------------------User.aspx---------------------------- (i need to clue it with logon)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="User.aspx.cs"  %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
     <title>Untitled Page</title>
     <style type="text/css">
         .style1
         {
             margin-left: 400px;
         }
     </style>

</head>
<body>
     <form id="form1" runat="server">
     <div align="center">
     <table><tr><th>Select query:</th><td><asp:DropDownList ID="DropDownList1" runat="server">
     </asp:DropDownList></td></tr>
     </table>
     </div>
         <p class="style1">
             <asp:Button ID="btnClick" onclick="Button_Click" runat="server" Text="Run" />
         </p>
         <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:Master1ConnectionString %>"
            DatasourceMode="DataSet"
             onselecting="SqlDataSource1_Selecting" SelectCommand="SELECT * FROM [dbo].[tblOne]">
             <SelectParameters>
             <asp:Parameter Name="theName" Type="String" />
             </SelectParameters>
         </asp:SqlDataSource>

        <asp:SqlDataSource ID="SqlDataSource2" runat="server"
            ConnectionString="<%$ ConnectionStrings:Master1ConnectionString %>"
            DatasourceMode="DataReader"
             onselecting="SqlDataSource2_Selecting" SelectCommand="SELECT * FROM [dbo].[tblOne]">
             <SelectParameters>
             <asp:Parameter Name="theName" Type="Int32" />
             </SelectParameters>
         </asp:SqlDataSource>
         <div class="style1">
                <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
         </div>
     <div class="style2">
         <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
         </div>
     </form>
</body>
</html>

---------------User.aspx.cs-------------------

namespace MyProject

{

    public partial class User : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        Populate1();

        DataView dvSql = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);

        foreach (DataRowView drvSql in dvSql)

        {

            Label1.Text = drvSql["theName"].ToString();

        }

        SqlDataReader rdrSql = (SqlDataReader)SqlDataSource2.Select(DataSourceSelectArguments.Empty);

        while (rdrSql.Read())

        {

            Label2.Text = rdrSql["theName"].ToString();

        }

        rdrSql.Close();

    }

        }

        public void Populate1()

        {

            SqlCommand cmd = new SqlCommand("SELECT qName FROM [tblQueries]", new SqlConnection(ConfigurationManager.AppSettings["ConnString"]));

            cmd.Connection.Open();

            SqlDataReader ddlValues;

            ddlValues = cmd.ExecuteReader();

            DropDownList1.DataSource = ddlValues;

            DropDownList1.DataValueField = "theName";

            DropDownList1.DataTextField = "theName";

            DropDownList1.DataBind();

            cmd.Connection.Close();

            cmd.Connection.Dispose();

        }

         protected void Button_Click(object sender, EventArgs e)

    {

        btnClick.BackColor = System.Drawing.Color.Green;

        lblHello.Text = "Hello World";

    }

    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)

    {

        e.Command.Parameters["theName"].Value = "user1";

    }

    protected void SqlDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e)

    {

        e.Command.Parameters["theName"].Value = "user1";

    }

    }

---------------------------Default.aspx----------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="MyProject._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html

xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
     <title>Untitled Page</title>
<script language="javascript" type="text/javascript">
</script>
</head>
<body>
     <form id="form1" runat="server">
     <div>
     <h3>you are in default</h3>
     <input type="submit" value="SignOut" runat="server" id="cmdSignOut" onclick="return cmdSignOut_onclick()" />
     </div>
     </form>
</body>
</html>

----------------Default.aspx.cs------------

namespace MyProject

{

    public partial class _Default : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        private void cmdSignOut_onclick(object sender, System.EventArgs e)

        {

            FormsAuthentication.SignOut();

            Response.Redirect("logon.aspx", true);

        }

    }

}

PLEASE! help meeeeeee!

Присоединённый файл ( Кол-во скачиваний: 1 )

MyProject.rar 25,33 Kb

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Разработка под ASP.NET \| Следующая тема »