NetProgrammingHelp.com

Asp.Net,C#,Ajax,Sql server,silverlight,Javascript codes exambles articles,Programming exambles

Recent Posts

  • Silverlight Data Binding OneWay TwoWay Asp.Net C# XAML
  • Accessing Executing Running Stored Procedure/Function/Package Using Linq Silverlight Xaml
  • Creating/Developing Silverlight Data Driven Applications Using Wcf Linq Asp.Net C#
  • Deploying Manually Publish XBAP Wpf Browser Application in IIS Asp.Net C# XAML
  • Creating Server and client using remoting asp.net c#
  • Create/Build wcf autocomplete/autoextender textbox using database linq c# asp.net
  • Essentials for Creating/Developing/Programming silverlight project tools wpf xaml
  • Working with xml data type variable sql server 2008 asp.net c#
  • Encrypting Stored Procedure sql server 2008 asp.net c#
  • sql server build-in functions aggregate functions asp.net c#
  • -->

    Silverlight Data Binding OneWay TwoWay Asp.Net C# XAML

    Posted by James Add Comments

    Introduction:
    In this article,i am going explain about,what are the binding options and modes available in silverlight application.

    Main:
    Data binding is simply a process of displaying data to the end user.Silverlight contains a rich data binding platform
    that will help us to fetching data in faster and using less code.

    Databinding needs atleast the below two items,

    1.UI elements, (refers UI element,element property)
    2.A Collection Source (refers a datasource and its path)

    Next we need to find a path/protocol for connecting the above two items,Normally we will use datacontext for mapping
    data into UI elements.

    The DataContext is the source of all entities mapped over a connection. It tracks changes that you made to all
    retrieved entities and maintains an “identity cache” that guarantees that entities retrieved more than one time are
    represented by using the same object instance.

    Different Modes of Data Binding,

    The direction of the flow of data in a data binding scenario is controlled by the Mode property of the Binding.

    The data has flowed from the source to the target (the UI controls). However, it can also flow in the opposite direction,
    that is, from the target towards the source. This way, not only can data binding help us in displaying data, but also in
    persisting data.

    The data flow from source to target can be classified into modes ONEWAY,TWOWAY,ONLY ONCE,

    A OneTime binding should be the default for data that does not change when displayed to the user. When using this mode,
    the data flows from source to target. The target receives the value initially during loading and the data displayed in
    the target will never change. Quite logically, even if a OneTime binding is used for a TextBox, changes done to the data
    by the user will not flow back to the source.

    We should use a OneWay binding for binding scenarios in which we want an up-to-date display of data. Data will flow from source to target here also, but every change in the values of the source properties will propagate to a change of the displayed values. Think of a stock market application where updates are happening every second. We need to push the updates to the UI of the application.

    <TextBlock x:Name="CurrentBalanceValueTextBlock"
               Text="{Binding CurrentBalance, Mode=OneWay}" >
    </TextBlock>

    The TwoWay bindings can help in persisting data. The data can now flow from source to target, and vice versa. Initially, the values of the source properties will be loaded in the properties of the controls. When we interact with these values (type in a textbox, drag a slider, and so on), these updates are pushed back to the source object. If needed, conversions can be done in both directions.

    Add a note hereThere is one important requirement for the OneWay and TwoWay bindings. If we want to display up-to-date values, then the INotifyPropertyChanged interface should be implemented. The OneTime and OneWay bindings would have the same effect, even if this interface is not implemented on the source. The TwoWay bindings would still send the updated values if the interface was not implemented; however, they wouldn’t notify about the changed values. It can be considered as a good practice to implement the interface, unless there is no chance that the updates of the data would be displayed somewhere in the application. The overhead created by the implementation is minimal.

    <TextBlock x:Name="CurrentBalanceValueTextBlock"
               Text="{Binding CurrentBalance, Mode=TwoWay}" >
    </TextBlock>

    conclusion:
    Hope this helps,
    Happy coding.

    Accessing Executing Running Stored Procedure/Function/Package Using Linq Silverlight Xaml

    Posted by James one Commented

    Introduction:
    In this article i am going to explain about how to access a stored procedure using linq.

    Main:
    In data driven business applications,instead of queries of we need to use either stored
    procedure or functions.Linq provide a object oriented,structured approach for accessing
    stored procedures.

    Just follow the below steps,

    1.Open a new silverlight application and named it LinqSpDemo,

    2.Right Click the project and add Linq to Sql Classes and named it Callingsplinq.dbml,

    3.click view and open server explorer

    4.Right click stored procedure tab and select Add New Stored Procedure,In this examble
    i am going to creating a stored procedure for inserting empid and empname in emp table,
    15

    ALTER PROCEDURE dbo.AddEmployee
    	 (
     @name nvarchar(50),
     @id int
     )
    AS
    BEGIN
      INSERT INTO Emp (Empid,FirstName)
      VALUES (@id,@name)
     
    END

    5.Open Tables tab and drag and drop Emp table,

    6.In the same way drag and drop AddEmployee Procedure,

    7.Create link between Table and stored procedure,(Right Click and Emp and select Configure
    Behaviour (see the below screenshot)),
    25

    8.Map the table columns into stored procedure parameters,
    33

    9.Click Save,

    10.Right click the select and add a wcf service,

    public void InsertEmployee(int Employeeid,string EmployeeName)
            {
                //string Empname = "Rouville Fisher";
     
                Emp objEmp = new Emp() { Empid = Employeeid, FirstName = EmployeeName };
     
                var spcon = new CallingSpLinqDataContext();
                spcon.Emps.InsertOnSubmit(objEmp);
                spcon.SubmitChanges();
     
            }

    11.Add the service Reference to client,and just call it,

    void objclent_InsertEmployeeCompleted(object sender, EmpServiceReference.InsertEmployeeCompletedEventArgs e)
            {            
                MessageBox.Show("Employee Details Inserted");
            }
     
            private void Click_Click(object sender, RoutedEventArgs e)
            {
                EmpServiceReference.EmpProcServiceClient objclent = new EmpServiceReference.EmpProcServiceClient();
                objclent.InsertEmployeeCompleted +=new EventHandler<EmpServiceReference.InsertEmployeeCompletedEventArgs>(objclent_InsertEmployeeCompleted);
                EmpServiceReference.InsertEmployeeRequest objEmpRequest = new EmpServiceReference.InsertEmployeeRequest();
                objEmpRequest.Employeeid = Convert.ToInt32(Empid1.Text);
                objEmpRequest.EmployeeName = EmpName1.Text.Trim();
                objclent.InsertEmployeeAsync(objEmpRequest);           
     
            }

    44
    thatsit,

    Conclusion:
    Hope this helps,
    Happy coding.

    Creating/Developing Silverlight Data Driven Applications Using Wcf Linq Asp.Net C#

    Posted by James one Commented

    Introduction:
    In this article,i am going to demonstrate how to create a data driven applications using
    silverlight linq and wcf.

    Main:

    Open Visual Studio 2010 with the Silverlight 4 tools installed. Once inside the Integrated Development Environment (IDE), Go to File | New | Project…. In the New Project dialog that appears, select the Silverlight node under Visual C# and select Silverlight Application. Name the application as SilverlightEmployeeBrowser and click the OK button.
    13

    In the dialog that appears as shown in the next screenshot, select ASP.NET Web Application Project as the type of web project that will be used to host the Silverlight application. Also, make sure that Silverlight 4 is selected as the
    target version of Silverlight 4.
    23

    Right-click on SilverlightEmployeeBrowser.Web and select Add | New Item…. Add a Silverlight-enabled WCF Service by
    selecting the Silverlight node under Visual C# and name it as EmployeeService. Click the Add button. Two files are
    added, namely, EmployeeService.svc and EmployeeService.svc.cs.(Getting or Declaring Ours data manipulation here),
    32
    For ex in EmployeeService.svc.cs,

    using System;
    using System.IO;
    using System.Data;
    using System.Linq;
    using System.Collections.Generic;
    using System.Runtime.Serialization;
    using System.ServiceModel;
    using System.ServiceModel.Activation;
     
    namespace SilverlightEmployeeBrowser.Web
    {
        [ServiceContract(Namespace = "")]
        [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
        public class EmployeeService
        {
            [OperationContract]
            public List<Employee> GetEmployees()
            {
                return new List<Employee>
        {
          new Employee
          {
            Name = "Peter",
            Salary = 100,
            Location = "Dallas",
            Country = "USA"
          },
          new Employee
          {
            Name = "Rouville Fisher",
            Salary = 200,
            Location = "London",
            Country = "United Kingdom"
          },
          new Employee
          {
            Name = "James",
            Salary = 15000,
            Location = "NY",
            Country = "USA"
          },
          new Employee
          {
     
            Name = "Marciya",
            Salary = 230,
            Location = "NPD",
            Country = "USA"
          }
        };
     
            }
            [DataContract]
            public class Employee
            {
                [DataMember]
                public string Name { get; set; }
                [DataMember]
                public string Location { get; set; }
                [DataMember]
                public string Country { get; set; }
     
                [DataMember]
                public double Salary { get; set; }
            }
        }
    }

    Next Right Click SilverlightEmployeeBrowser and select Add service reference and add Employeeservice we build,
    43
    Open MainPage.xaml and design it necessary,
    for ex,

    <UserControl x:Class="SilverlightEmployeeBrowser.MainPage"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        mc:Ignorable="d"
        d:DesignHeight="300" d:DesignWidth="400">
     
        <Grid x:Name="LayoutRoot" Width="400" Height="300"
          Background="SkyBlue">
            <Grid.RowDefinitions>
                <RowDefinition Height="50"></RowDefinition>
                <RowDefinition></RowDefinition>
            </Grid.RowDefinitions>
            <ComboBox x:Name="EmployeeComboBox" Width="250"
                SelectionChanged="EmployeeComboBox_SelectionChanged"
                DisplayMemberPath="Name"
                VerticalAlignment="Center">
            </ComboBox>
            <Grid x:Name="EmployeeDetailGrid" Grid.Row="1"
            VerticalAlignment="Top">
                <Grid.RowDefinitions>
                    <RowDefinition></RowDefinition>
                    <RowDefinition></RowDefinition>
                    <RowDefinition></RowDefinition>
                    <RowDefinition></RowDefinition>
                </Grid.RowDefinitions>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition></ColumnDefinition>
                    <ColumnDefinition></ColumnDefinition>
                </Grid.ColumnDefinitions>
                <TextBlock x:Name="NameTextBlock"
                   Grid.Row="0"
                   Grid.Column="0"
                   FontWeight="Bold"
                   Text="Name: "
                   HorizontalAlignment="Right">
                </TextBlock>
                <TextBlock x:Name="NameValueTextBlock"
                   Grid.Row="0"
                   Grid.Column="1"
                   Text="{Binding Name}">
                </TextBlock>
                <TextBlock x:Name="LocationTextBlock"
                   Grid.Row="1"
                   Grid.Column="0"
                   FontWeight="Bold"
                   Text="Location: "
                   HorizontalAlignment="Right">
     
                </TextBlock>
                <TextBlock x:Name="LocationValueTextBlock"
                   Grid.Row="1"
                   Grid.Column="1"
                   Text="{Binding Location}">
                </TextBlock>
                <TextBlock x:Name="CountryTextBlock"
                   Grid.Row="2"
                   Grid.Column="0"
                   FontWeight="Bold"
                   Text="Country: "
                   HorizontalAlignment="Right">
                </TextBlock>
                <TextBlock x:Name="CountryValueTextBlock"
                   Grid.Row="2"
                   Grid.Column="1"
                   Text="{Binding Country}">
                </TextBlock>
                <TextBlock x:Name="PriceTextBlock"
                   Grid.Row="3"
                   Grid.Column="0"
                   FontWeight="Bold"
                   Text="Salary: "
                   HorizontalAlignment="Right">
                </TextBlock>
                <TextBlock x:Name="SalaryValueTextBlock"
                   Grid.Row="3"
                   Grid.Column="1"
                   Text="{Binding Salary}">
                </TextBlock>
            </Grid>
        </Grid>
     
    </UserControl>

    In MainPage.xaml.cs,

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net;
    using System.Windows;
    using System.Windows.Controls;
    using System.Windows.Documents;
    using System.Windows.Input;
    using System.Windows.Media;
    using System.Windows.Media.Animation;
    using System.Windows.Shapes;
     
    namespace SilverlightEmployeeBrowser
    {
        public partial class MainPage : UserControl
        {
            public MainPage()
            {
                InitializeComponent();
                EmployeeService.EmployeeServiceClient EmpClient = new EmployeeService.EmployeeServiceClient();
                EmpClient.GetEmployeesCompleted +=new EventHandler<EmployeeService.GetEmployeesCompletedEventArgs>(EmpClient_GetEmployeesCompleted);
                EmpClient.GetEmployeesAsync();
            }
     
            void EmpClient_GetEmployeesCompleted(object sender,EmployeeService.GetEmployeesCompletedEventArgs e)
            {
                EmployeeComboBox.ItemsSource = e.Result;
            }
     
     
                private void EmployeeComboBox_SelectionChanged(object sender,
              SelectionChangedEventArgs e)
                    {
                      EmployeeDetailGrid.DataContext = (sender as ComboBox)
                        .SelectedItem as EmployeeService.EmployeeServiceEmployee;
                    }
     
     
        }
    }

    Build it and run it thatsit!
    55

    Conclusion:
    Please use forum for further more doubts/discussion/queries for this article
    Happy coding.

    Join the forum discussion on this post

    Deploying Manually Publish XBAP Wpf Browser Application in IIS Asp.Net C# XAML

    Posted by James Add Comments

    Introduction:
    In this article,i am going to demonstrate how to deploy a XBAP (wpf browser) application in IIS using asp.net.

    Main:
    Build and run an XBAP application,
    8

    Create a new virtual directory,

    9

    101

    Copy the contents xbab bin and paste into the virtual dir,

    12

    Now try this url in internet explorer,

    http://localhost/MyFirstXBapApp/MyFirstXBapApp.xbap

    7

    thatsit!

    Conclusion:
    Hope this helps,
    Happy coding.

    Join the forum discussion on this post

    Creating Server and client using remoting asp.net c#

    Posted by James one Commented

    Introduction:
    In this article http://netprogramminghelp.com/aspnet/how-to-useperformutilizeaccess-aspnet-remoting-understanding-remoting-concepts/ we allready discussed about remoting.In this article i am going to explain with one sample server and client.

    Main:

    This demonstration contains 3 programs,

    Step 1: Remote Object Creation,
    Step 2: Remote Server,
    Step 3: Remote Client,

    Remote Object Creation,
    11
    Creating remote object using marshalbyrefobject,

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
     
    namespace MyRemoteObject
    {
        public class MyRemotingObjectClass : MarshalByRefObject
        {
            public double addition(double i, double j)
            {
                Console.WriteLine("Adding {0},{1}", i, j);
                return i + j;
            }
     
        }
    }

    Remote Server,
    31
    Registering the remote object using tcpchannel,

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Net;
    using System.Runtime.Remoting.Channels.Tcp;
    using System.Runtime.Remoting.Channels;
    using System.Runtime.Remoting;
     
    using MyRemoteObject;
     
    namespace MyRemotingServer
    {
        class Program
        {
            static void Main(string[] args)
            {
     
                //Creating new Tcp Server Channel
                TcpServerChannel local_tcp = new TcpServerChannel(7777);
                //Registering Tcp Server Channel
                ChannelServices.RegisterChannel(local_tcp,false);
     
                //register the service type
                RemotingConfiguration.RegisterWellKnownServiceType(
                     typeof(MyRemotingObjectClass), "MyRemotingObjectClassServer",
                    //Singleton:Each incoimg request/message services by same object instance
                     WellKnownObjectMode.Singleton
                    //SingleCall:Each incoming request/message serviced by new object instance
                    // WellKnownObjectMode.SingleCall
                    );
     
     
                Console.WriteLine("Click Enter to Close");
                Console.ReadLine();
            }
        }
    }

    Remote Client,
    22
    Activating and getting results from remote object,

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Runtime.Remoting;
     
    using MyRemoteObject;
     
     
    namespace MyRemotingClient
    {
        class Program
        {
            static void Main(string[] args)
            {
     
                //Make call to activate remote object,
                MyRemotingObjectClass objcls =
                        (MyRemotingObjectClass)Activator.GetObject(typeof(MyRemotingObjectClass),
                        "net.tcp://124.123.164.48:7777");
     
                //functionality part,call the remote object method
                double a=10,b=30;
                double sum1 = objcls.addition(a,b);
                Console.WriteLine("Addition Result is:{0}", sum1);
                Console.ReadLine();
     
            }
        }
    }

    conclusion:
    Hope this helps,
    Happy coding.

    Join the forum discussion on this post

    Create/Build wcf autocomplete/autoextender textbox using database linq c# asp.net

    Posted by James Add Comments

    Introduction:
    In this article,i am going to explain about how to create auto complete textbox using wcf with sample application.

    Main:
    A service for the auto-complete extender can have any number of operations, but all have the same prototype. Here’s
    a possible contract,

    public string[] GetExtendedText(string prefixText, int count)
      {
         GetAutoTextDataContext objautocontext = new GetAutoTextDataContext();
     
         string[] fulltext = (from n in objautocontext.Emps
                              where n.FirstName.StartsWith(prefixText)
                              select n.FirstName).ToArray();
     
         return fulltext;
       }

    in this demonstartion we are just going to call WCF service instead of web service,

    Create a new web application and named it WcfAutoCompleteExtender,
    wcfae11

    Add a class under app_data dir and named it AutoExtender.cs,and define the service contract,

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ServiceModel;
    using System.ServiceModel.Activation;
    using System.ServiceModel.Web;
     
    namespace WcfAutoCompleteExtender
    {
        [ServiceContract(Namespace = "NetProgrammingHelp", Name = "AutoCompleteTextBoxService")]
        public interface IAutoCompleteSvc
        {
            [OperationContract]
            string[] GetExtendedText(string prefixText, int count);
        }
        [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
        public class AutoCompleteservice : IAutoCompleteSvc
        {
            public string[] GetExtendedText(string prefixText, int count)
            {
                GetAutoTextDataContext objautocontext = new GetAutoTextDataContext();
     
                string[] fulltext = (from n in objautocontext.Emps
                                     where n.FirstName.StartsWith(prefixText)
                                     select n.FirstName).ToArray();
     
                return fulltext;
            }
     
     
        }
    }

    define the ajax behaviours in web.config,

    <system.serviceModel>
        <behaviors>
          <endpointBehaviors>
            <behavior name="AjaxServiceBehaviour">
              <enableWebScript />
            </behavior>
          </endpointBehaviors>
        </behaviors>
        <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
        <services>
          <service name="WcfAutoCompleteExtender.AutoCompleteservice">
            <endpoint address=""
                      behaviorConfiguration="AjaxServiceBehaviour"
                      binding="webHttpBinding"
                      contract="WcfAutoCompleteExtender.IAutoCompleteSvc" />
          </service>
        </services>
      </system.serviceModel>

    Add a new text file under solution and named it AutoService.svc,and define the endpoint of this service

    <%@ ServiceHost
        Service="WcfAutoCompleteExtender.AutoCompleteservice"
        Language="C#"
        Debug="true"
        CodeBehind="~/App_Data/AutoExtender.cs" %>

    Next call the service using auto extender,

    <form id="form1" runat="server">
        <ajaxToolkit:ToolkitScriptManager ID="ScriptManager1" runat="server">   
        </ajaxToolkit:ToolkitScriptManager>
     
         <asp:TextBox runat="server" ID="myTextBox" Width="300" autocomplete="off" />        
                <ajaxToolkit:AutoCompleteExtender
                    runat="server" 
                    ID="autoComplete1" 
                    enabled="true"
                    TargetControlID="myTextBox"
                    ServicePath="~/AutoService.svc"
                    ServiceMethod="GetExtendedText"
                    MinimumPrefixLength="1" 
                    CompletionSetCount="20" 
                    CompletionInterval="0000"
                    EnableCaching="true">
                </ajaxToolkit:AutoCompleteExtender>     
     
     
        </form>

    wcfae23

    thatsit!

    Conclusion:
    Hope this helps,
    Happy coding.

    Essentials for Creating/Developing/Programming silverlight project tools wpf xaml

    Posted by James one Commented

    Introduction:
    In this article, i am going to explain about what are the things needed for creating developing programming
    silverlight project.

    Main:

    What are key skills needed for learning silverlight?

    Must Comfortable with visual studio,
    Must Comfortable with XML,
    Must Comfortable with CLR,
    Knows the difference between DLL and HTML,

    What is new in silverlight?

    We can separate presentation and logic using XAML,
    Solid XAML support,

    What are the tools needed for installing silverlight?

    The basic common tools are,

    1.visual studio 2010 or visual studio 2008 sp1 with visual studio tools for silverlight,
    2.The Silverlight Runtime,
    3.Silverlight Toolkit,
    4.Expression Blend 3,

    You can download all the above upto date links and installation instructions into

    http://silverlight.net/GetStarted/

    For silverlight toolkit we need to reach,

    http://codeplex.com/silverlight

    ok,now its a time for creating silverlight project,

    Click File — Select New Project,
    Select a new silverlight application,and then click OK,
    21

    Accept the asp.net web application project and then click OK,
    3
    Copy the below in mainpage.xaml,

    <UserControl x:Class="FirstSilverlightApp.MainPage"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        mc:Ignorable="d"
        d:DesignHeight="300" d:DesignWidth="400">
     
        <Grid x:Name="LayoutRoot" Background="White">
            <Button Width="200" Height="50" Content="Welcome to Silverlight"></Button>
        </Grid>
    </UserControl>

    Press F5,
    42
    thatsit,

    Conclusion:
    Hope this helps,
    Happy coding.

    Working with xml data type variable sql server 2008 asp.net c#

    Posted by James one Commented

    Introduction:
    In this article,i am going to explain about how to use the new xml data type as variable, and how to
    define the xml data.

    Main:

    The ‘xml’ datatype helps us to declare xml data in sql server 2008.

    DECLARE @xmlData AS XML
    SET @xmlData='
    <Customers>
      <CustomerID>TELRK</CustomerID>
      <CompanyName>Telerik, Inc</CompanyName>
      <ContactName>Stephen Forte</ContactName>
      <ContactTitle>Sales Representative</ContactTitle>
      <Address>5–9 Union Square West</Address>
      <City>New York</City>
      <PostalCode>10028</PostalCode>
      <Country>USA</Country>
      <Phone>030-0074321</Phone>
      <Fax>030-0076545</Fax>
      </Customers>'
    SELECT @xmlData

    How to insert a xml data into table?

    --Insert Static XML via a variable
    DECLARE @xmlData AS XML
    SET @xmlData = '
    <Orders>
      <Order>
        <OrderID>5</OrderID>
        <CustomerID>65</CustomerID>
        <OrderAmount>25</OrderAmount>
      </Order>
    </Orders>'
    --insert into the table
    INSERT INTO OrdersXML (OrderDocID, xOrders) Values (1, @xmlData)

    You can insert XML into these columns in a variety of other ways: XML Bulk Load, loading from an XML
    variable (as shown above),

    How to use xml bulk load?

    In SQL Server 2000, XML Bulk Load allowed users to load large XML documents on the client side.
    XML Bulk Load works by reading the XML and producing SQL INSERT statements that run on the client
    in a batched fashion. SQL Server 2005 and 2008 greatly enhance XML Bulk Load by allowing it to run
    on the server and to load directly into an xml data type column.

    Add a note hereUsing the enhanced XML Bulk Load requires using the system rowset provider function
    OPENROWSET and specifying the BULK provider,

    --create a table with an xml column
    CREATE TABLE tblXmlCustomers
     (CustomerID int PRIMARY KEY IDENTITY,
     
      CustomerXML xml NOT NULL)
     
    --this file will load 1 record in (SINGLE_CLOB); for more records use a format file)
    INSERT INTO tblXmlCustomers)
     SELECT * FROM OPENROWSET(BULK 'C:\customer_01.xml', SINGLE_CLOB) AS XmlData)

    Querying xml data using xquery?

    XQuery provides a native and elegant way to query XML data.

    DECLARE @XML xml
    SET @XML='
    <catalog>
      <book category="ITPro">
        <title>Windows Step By Step</title>
        <author>Bill Zack</author>
        <price>49.99</price>
      </book>
      <book category="Developer">
        <title>Developing ADO .NET</title>
        <author>Andrew Brust</author>
        <price>39.93</price>
      </book>
      <book category="ITPro">
        <title>Windows Cluster Server</title>
        <author>Stephen Forte</author>
        <price>59.99</price>
      </book>
    </catalog>'
     
    SELECT @XML.query('
       for $b in /catalog/book
       where $b/@category="ITPro"
       order by $b/author[1] descending
       return ($b)')

    Conclusion:
    Hope this helps,
    Happy coding.

    Encrypting Stored Procedure sql server 2008 asp.net c#

    Posted by James 2 Comments

    Introduction:
    In this article,i am going to explain about how to encrypt a stored procedure and how to set a stored
    procedure security while using exec statement.

    Main:
    Just like a view, stored procedure Transact-SQL definitions can have their contents encrypted in the database,
    removing the ability to read the procedure’s definition.

    In order to encrypt the stored procedure, WITH ENCRYPTION is designated after the name of the new stored procedure,
    as this next example demonstrates:

    CREATE PROCEDURE dbo.usp_SEL_NetPayHistory
    WITH ENCRYPTION
    AS
     
    SELECT NetID, RateChangeDate, Rate, PayFrequency, ModifiedDate
    FROM HumanResources.NetPayHistory
     
    GO

    Once you’ve created WITH ENCRYPTION, you’ll be unable to view the procedure’s text definition:

    – View the procedure’s text
    EXEC sp_helptext usp_SEL_NetPayHistory

    The text for object ‘usp_SEL_NetPayHistory’ is encrypted.

    Encryption can be defined using either CREATE PROCEDURE or ALTER PROCEDURE, but be sure to save your source code, as
    the existing encrypted text cannot be decrypted easily.

    Using EXECUTE AS to Specify the Procedure’s Security Context

    The WITH EXECUTE AS clause allows you to specify the security context that a stored procedure executes under,
    overriding the default security of the stored procedure caller. In this case, security context refers to the
    permissions of the user executing the stored procedure.

    You have the option to execute a stored procedure under

    * The security context of the caller
    * The person who authored or last altered the procedure
    * A specific login (if you have IMPERSONATE permissions for that person’s login)
    * The owner of the stored procedure

    CREATE USER James
    GO
    GRANT EXEC ON usp_DEL_EmployeeSalary to James
     
    CREATE USER James
     
    GRANT SELECT ON OBJECT::HumanResources.Employee TO James
    GO

    Conclusion:
    Hope this helps,
    Happy coding.

    sql server build-in functions aggregate functions asp.net c#

    Posted by James Add Comments

    Introduction:
    In this article,i am going to explain about how to effectively use/utilize the sql-server build in
    aggregate functions in t-sql code.

    Main:
    Aggregate Functions

    Aggregate functions:- are used to perform a calculation on one or more values, resulting in a single value.
    An example of a commonly used aggregate function is SUM, which is used to return the total value of a set
    of numeric values.

    AVG:-The AVG aggregate function calculates the average of non-NULL values in a group.

    CHECKSUM_AGG:-The CHECKSUM_AGG function returns a checksum value based on a group of rows, allowing you to
    potentially track changes to a table. For example, adding a new row or changing the value of a column that
    is being aggregated will usually result in a new checksum integer value. The reason why I say “usually” is
    that there is a possibility that the checksum value does not change even if values are modified.

    COUNT:-The COUNT aggregate function returns an integer data type showing the count of rows in a group.

    COUNT_BIG:-The COUNT_BIG function works the same as COUNT, only it returns a bigint data type value.

    GROUPING:-The GROUPING function returns 1 (True)or 0 (False) depending on whether a NULL value is due
    to a CUBE, ROLLUP, or GROUPING SETS operation. If False, the column expression NULL value is from the
    natural data. See Chapter 1′s recipe “Revealing Rows Generated by GROUPING.”

    MAX:-The MAX aggregate function returns the highest value in a set of non-NULL values.

    MIN:-The MIN aggregate function returns the lowest value in a group of non-NULL values.

    SUM:-The SUM aggregate function returns the summation of all non-NULL values in an expression.

    STDEV:-The STDEV function returns the standard deviation of all values provided in the expression based
    on a sample of the data population.

    STDEVP:-The STDEVP function also returns the standard deviation for all values in the provided expression,
    only it evaluates the entire data population.

    VAR:-The VAR function returns the statistical variance of values in an expression based on a sample of the
    provided population.

    VARP:-The VARP function also returns the variance of the provided values for the entire data population of
    the expression.

    Returning the Average of Values:-
    The AVG aggregate function calculates the average of non-NULL values in a group. For example:

    -- Average Product Review by Product
    SELECT ProductID,
          AVG(Rating) AvgRating
    FROM Production.ProductReview
    GROUP BY ProductID

    Returning ROW COUNT:-

    SELECT  Shelf,
          COUNT(ProductID) ProductCount
    FROM  Production.ProductInventory
    GROUP BY Shelf
    ORDER BY Shelf

    Min and Max:

    SELECT  MIN(Rating) MinRating,
          MAX(Rating) MaxRating
    FROM  Production.ProductReview

    Returning Sum of the values,

    SELECT  AccountNumber,
          SUM(TotalDue) TotalDueBySalesOrderID
    FROM Sales.SalesOrderHeader
    GROUP BY AccountNumber
    ORDER BY AccountNumber

    Using Statistical Aggregate Functions

    In this recipe, I’ll demonstrate using the statistical functions VAR, VARP, STDEV, and STDEVP.

    The VAR function returns the statistical variance of values in an expression based on a sample of the provided population
    (the VARP function also returns the variance of the provided values for the entire data population of the expression).

    This first example returns the statistical variance of the TaxAmt value for all rows in the Sales.SalesOrderHeader table:

    SELECT  VAR(TaxAmt) Variance_Sample,
          VARP(TaxAmt) Variance_EntirePopulation
    FROM Sales.SalesOrderHeader

    Conclusion:
    Hope this helps,
    Happy coding.

    Software