SQL Server Fucntion to Split Comma-Seperated Strings into table

Split function in general would have comma-separated string value to be split into individual strings.

The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

CREATE  FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000)) 
as  
begin 
declare @idx int
declare @slice varchar(8000) 
select @idx = 1 
if len(@String)<1 or @String is null return 
while @idx!= 0 
begin 
set @idx = charindex(@Delimiter,@String) 
if @idx!=0 
set @slice = left(@String,@idx - 1) 
else 
set @slice = @String 
if(len(@slice)>0)
insert into @temptable(Items) values(@slice) 
set @String = right(@String,len(@String) - @idx) 
if len(@String) = 0 break 
end 
return

split function can be Used as

       select * from dbo.split ( '9899999999,9877889876,9865489678' , ',' )


would return

Hope this helps.

How to Import Excel File into SQL Server using SQLBULK in ASP.net

This example explains how to upload excel file, read Excel file data,  save Excel file data and import into SQL Server using SQLBULK in ASP.Net.

Step:1 Create a Excel file like:

Step:2 Create a Sql table in database like:

Step:3 Now, add the code in "Default.aspx"

<asp:FileUpload ID="fupUpload" runat="server" />

<asp:Button ID="btnImport" Font-Bold="true" ForeColor="White"

BackColor="#136671" Height="23px" runat="server" Text="Import Excel Data"
onclick="btnImport_Click" />


Step:4  Add the code in "Default.aspx.cs"

Add these NameSpace

using System.IO;
using System.Data.OleDb;
using System.Data;


Write the code in Click Event of Import Button

protected void btnImport_Click(object sender, EventArgs e)
{
 string strFilepPath;
 DataSet ds = new DataSet();
 string strConnection = ConfigurationManager.ConnectionStrings
                          ["connectionString"].ConnectionString;
 if (fupUpload.HasFile)
 {
  try
  {
    FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
    string ext = fi.Extension;
    if (ext == ".xls" || ext == ".xlsx")
    {
     string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
     string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
     strFilepPath = DirectoryPath + fupUpload.FileName;      
     Directory.CreateDirectory(DirectoryPath);
     fupUpload.SaveAs(strFilepPath);   
     string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
                      + strFilepPath + ";Extended Properties=\"Excel 12.0 
                      Xml;HDR=YES;IMEX=1\"";
     OleDbConnection conn = new OleDbConnection(strConn);
     conn.Open();     
     OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
     OleDbDataAdapter da = new OleDbDataAdapter(cmd);
     da.Fill(ds);
     DeleteExcelFile(fupUpload.FileName); // Delete File Log
     SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection, 
                                   SqlBulkCopyOptions.KeepIdentity);
     sqlBulk.DestinationTableName = "Table_1";
     sqlBulk.WriteToServer(ds.Tables[0]);
     conn.Close();
     sqlBulk.Close();      
     ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
        "alert('Excel file successfully imported into DB');", true);
     return;
    }     
    else
    {
      ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
                    "alert('Please upload excel file only');", true);
     return;
    }
  }   
  catch (Exception ex)
   {
    DeleteExcelFile(fupUpload.FileName);       
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
      "alert('error occured: " + ex.Message.ToString() + "');", true);
    return;
   }
  }  
 else
  { 
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
                        "alert('Please upload excel file');", true);
   return;
  }
}

protected void DeleteExcelFile(string Name)
{              
 if (Directory.Exists(Request.PhysicalApplicationPath +   
                                           "UploadExcelFile\\"))
   {      
    string[] logList = Directory.GetFiles(Request.PhysicalApplicationPath 
                       + "UploadExcelFile\\", "*.xls");
     foreach (string log in logList)
      {         
        FileInfo logInfo = new FileInfo(log);
        string logInfoName = logInfo.Name.Substring(0,  
                             logInfo.Name.LastIndexOf('.'));
        if (logInfoName.Length >= Name.Length)
         {            
          if (Name.Equals(logInfoName.Substring(0, Name.Length)))
           {
             logInfo.Delete();
           }
         }
      }
   }
}





and the result will be be like as:

Entity Framework

What is Entity Framework ?

• Entity Framework based on ORM (object relation mapping).

• Entity Framework enables developers to work with relational data, eliminate the need for most of the data access plumbing code that developers usually need to write.

• It uses LINQ (Language Integrated Query) to retrieve and manipulate data as strongly typed objects.

 

Advantages - 

• One common syntax "LINQ" for all object queries.
• Fast
• Easy to implement.
• Less coding required.

Now, take an example for How to use it:

Insertion, Updation and Deletion with Entity Framework

Step:1 First, create a ASP.Net empty web application named "WebAppEntity". Right click on solution, add new item > add ADO.NET Data Entity Model named it as "BusinessObjects.edmx"

Now check your Web.config file, it will create connection string automatically. And check your BusinessObjects.designer.cs also, It looks like:

Open/Expand Contexts,

Step:2 File > Add > New Project > ASP.NET Empty Web Application named "Entity Framework".

• Copy connection string of "WebApp Entity Web.config file" to "Entity Framework Web.config file".

• Add two "Web Refrences" in "Entity Framework"

                       1. Projects > "WebAppEntity"
                       2. System.Web.Entity

• Create a aspx page named "EntityFirst.aspx" for create, update and delete the records.

Now, Solution looks like:

Step:3 Now Add a code into "EntityFirst.aspx" and  "EntityFirst.aspx.cs"     

Add three button for save, update and delete in EntityFirst.aspx like:

<asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" />
<asp:Button ID="btnupdate" runat="server" Text="Update" OnClick="btnupdate_Click" />
<asp:Button ID="btndelete" runat="server" Text="Delete" OnClick="btndelete_Click" />


Now, add code in code behind like:

using  WebAppEntity;


LeadManagementTestEntities LeadDB = new LeadManagementTestEntities();

protected void btnsave_Click(object sender, EventArgs e)

{
aaa objaa = new aaa();

objaa.user = "Name";

objaa.city = "Gurgaon";

objaa.designation = "Software Engineer";

LeadDB.aaas.AddObject(objaa);
LeadDB.SaveChanges();
lblmsg.Text = "Record saved successfully";

}

protected void btnupdate_Click(object sender, EventArgs e)
{
aaa objaa = LeadDB.aaas.SingleOrDefault(p => p.user == "Ruchi");
objaa.city = "Delhi";
LeadDB.SaveChanges();
lblmsg.Text = "Record updated successfully";
}

protected void btndelete_Click(object sender, EventArgs e)
{
aaa objaa = LeadDB.aaas.SingleOrDefault(p => p.user == "Name");
LeadDB.aaas.DeleteObject(objaa);
LeadDB.SaveChanges();
lblmsg.Text = "Record deleted successfully";
}

Now, we are done. Run the application and test it.

Paging in SQL Server 2011 (Denali)

SQL Server 2011 has been launched with exciting features and lots enhancement for SQL developers.
You can see syntax in MSDN.

Lets start, how paging works in SQL Server 2011:

Create Table with data like so:

Now lets use Order By with Offset Clause. When you specify Order By Clause with Offset then number of rows specified with Offset are ignored and remaining records are returned.

Select * from PagingData Order By Id Offset 3 rows

Output like so:

So you can see number of rows specified with Offset has been skipped.

Note: SQL will throw error if Order By is not used in query.

Now Limit the numbers of rows after Offset like

Select * from PagingData Order By Id Offset 3 Rows Fetch next 3 rows only

Now Let’s see, How We can use Stored Procedure to return Page Wise Data.

  Create Procedure Usp_GetPageWisePagingData  
    (  
    @PageNumber Int,  
    @RecordPerPage Int  
    )  
    AS  
    Begin  
    Select * From PagingData  
    Order By Id  
    Offset ((@PageNumber-1)*@RecordPerPage) Rows  
    Fetch Next @RecordPerPage Rows Only  
    End  
    Go   

Accordingly Passed two parameters named PageNumber (which is record page number), and RecordPerPage (which is number of record) in stored procdure, we will get the result paging wise.