Use stored credential for report created by c# code

In my current project i have created an interface through which admin can generate the report he wants from any of the table (or combination of tables) from any DB in the server. The interface is generating the xml Report Defination (rdl) and publishing it with the reporting service web service.
Now the problem came with the datasource of the report. When i publish the report it was showing the username and password input box in the report viewer. If i put those it was running beautifulluy. But it was not what i wanted. None of us will bother to give the UID and password everytime. Here is the sample of code i used to generate the report

static int zIndex = 1;
string serverName = "localhost";
string reportVirtualPath = "LocalReportServer";
string parentFolder = "QuoteReports";
string dataSetName = "DSSOP";
string dataSourceName = "DynamicDataSource";
string parTabName = "bodyTable";

void deployReport(string reportName, string reportDefination)// reportDefination is the xml rdl
{
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] byteRDL;
System.Text.UTF8Encoding encoder = new UTF8Encoding();
byteRDL = encoder.GetBytes(reportDefination);
Property[] rsProperty = new Property[10];
//Property property = new Property();
Warning[] warnings;
try
{
warnings = rs.CreateReport(reportName, "/" + parentFolder, true, byteRDL, null);
}
catch (System.Web.Services.Protocols.SoapException ex)
{
tdMessage.InnerHtml = "Exception in publiching report.
" + ex.Message + "";
return;
}
}

I was using report-specific datasource with authentication. My sample datasource section of the RDL is below
<datasource name="Personal">
<?xml:namespace prefix = rd /><rd:datasourceid>1a234378-11f1-4dc0-bc74-91df6d7d94f7</rd:datasourceid>
<connectionproperties>
<dataprovider>SQL</dataprovider>
<connectstring>Data Source=SODC42\SQLEXPRESS;Initial Catalog=SOP;UID=sa;password=123456;</connectstring>
</connectionproperties>
</datasource>

Though i was putting the connection string with UID and password, still it was showing the textboxes for username and password.
After a lot of google i found that reporting service uses two connection strings 1) one for connection to the report server 2) another for connection the report server to the databse (In mycase the both of report server and DB server is same express version in localhost). The connection string supplied in the RDL is for the first purpose and the second connection information is stored with the DataSource information in the ReportServer DB.
Then i open the report with the reportmanager (http://localhost/reports) and edit the report. In the editor i found the datasources link where i saw the "Credentials supplied by the user running the report" radio is selected. I canged the selection to "Credentials stored securely in the report server" and gave the desired credentials. Then i saw the report is not showinf the text boxes.
Now it was clear that the problem is with the DataSource not with the RDL. Then i went with the idea of using SharedDataSource and attach it with the report. While deploying the report i was checking if the shared datasource is already existing. If not then create it. Here is the code for creating the shared datasource.

DataSourceDefinition def = new DataSourceDefinition();
def.CredentialRetrieval = CredentialRetrievalEnum.Store;
def.ConnectString = ReportConnection.GetConnection("SOP").ConnectionString;
def.Enabled = true;
def.EnabledSpecified = true;
def.Extension = "SQL";
def.ImpersonateUser = false;
def.ImpersonateUserSpecified = true;
def.WindowsCredentials = false;
def.UserName = "sa";
def.Password = "123456";
rs.CreateDataSource(dataSourceName, dataSourcePath, true, def, null);
rs.SetDataSourceContents(dataSourcePath + "/" + dataSourceName, def);

Note:Before creating you have to check whether it already exists of not. And also the folder structure.
The rdl for datasource :
<DataSources>
<DataSource Name="DynamicDataSource">
<DataSourceReference>/Data Sources/DynamicDataSource</DataSourceReference>
</DataSource>
</DataSources>
Now the report is running fine.
But still there was something hitting in my mind. Why i shall use SharedDataSource? I shall use report specific datasource. But how?
After some days lots of RnD s i found it. It is totally my assumtion. I have no idea how far it is true. But it works.
The report specific datasources you have to keep in a folder "Data Sources" in the same directory as the report and then only the report can use it. So i created the datasource in the report folder's "Data Sources" folder. And so far it is running.

Download Sample Project
For information about the project refer to my other post Generate Your Dynamic Report Through Reporting Service

Reporting service: Dynamic Toggle Group

In my current project i have to use Reporting Service 2005 for generating some reports. It was the first time for me to RS. The report was showing some group on Year >> Month >> Day. The report was drill down one. I had to show the current month's days open i.e when the user open the report which is decending sorted with year,month,day will show the report with current mont's days expanded. All other years and month will remain collapsed.
For that i added two parameter to my report through
Report Layout (Designer)-> Report (Menu) -> Report Parameters(sub menu).
The parameters are showYear of type int and showMonth of type int.
Default value for showYear, i selected "non-queried" and in the textbox wrote =Year(Today)
and same for showMonth with TB value =Month(Today) so that the default value is set to the current month and year.
Now i edit the group showing the month and in the visibility tab i set "Initial Visibility" expression to "=IIF(Parameters!showYear.Value=Fields!Year.Value,false,true)", so that if the yeay is equals to current year then it will stay initially visible. And for day i set expression to "=IIF(Parameters!showYear.Value=Fields!Year.Value,IIF(Parameters!showMonth.Value=Fields!Month.Value, false,true),false)", so that if the month and year is euals to current then it will be stay initially visible (i kept the "Visibility can be toogled by another report item" section as it was.).
Now when i preview the report i found everything is running fine. By default it is expanding current year and month and if i put some other value in the report changing textbox it is expanding corresponding record. But the "+" and "-" signgs for toogle for those initially expanded is showing opposite i.e. "+" even when initially it is expanded and "-" when clicked to collapse.
Why this is happening? After searching various properies for the report and its item i got the "InitialToogleState" property of the textbox which by default is collapsed. Then i set this property to expression so that it is "Collapsed" and "Expanded" properly to show "+" and "-" sign respectively.
I set the year textbox's (TB which is responsible for toggle in year group row) InitialToogleState property to "=IIF(Parameters!showYear.Value=Fields!Year.Value,true,false)" and for month's one "=IIF(Parameters!showYear.Value=Fields!Year.Value,IIF(Parameters!showMonth.Value=Fields!Month.Value, true,false),true)" . Then i saw the result was as desired.
We can hide the Parameter Promt in the report viewer by ShowParameterPrompts="False" in the reportviewer control and send the parameter by querystring as 'showYear=2004&showMonth=5' .
Some points to note:
1) "InitialToogleState" property is boolean. "Collapsed" indicates false and "Expanded" true
2) "Initial Visibility" is set to month and day rows where as "InitialToogleState" is set for year and month TB.

One useful link:http://msdn.microsoft.com/en-us/library/aa337391.aspx

XML writing problem.. Encoding Fixing

MemoryStream ms = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(ms, System.Text.ASCIIEncoding.UTF8);
writer.Indentation = 3;
writer.Formatting = Formatting.Indented;
writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
writer.WriteStartElement("Report");
writer.WriteAttributeString("xmlns", null, "http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition");//Writing namespace
writer.WriteAttributeString("xmlns", "rd", null, "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");//Writing namespace


Or You can use StringBuilder class object like below
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);

Some Handy SQL Server Query

Get the Information about all the databases available in your SQL Server
EXEC sp_databases
EXEC sp_helpdb
select * from master..sysdatabases
SELECT * FROM sys.databases
SELECT * FROM sys.sysdatabases
EXEC sp_msForEachDB 'PRINT ''?'''
Get the Information about all the tables available in your SQL Server Database
SELECT Owner = TABLE_SCHEMA, TableName = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0 ORDER BY TABLE_SCHEMA, TABLE_NAME
or
exec sp_tables
but with this you have to filter to exclude the table owned by (
TABLE_OWNER) 'sys','INFORMATION_SCHEMA'.
Get the Information about all the tables available in your SQL Server Database
sp_columns @tableName
Select * from Information_Schema.Columns where Table_Name=@tableName

Problem Numeric check in SQL Server

In my datase i had a table which contains both numeric and characters and the table datatype was Varchar, and i have to find the min of the of the numeric value. In sql server the function ISNUMERIC(expr) was the solution to get rid of the character value.
The ISNUMERIC function determines whether an expression is a valid numeric type. returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. So i used the query like below
Select min(ColName) from demo where ISNUMERIC(ColName)=1
And i was getting my desired value.
But one day it started an exception in my asp.net page. And i could not find the reason.
After a long debugging i found that it was the problem with ISNUMERIC function of sql server. The ISNUMERIC function is returning 1 for some non-numeric character like '-' i.e
Select Isnumeric('-') returns 1 not 0.
And it was returning '-' from the query and error in asp.net page.
Then i tried with casting the result to int as.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1
And in this way the error was solved but the min value was always 0 as Cast('-')=0
Then i identified this abnormal characters as excluded them through the where clause.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1 and eadr not in('+','-','$').
This behaviour occurs for these three charactes('+','-','$') i have found so far.
For more information check this link

XML Encoding Problem In SQL Server

In a project i was sending an XML file to a SP which was written by someone. It was insering values to a master table and some details table. The SP was taking some TEXT datatype as input for the XML. Now the problem was one day we found some
"XML parsing error: An invalid character was found in text content. sql server xml parse" error and i come to know that some datatype is having some non-ascii characters like "Ö","«" for which the error was generated.
We were told to just avoid throughing error so that the user can continue their work. The fastest solution i came accross was to format the string before puttin into the XML.
So i wrote a function which will take the String as input and return the ASCII string as output as below.
public static string getUTF8String(string str) {
char[] ch = str.ToCharArray();
byte[] by = new byte[ch.Length * 2]; //If all char is UNicode then max byte is 2 X length System.Text.ASCIIEncoding enc = new ASCIIEncoding();
int charUsed, byteUsed;
bool completed;
enc.GetEncoder().Convert(ch, 0, ch.Length, by, 0, by.Length, true, out charUsed, out byteUsed, out completed);
string res=enc.GetString(by, 0, byteUsed);
return res;
}
So the Non-Ascii character may change, but the workflow wsa not hampered.
But the best solution is to change the SP and the xml sent to the SP. In the SP you should make the input parameter as NTEXT rather than TEXT and in the XML mention the encoding as UTF-16 - not UTF-8. In this way it will accept all unicode characters.

Ajax Like File Uploading

Place a hiddenIFrame into the page and submit the form containing the file uploader (input type='file') to the hidden iframe by changing the target property of the form to the name of
the IFrame. Here i have made the target of form (id='formUpload') to "iframeHidden"
as my iframe name is "ifameHidden". For uploading the file you have to make the'
enctype' and 'method' attributes values as it is in the example.
I have made the action attribute of the page to UploadFile.aspx page. This is a
page where it counts the Files Collection of the Request Object and if it finds
any file in the Collection (Request.Files.Count>0), it saves the file in the same
folder with the same name as file. The saving code is very trivial, for a real world
applicatio you have to be carefull while saving like name overwrite,different path
ect.


<script type="text/javascript">
function uploadFile(id)
{
document.getElementById("formUpload").submit();
}
</script>



<iframe id="iframeHiden1" style="height:0px;width:0px;border:0"
name="iframeHiden" src="UploadFile.aspx"></iframe>

<form id="formUpload" target="iframeHiden" enctype="multipart/form-data"
action="UploadFile.aspx" method="post">


Ajax Upload File In HTML Form:<input type="file" onchange="uploadFile('file1')"
id="file1" name="fileUploadHTML" />


</form>


UploadFile.aspx Page Load Event


protected
void Page_Load(object sender, EventArgs e)

{



int noOfFile = Request.Files.Count;


if (noOfFile > 0)


{


string path = Request.PhysicalPath.Substring(0, Request.PhysicalPath.LastIndexOf("\\")
+ 1) + Request.Files[0].FileName;


Request.Files[0].SaveAs(path);


}
}