Passing lists to SQL server stored procedures

Click Here to visit Zonix’s Website

Article is about:

The ability to pass “a list of values” from .Net as a parameter to a T-SQL based stored procedure.

Scenarios:

There are lots of scenarios where we need to pass a list of values to save in database. Here’s a couple of obvious ones:

INSERT a list of values into the database in one “chunky” call (e.g. some IDs from a CheckBoxList)
SELECT rows where IDs are IN ()
Some general Approaches:

Taking the INSERT statements as an example, there are various general approaches that we adopt to achieve the desired result:

Use dynamic / Inline SQL! But ideally say, dynamic / Inline SQL is rarely the ideal solution for obvious reasons.
Make a stored proc call for each ID to insert. This is the most common approach we see in various projects, mainly because it is the easiest to implement. The drawback of course is if we were to insert 60 values, it would result in 60 “chatty” calls to the database.
Pass comma separated values via a VARCHAR (or similar) parameter. This works fine but has messy “string splitting” in the stored procedure to extract the IDs and then build the SQL statement in the procedure itself. Prone to SQL injection and not the best performance.
Pass the list as an XML parameter. This is nicer and is the point of this article.
Coming to the main Point, Using XML:

Using XML for “list passing” has a number of benefits, in particular the ability to pass lists of more “complex types” rather than just single values.

Lets take an example. Suppose we are having 2 CheckedListBox; one is list of Users and another is the list of tasks / roles that can be assigned to Users. We want to store the values in Table which is having Fields UserID and TaskID. The Stored Procedure will accept Parameter with XML Datatype as,

CREATE PROCEDURE [dbo].[usp_InsertUserTask]
@UserTaskXML XML
AS
BEGIN
INSERT INTO UserTasks (UserID,TaskID)
SELECT
UserTaskTab.UserTaskCol.value(‘UserID[1]’,’int’) AS UserID,
UserTaskTab.UserTaskCol.value(‘TaskID[1]’,’int’) AS TaskID
FROM @UserTaskXML.nodes(‘//UserTaskList/UserTaskData’) AS UserTaskTab(UserTaskCol)
END

To call this in Stored Procedure, you would have something like this:

EXEC [dbo].[usp_InsertUserTask]
@UserTaskXML = ‘

1
100

2
200

In your application, your C# calling code could be:

SqlConnection sqlCN = new SqlConnection();
sqlCN.ConnectionString = ConfigurationManager.AppSettings[“DBConn”].ToString();
string strQuery = “usp_InsertUserTask”;
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter(“@UserTaskXML”, GetStudyDataXMLString());
SqlHelper.ExecuteNonQuery(sqlCN, CommandType.StoredProcedure, strQuery, sqlParams);
if (sqlCN.State == ConnectionState.Open)
sqlCN.Close();
sqlCN.Dispose();

which calls the method below to translate the UserID and TaskID from CheckBoxLists into an XML String:

private string GetUserTaskListXML()
{
try
{
StringBuilder XMLString = new StringBuilder();
XMLString.AppendFormat(“”);
for (int iUserCount = 0; iUserCount < UserCheckBoxList.Items.Count; iUserCount++)
{
if(UserCheckBoxList.Items[iUserCount].Selected)
{
for (int iTaskCount = 0; iTaskCount < TaskCheckBoxList.Items.Count; iTaskCount++)
{
if(TaskCheckBoxList.Items[iTaskCount].Selected)
{
XMLString.AppendFormat(“”);
XMLString.AppendFormat(“{0}”, UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat(“{0}”, UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat(“”);
}
}
}
}
XMLString.AppendFormat(“”);
}
catch (Exception Ex)
{
throw Ex;
}
return XMLString.ToString();
}

Here, StringBuilder is used for the xml concatenation as in this case I think it fits the bill but purists might prefer an XmlTextWriter approach. In summary, it performs very well and is adaptable for various lists of objects and more complex structures.

Referenced Link:

Passing lists to SQL server stored procedures


Bookmark and Share

Advantages of Asp.net and Sharepoint – Where to use SharePoint

Click Here to visit Zonix’s Website

Here are some of Advantages/Disadvantages of SharePoint apps over ASP.Net apps:

 

Advantages:

 

1. Many built-in features are available with MOSS which can be used to easily develop complex solutions.

2. Rich Security features which come built in.

3. Integrated with Content Management.

4. Very less efforts required to create basic sites with lot of features.

5. It is quite scalable.

6. OOB Integration with Office products.

7. Rich backup techniques

8. Business process can be integrated with Workflows.

9. Multiple sites can be created with the help of templates.

 

Disadvantages:

1. It is difficult to add custom code in SharePoint. Features and WSP files take time.

2. Difficult to solve the problems in development.

3. Creating ASP.net pages is easy then creating web parts.

4. Cost is very high.

5. Performance for ASP.NET apps is much better.

6. Very few experts are there.

 

Advantages/Disadvantages of ASP.Net apps over SharePoint apps:

 

Advantages:

 

1. Here we have lot of control on application in terms of DB, UI.

2. Less cost to build.

3. Easy to develop and deploy.

4. Plenty of resources.

 

Disadvantages:

1. Takes lot more time to get same functionality as of SharePoint.

2. There is nothing built in. So every solution needs to start from zero.

3. Good DB knowledge is also required to create sites having data.

4. Security needs to build.

What’s SharePoint Not Good For

Click Here to visit Zonix’s Website

SharePoint is an excellent way to create data-driven web sites, in my opinion, but there are other times where I don’t think it’s the best choice. For example, SharePoint isn’t a substitute for a code management library, such as SourceSafe. (Microsoft is working on this for Visual Studio 2008, however, so watch out!) Also, SharePoint’s integration with Microsoft Office, Windows, and .NET means that users of other operating platforms (Mac, Linux) or non-Microsoft browsers may have problems using the sites.

In particular, SharePoint authentication does not seem to work with Internet Explorer for the Macintosh or Mozilla prior to Version 1.7.2. In addition, SharePoint pages look different in FireFox, Mozilla, and Netscape Navigator than they look in Internet Explorer. You can compare these differences by using different browsers to view public SharePoint sites.

Introduction to SharePoint

Click Here to visit Zonix’s Website

  1. Why Use SharePoint?

a.  SharePoint is a component of Windows 2003 that lets you share Microsoft Office documents with others through web pages. Unlike most web sites, SharePoint sites are designed to be highly dynamic. Team members can easily upload documents, add public announcements, send alerts, track work items, and call meetings right from within Office products. 

2.      What is difference between SharePoint Service and SharePoint Server?

  1.  
    1. A Sharepoint service is freely downloadable.
    2. The Windows SharePoint Services are primarily focused around workgroup level collaboration . The main purpose for SharePoint Portal Server is to act as an enterprise level portal.
    3. SharePoint Portal Server was built on top of the Windows SharePoint Services. This means that anything that the Windows SharePoint Services can do, SharePoint Portal Server can also do. Windows SharePoint Service’s primary focus is to create workspaces that small groups of users can use to collaborate on projects by sharing a small collection of documents and other data. Where for SharePoint Portal Server is to act as an enterprise level portal. SharePoint server has some advance feature like searching like we can search for specific keyword. The results of the search can then be arranged by document author, site, date, and category. SharePoint Portal Server also offers hierarchical search scopes that allow users to perform searches from within specific topics, categories, or content sources.
  2. What is the  OS requirement for Windows SharePoint Service?
    1. Windows server 2003.
  3. What Software Requirements at client side for use sharepoint  services features?
    1. Microsoft Internet Explorer 5.01 with SP2 or later (best results with Microsoft Internet Explorer 5.5 with SP2 or later), Microsoft Internet Explorer 5.2 or later for Macintosh, Netscape Navigator version 6.2 or later, Mozilla 1.4 or later, or Firefox 1.0.4 or later 
  4. What types of configuration available for Sharepoint Services?
    1. There are two types of configuration available for sharepoint services and farm server.

                   i.      stand-alone server: if our requirement is hosting  very few sites  and for small organization

                  ii.      server farm:If we are supporting Web sites in a large organization or as an Internetserviceprovider (ISP), and anticipate heavy usage and a lot of data, we will most likely want to use the server farm configuration.


Bookmark and Share

Introduction to SharePoint

Click Here to visit Zonix’s Website

  1. Why Use SharePoint?

a.  SharePoint is a component of Windows 2003 that lets you share Microsoft Office documents with others through web pages. Unlike most web sites, SharePoint sites are designed to be highly dynamic. Team members can easily upload documents, add public announcements, send alerts, track work items, and call meetings right from within Office products. 

2.      What is difference between SharePoint Service and SharePoint Server?

  1.  
    1. A Sharepoint service is freely downloadable.
    2. The Windows SharePoint Services are primarily focused around workgroup level collaboration . The main purpose for SharePoint Portal Server is to act as an enterprise level portal.
    3. SharePoint Portal Server was built on top of the Windows SharePoint Services. This means that anything that the Windows SharePoint Services can do, SharePoint Portal Server can also do. Windows SharePoint Service’s primary focus is to create workspaces that small groups of users can use to collaborate on projects by sharing a small collection of documents and other data. Where for SharePoint Portal Server is to act as an enterprise level portal. SharePoint server has some advance feature like searching like we can search for specific keyword. The results of the search can then be arranged by document author, site, date, and category. SharePoint Portal Server also offers hierarchical search scopes that allow users to perform searches from within specific topics, categories, or content sources.
  2. What is the  OS requirement for Windows SharePoint Service?
    1. Windows server 2003.
  3. What Software Requirements at client side for use sharepoint  services features?
    1. Microsoft Internet Explorer 5.01 with SP2 or later (best results with Microsoft Internet Explorer 5.5 with SP2 or later), Microsoft Internet Explorer 5.2 or later for Macintosh, Netscape Navigator version 6.2 or later, Mozilla 1.4 or later, or Firefox 1.0.4 or later 
  4. What types of configuration available for Sharepoint Services?
    1. There are two types of configuration available for sharepoint services and farm server.

                   i.      stand-alone server: if our requirement is hosting  very few sites  and for small organization

                  ii.      server farm:If we are supporting Web sites in a large organization or as an Internetserviceprovider (ISP), and anticipate heavy usage and a lot of data, we will most likely want to use the server farm configuration.


Bookmark and Share

Create a Working Map using Google Maps API

Click Here to visit Zonix’s Website

Create a Working Map using Google Maps API

 


  1. To include a Google map on an online site, you’ll need an API key which is tied to your site URL and your GMail account. First, visit http://www.google.com/apis/maps/signup.html. Make sure to read through the terms of use — there are some uses of the Maps API which are not allowed that you should be aware of.

     

  2. Enter the URL of your website in the ‘My web site URL’ box.It should be a domain name like http://gtl-466/GoogleMapIntegration. Check the box indicating you agree with the terms, then press the ‘Generate API Key’ button.

     

     

         

  1. You should now see the sign-up thank you page, which tells you your key (a long string of random characters), the directory/domain it’s valid for, and sample code for your first map which includes your API key. You can see the API key appended to the end of the script tag that loads the Maps API into the page. Keep this page open in your browser.

           

 

 

  1. Open a text editor like Notepad on Windows or TextEdit on Macs. Copy and paste the HTML from the thank you page into a blank document, and save it as mymap.html.

             

 

  1. It’s time to test it out! Right click on ‘mymap.html’ and select view in browser .Now you’ll see a browser window open up to http://gtl-466/GoogleMapIntegration/mymap.html.If you did all the steps correctly, you should now see a map centered on Palo Alto, California

     

    If you’re getting an error about the key being registered for a different site, then you probably entered in the wrong URL when you signed up for the API key. Go back to Step 2 of this section and make sure you enter the domain name that you see in the browser window for the page.

     

    If you’re not seeing a map and are just seeing a blank page, you probably made a copy & paste error. Go back to Step 4, and make sure you copy and paste all and only the HTML given to you in the textbox.


Bookmark and Share


Bookmark and Share

Create a Working Map using Google Maps API

Click Here to visit Zonix’s Website

Create a Working Map using Google Maps API

 


  1. To include a Google map on an online site, you’ll need an API key which is tied to your site URL and your GMail account. First, visit http://www.google.com/apis/maps/signup.html. Make sure to read through the terms of use — there are some uses of the Maps API which are not allowed that you should be aware of.

     

  2. Enter the URL of your website in the ‘My web site URL’ box.It should be a domain name like http://gtl-466/GoogleMapIntegration. Check the box indicating you agree with the terms, then press the ‘Generate API Key’ button.

     

     

         

  1. You should now see the sign-up thank you page, which tells you your key (a long string of random characters), the directory/domain it’s valid for, and sample code for your first map which includes your API key. You can see the API key appended to the end of the script tag that loads the Maps API into the page. Keep this page open in your browser.

           

 

 

  1. Open a text editor like Notepad on Windows or TextEdit on Macs. Copy and paste the HTML from the thank you page into a blank document, and save it as mymap.html.

             

 

  1. It’s time to test it out! Right click on ‘mymap.html’ and select view in browser .Now you’ll see a browser window open up to http://gtl-466/GoogleMapIntegration/mymap.html.If you did all the steps correctly, you should now see a map centered on Palo Alto, California

     

    If you’re getting an error about the key being registered for a different site, then you probably entered in the wrong URL when you signed up for the API key. Go back to Step 2 of this section and make sure you enter the domain name that you see in the browser window for the page.

     

    If you’re not seeing a map and are just seeing a blank page, you probably made a copy & paste error. Go back to Step 4, and make sure you copy and paste all and only the HTML given to you in the textbox.


Bookmark and Share


Bookmark and Share

How to add flash files to asp.net web pages

Click Here to visit Zonix’s Website

<object classid=”clsid:D27CDB6E-AE6D-11cf-96B8-444553540000″ codebase=”http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=5,0,0,0” width=”540″ height=”400″ hspace=”10″ id=”ShockwaveFlash1VIEWASTEXT”>

<param name=movie value=”Flash File Path”>
<PARAM NAME=”quality” VALUE=”high”>
<PARAM NAME=”bgcolor” VALUE=”#FFFFFF”>
<param name = “wmode” value = “transparent”/>

<embed src=”Flash File Path” width=”540″ height=”400″ loop=”false” quality=high pluginspage=”http://www.macromedia.com/shockwave/download/index.cgi?P1_Prod_Version=ShockwaveFlash” type=”application/x-shockwave-flash”>
</embed>

</object>

Remember to place the Flash File inside the Project only.and give a relative path for the file.

You can also display a Flash file in <iframe>.

Eg:

<

iframe src=”Flash File Path height=”146px” width=”575px”></iframe>



Bookmark and Share