Hi Dmitry,
I am able to connect to the SQL SERVER database using the main SQL CMSConnection string, the REGFconnectionstring (custom database) is on the same server. I can connect to it from SSMS using the user name and password.
Here is the Code behind.
public partial class CMSWebParts_UBB_RegFDetail : CMSAbstractWebPart
{
private string CONNECTION_STRING = ConfigurationManager.ConnectionStrings["RegFConnectionString"].ConnectionString;
const string DOLLARS = "{0:#,##0}";
const string PERCENTAGE = "{0:0.00}";
string currDate;
// This property is used to display a date other than the default date
public string Date
{
get
{
return currDate;
}
set
{
currDate = value;
}
}
/// <summary>
/// Gets the value using the .NET 2.0 version of data-table filtering.
/// </summary>
/// <param name="id">The fieldname identifier.</param>
/// <param name="list">The list of all values</param>
/// <param name="formatting">The formatting used to convert the output to human readable format</param>
/// <returns></returns>
private static string GetValue(int id,
DataTable list,
string formatting)
{
double value = 0.0;
// Create a filter
list.DefaultView.RowFilter = String.Format("FieldID = {0}", id);
// Get the value
// double value = (double)list.DefaultView[0][1];
if (list.DefaultView.Count > 0)
value = (double)list.DefaultView[0][1];
// Return in the correct format
return String.Format(formatting, value);
}
/// <summary>
/// Loads the data from the data-store.
/// </summary>
/// <param name="d">The date in string format</param>
private void LoadData(string d)
{
DataTable data = new DataTable();
DateTime dteTemp;
DateTime? dte = null;
// Convert the string to a date/time object
if (DateTime.TryParse(d, out dteTemp) == true)
dte = (DateTime?)dteTemp;
// Ensure the date isn't NULL
if (dte.HasValue == true)
lData1.Text = dte.Value.ToString();
GetDataFromDatabase(data, dte);
if (data.Rows.Count > 0)
{
// Display the date
lDate.Text = ((DateTime)data.Rows[0][2]).ToShortDateString();
// These are precentages
lData1.Text = GetValue(1, data, PERCENTAGE);
lData2.Text = GetValue(2, data, PERCENTAGE);
lData3.Text = GetValue(3, data, PERCENTAGE);
lData4.Text = GetValue(4, data, PERCENTAGE);
lData5.Text = GetValue(5, data, PERCENTAGE);
lData6.Text = GetValue(6, data, PERCENTAGE);
lData13.Text = GetValue(13, data, PERCENTAGE);
lData14.Text = GetValue(14, data, PERCENTAGE);
lData16.Text = GetValue(16, data, PERCENTAGE);
lData17.Text = GetValue(17, data, PERCENTAGE);
lData18.Text = GetValue(18, data, PERCENTAGE);
lData19.Text = GetValue(19, data, PERCENTAGE);
// These are dollar amounts
lData7.Text = GetValue(7, data, DOLLARS);
lData8.Text = GetValue(8, data, DOLLARS);
lData9.Text = GetValue(9, data, DOLLARS);
lData10.Text = GetValue(10, data, DOLLARS);
lData11.Text = GetValue(11, data, DOLLARS);
lData12.Text = GetValue(12, data, DOLLARS);
lData15.Text = GetValue(15, data, DOLLARS);
}
}
/// <summary>
/// Gets the data from database.
/// </summary>
/// <param name="data">The data.</param>
/// <param name="dte">The requested date.</param>
private void GetDataFromDatabase(DataTable data, DateTime? dte)
{
// Define the connection object
SqlConnection cnn = new SqlConnection(CONNECTION_STRING);
using (cnn)
{
try
{
// Stored procedure used to return the information
SqlCommand comm = new SqlCommand("dbo.spGetRegFData", cnn);
using (comm)
{
comm.CommandType = CommandType.StoredProcedure;
// Don't add the parameter if the SP is to use the default values
if (dte.HasValue == true)
{
comm.Parameters.AddWithValue("@RegFdate", dte);
}
// Open the connection and return the data
cnn.Open();
data.Load(comm.ExecuteReader(CommandBehavior.CloseConnection));
}
}
catch (Exception er)
{
Response.Write(er.ToString());
}
finally
{
// Always ensure the connection is closed
if (cnn.State == ConnectionState.Open)
cnn.Close();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(Request.QueryString["d"]))
Date = Request.QueryString["d"];
// Determine if the control should display the current date or use the requested value
if (string.IsNullOrEmpty(currDate))
LoadData("");
else
LoadData(currDate);
}
}