Friday 15 May 2009

Upload XML File

To upload an XML file into SQL Server 2008:

1. Open SQL Server Business Intelligence Studio.
2. Create a new package ( shown in previous post)
3. Create Destination table on Server to upload file to.
4. Drag on Execute SQL Task - add TSQL to truncate the table.
5. Drag on Data Flow Task and double click
6. Drag on XML Source file and set the properties.
7. Drag on OLE DB Destination and set properties.

Job Done.

Thursday 14 May 2009

Subsonic Parent -> Child saving

How to save subsonic objects to the db that have FK values needing set.

Here's an example that also uses a transaction. Please note that you will have add a reference to the System.Transactions namespace in your project and then reference in your class.



   using (TransactionScope scope = new TransactionScope())

    {

        try

        {

            Order order = new Order();

            //populate order details.

            order.Save(); //Commit to DB





            OrderItem item = new OrderItem();

            //populate orderItem details.



            item.Order = order;   //THIS LINE SETS THE PARENT OBJECT TO ABOVE ORDER



            item.Save();  //Commit to DB



            //complete you transaction

            scope.Complete();



        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            throw ex;

        }

    }


Create a SSIS package

Basic steps to create an SSIS package (old DTS) to copy data from one database to another in SQL Server 2008:

Great link for the basic steps http://it.toolbox.com/blogs/coding-dotnet/creating-a-ssis-package-24699

However to truncate the tables before filling them with the above package:

1. Drag a Execute SQL Task into the control Flow window.
2. Double click and set the connection
3. Enter SQL Statement
4. Drag green line to the data flow task or the next step to run.

Point to Note: This tool is similar to Reporting Services where you create a project and then can have many packages within the project. Each package can then share the connections within the Data Sources folder.

Enjoy!

Friday 8 May 2009

Setting up an ASP.Net site for membership using your own database

If you are setting up a new application and you want to use the membership and roles security then normally .Net will try to set this up for you in your local express server. If however you want to set this up in your application database, on your main server then do the following.

1) Run C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe
2) Use the wizard to select the server and database. (I believe this can be any of 2000/05/08)
3) Change the following in your web config to suit your DB and server settings. Cahnge any other settings to suit your application. Click here for MSDN article.


<configuration>

<connectionStrings>

<add name="MySqlConnection" connectionString="Data

Source=YOURSQLSERVENAME;Initial Catalog=YOURDBNAME;Integrated

Security=SSPI;" />

</connectionStrings>

<system.web>

<authentication mode="Forms" >

<forms loginUrl="login.aspx"

name=".ASPXFORMSAUTH" />

</authentication>

<authorization>

<deny users="?" />

</authorization>

<membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="15">

<providers>

<clear />

<add

name="SqlProvider"

type="System.Web.Security.SqlMembershipProvider"

connectionStringName="MySqlConnection"

applicationName="MyApplication"

enablePasswordRetrieval="false"

enablePasswordReset="true"

requiresQuestionAndAnswer="true"

requiresUniqueEmail="true"

passwordFormat="Hashed" />

</providers>

</membership>

</system.web>

</configuration>






4) That should be you!


I got this info by using StackOverflow and asking this question.



Thursday 7 May 2009

Problem with Infragistics Charts in a Web Site!

When using Infragistics Chart components in a web site, you may get a GDI+ error relating to image permissions.

There is an infragistics solution to this problem here.

You have to grant user permissions to the web site folder/virtual directory on the server.

Right click the folder > Properties > Security Tab > Add

On Windows Server 2003 you would do: servername\NETWORK SERVICE

And that should be it.

Calling server side ASP.NET method from jQuery

I was recently looking at calling a server side method from an ASP.NET 3.5 application using jQuery. The following example has a server side method that returns some HTML markup from a database to jQuery, which then inserts it into the page markup.

Server side code, in Default.aspx:


using System.Web.Services;

[WebMethod(true)]
>(public static string getMarkup()
{
return "<h3>Test Markup</h3>";
}


You need to make the method static and include the WebMethod(true) operator above it. You can also use the following code to call a WebMethod from an asmx or WCF web service.

You call this from the client side jQuery code using:


$("#loadHome").click(function(){
$.ajax({
type: "POST",
url: "./Default.aspx/getMarkup",
data: "{}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function(result){
$("#contentDiv").html(result.d);
}
});
});


This uses the jQuery ajax method to get the data from the server side method, which is returned as a JSON object. You use the .d notation on the JSON object to get the result supplied from the server side.

If you want to send any data to the method, send as a JSON object. For example if you had a server side method that takes 2 params, orderId and customerId, you would use:

var data = "{orderId:'2',customerId:'99'}";
Free Hit Counter