troubleshooting Question

.net SQLDataAdapter using domain credentials...

Avatar of gfcit
gfcit asked on
.NET Programming.NET App ServersMicrosoft SharePoint
6 Comments1 Solution536 ViewsLast Modified:
I found a how-to on the internet concerning a reuseable SQL Data Adapter web part for Sharepoint.  I edited it to make it match my needs and I have one small issue that I just cannot figure out.  It works fine with a database user account but will not work using a domain account.  I'm sure it has to do with how the text is passed in the username property but I cannot figure it out.  I'm posting the code to both of my Classes in my project.  The first is my dataviewer (GFDataViewer.cs) and the second is my connection class (DBAccess.cs).  I'm hoping someone with more experience can see what I am doing wrong.

Thank you in advance,
GF
//GFDataViewer.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;

namespace GFDataViewer
{
    public class GFDataViewer : WebPart
    {
        protected override void RenderContents(HtmlTextWriter output)
        {
            output.Write(username);
            output.Write(innerpassword);
            output.Write(dbname);
            output.Write(servername);
        }
        private string servername;
        [Personalizable(), WebBrowsable(true), WebDisplayName("SQL Server"), Category("Data Properties")]

        public string ServerName
        {
            get { return servername; }
            set { servername = value; }
        }

        private string dbname;
        [Personalizable(),WebBrowsable(true),WebDisplayName("Database Name"), Category("Data Properties")]

        public string DatabaseName
        {
            get { return dbname; }
            set { dbname = value; }
        }

        private string username;
        [Personalizable(), WebBrowsable(true), WebDisplayName("DB User Name"), Category("Data Properties")]

        public string UserName
        {
            get { return username; }
            set { username = value; }
        }

        private string password;
        [Personalizable(), WebBrowsable(true), WebDisplayName("Password"), Category("Data Properties")]

        public string Password
        {
            get { return password; }
            set { password = value; }
        }

        private string innerpassword;
        [Personalizable(), WebBrowsable(false), WebDisplayName("InnerPassword"), Category("Data Properties")]

        public string InnerPassword
        {
            get { return innerpassword; }
            set { innerpassword = value; }
        }

        private string sqlquery;
        [Personalizable(), WebBrowsable(true), WebDisplayName("SQL Query"), Category("Data Properties")]

        public string SQLQuery
        {
            get { return sqlquery; }
            set { sqlquery = value; }
        }

        private int datarows;
        [Personalizable(), WebBrowsable(true), WebDisplayName("Rows Per Page"), Category("Data Properties")]

        public int DataRows
        {
            get { return datarows; }
            set { datarows = value; }
        }

        GridView gv = new GridView();

        protected override void CreateChildControls()
        {
            base.CreateChildControls();
            try
            {
                DBAccess objDB = new DBAccess();
                if (password != "******")
                {
                    InnerPassword = password;
                    password = "******";
                }
                string strConnection = "UID=" + username + ";PWD=" + innerpassword + ";Initial Catalog=" + dbname + ";Data Source=" + servername + ";";
                string sql = sqlquery;
                objDB.StrConnection = strConnection;

                DataTable dt = new DataTable();
                dt = objDB.GetSQLResult(sql);

                Controls.Add(gv);
                gv.DataSource = dt;
                gv.AlternatingRowStyle.BackColor = System.Drawing.Color.WhiteSmoke;
                gv.GridLines = GridLines.Horizontal;
                gv.CellPadding = 1;
                gv.DataBind();
                gv.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
                gv.AllowPaging = true;
                gv.PageSize = datarows;
                gv.PageIndexChanging += new GridViewPageEventHandler(this.gv_PageIndexChanging);
            }

            catch (Exception ex)
            {
                Label lbl = new Label();
                lbl.Text = "Error Occured: ";
                lbl.Text += ex.Message;
                Controls.Add(lbl);
            }
        }
        protected void gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gv.PageIndex = e.NewPageIndex;
            string strConnection = "UID=" + username + ";PWD=" + innerpassword + ";Initial Catalog=" + dbname + ";Data Source=" + servername + ";";

            DBAccess objdb = new DBAccess();
            objdb.StrConnection = strConnection;
            DataTable dt = objdb.GetSQLResult(sqlquery);
            gv.DataSource = dt;
            gv.DataBind();
        }
    }
}

//DBAccess.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace GFDataViewer
{
    public class DBAccess
    {
        private string strConnection;
        public DBAccess()
        {
        }
        public string StrConnection
        {
            get { return strConnection; }
            set { strConnection = value; }
        }
        public DataTable GetSQLResult(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cnn = new SqlConnection(strConnection);
                SqlDataAdapter da = new SqlDataAdapter(sql, cnn);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return dt;
            }
        }
    }
}
ASKER CERTIFIED SOLUTION
GeorgeGergues

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros