Add SQL Query And Variable to SharePoint ASPX Page

I created an aspx page on a sharepoint 2013 site. I configured the css pages, buttons and layout exactly as I needed. Now my goal is to add a SQL connection to a database on our network to pull data based on the current user. I wrote a SQL query mainly in the Select box "From Database where SharePointUserloggedinn resembles" db.table.field ". I tested this query in sql management studios and it works great. My question is how to add this part of the sql query to my aspx page, set the query result to a variable, and then display the query results (variable) to display via textbox / paragraph paragraph / other.

I added the namespaces and the C # part below to my aspx page, but I'm not sure how or where to put the C # code to connect to the SQL database, set a variable and then call that variable to display in the field further down.

`

<%@ Import Namespace="System;"%>
<%@ Import Namespace="System.Collections.Generic;"%>
<%@ Import Namespace="System.ComponentModel;"%>
<%@ Import Namespace="System.Data;"%>
<%@ Import Namespace="System.Drawing;"%>
<%@ Import Namespace="System.Linq;"%>
<%@ Import Namespace="System.Text;"%>
<%@ Import Namespace="System.Threading.Tasks;"%>
<%@ Import Namespace="System.Windows.Forms;"%>
<%@ Import Namespace="System.Data.SqlClient;"%>
<%@ Import Namespace="System.Web.UI.Page" %>


<%@ public partial class _Default : System.Web.UI.Page
{
private SqlDataReader reader = null;
public SqlDataReader Reader { get { return reader; } set { reader = value; } }
    protected void Page_Load(object sender, EventArgs e)
    {
    string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    SqlConnection connection = new SqlConnection(connectionString);
    connection.Open();

    SqlCommand command = new SqlCommand("SELECT [totalHours] FROM [DB].[dbo].[TABLE] Where [DB].[dbo].[TABLE].[column] like 'persons name') = @variable1", connection);
        command.Parameters.Add(new SqlParameter("variable1", "anonymous"));

        Reader = command.ExecuteReader();
    }
}
 %>

      

Any ideas or thoughts would be appreciated.

+3


source to share


1 answer


You shouldn't place your C # code in the page directly.

You can do custom control instead in the following tutorial: https://msdn.microsoft.com/en-us/library/ee231548.aspx

Then in your page you have to register the tag for the control

<%@ Register Tagprefix="MyControls" 
    Namespace="KM.MyControls.MyControl" 
    Assembly="KM.MyControls, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=<Your token>" %>

      



and then you can use your control on the page:

<MyControls:MyUserControl runat="server"/>

      

source: https://sharepoint.stackexchange.com/questions/46629/how-to-put-custom-user-control-on-page-layout

0


source







All Articles