How to Generate Date wise Sales Order Number in SQL Server?

Step 1:- Create the table

Create table TestSalesRecord

(

       SalesOrderID int identity,

SalesOrderNumber nvarchar(25) not null,

       InsertDate datetime default(getdate())

)

Step 2:- Create the view for get current date

create view view_CurrentDateWithHyphen

AS

SELECT CONVERT(varchar(10), GETDATE(), 105) AS CurrentDate

create view view_CurrentDateWithoutHyphen

AS

SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 105), '-', '') AS CurrentDate

Step 3:- Create the function to Generate the Sales Order Number date wise.

create function GenerateSalesOrderNumber()

returns varchar(50)

as

begin

       declare @CurrentDateWithHyphen varchar(50)

       declare @SalesOrderNumber varchar(50)

       declare @CurrentDateWithoutHyphen varchar(50)

       declare @SalesOrderNumberOutput varchar(50)

       set @CurrentDateWithHyphen = (

                                  Select CurrentDate

                                  from view_CurrentDateWithHyphen

       )

       set @SalesOrderNumber = (

                                  select max(right(SalesOrderNumber,4))

                                  from TestSalesRecord

                                  where CONVERT(varchar(10), InsertDate, 105)=@CurrentDateWithHyphen

       )

       set @CurrentDateWithoutHyphen = (

                               select CurrentDate

                                  from view_CurrentDateWithoutHyphen

       )

       if  @CurrentDateWithHyphen not in (select CONVERT(varchar(10), InsertDate, 105) from TestSalesRecord)

       begin

              set @SalesOrderNumberOutput = '' +@CurrentDateWithoutHyphen+'' +''+ right(10000+1,4)

       end

       else

       begin

              set @SalesOrderNumber = @SalesOrderNumber+1

              if @SalesOrderNumber<>''

              begin

                     set @SalesOrderNumberOutput = '' +@CurrentDateWithoutHyphen+'' +''+ right(10000+@SalesOrderNumber,4)

              end

              if @SalesOrderNumber = ''

              begin

                     set @SalesOrderNumberOutput = '' +@CurrentDateWithoutHyphen+'' +''+ right(10000+1,4)

              end

       end

              return @SalesOrderNumberOutput

end

Step 4:- Insert the value in the TestSalesRecord table

insert into TestSalesRecord(

SalesOrderNumber

)

select dbo.GenerateSalesOrderNumber()

go 5

Difference between Cursor vs. while loop in SQL Server?

Step 1:- Create the table.

Create table #TestEmpRecord

(

       Row_No int identity,

       EmpID varchar(10),

       EmpName varchar(50)

)

insert into #TestEmpRecord(

EmpID,EmpName

)

select 'E001','Employee 1' union

select 'E002','Employee 2' union

select 'E003','Employee 3' union

select 'E004','Employee 4' union

select 'E005','Employee 5' union

select 'E006','Employee 6'

Step 2:- Using Cursor Print EmpId

declare @EmpID varchar(10)       --Declaring Variable

declare curTblwstp Cursor For    --Declaring Cursor

select EmpID from #TestEmpRecord --Select Record and Storing in Cursor

open curTblwstp                  --Open Cursor

fetch next from curTblwstp into @EmpID --Store Next Record In @EmpID

While @@Fetch_Status = 0

begin

  print @EmpID

  fetch next from curTblwstp into @EmpID --Store Next Record In @EmpID                                                    Using Loop

end

Close curTblwstp                --Close Cursor

Deallocate curTblwstp           --Deallocate Cursor

Step 3:- Using while loop Print EmpId

declare @EmpID varchar(10)

declare @RowCount int

declare @I int

set @RowCount = 0

set @I = 1

set @RowCount = (select count(*) from #TestEmpRecord)

while(@I <= @RowCount)

begin

       set @EmpID = (select EmpID from #TestEmpRecord where Row_No = @I)

       print @EmpID

       set @I = @I + 1

end

Note:-

Based on the above example, both are doing the same thing and execution time also takes same. You need to decide either while loop or cursor based on your requirement.

How to Export datatable to dbf file in ASP.Net C#?

Step 1:- (Create DBF File.aspx.cs)(Complete Code)

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace Demo

{

    public partial class Create_DBF_File : System.Web.UI.Page

    {

        OleDbConnection dBaseConnection = null;

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!Page.IsPostBack)

            {

                CreateDBFFile();

            }

        }

        private DataTable GenerateData()

        {

            DataTable dt = new DataTable();

            DataRow dr = null;

            dt.Columns.Add(new DataColumn("Column1", typeof(string)));

            dt.Columns.Add(new DataColumn("Column2", typeof(string)));

            dt.Columns.Add(new DataColumn("Column3", typeof(string)));

            dt.Columns.Add(new DataColumn("Column4", typeof(DateTime)));

            int totalRow = 25;

            for (int i = 0; i < totalRow; i++)

            {

                dr = dt.NewRow();

                dr["Column1"] = i + 1;

                dr["Column2"] = "Row" + i;

                dr["Column3"] = "Row" + i;

                dr["Column4"] = DateTime.Now;

                dt.Rows.Add(dr);

            }

            return dt;

        }

        private void CreateDBFFile()

        {

            string filepath = null;

            filepath = Server.MapPath("~//Download//");

            string TableName = "T" + DateTime.Now.ToLongTimeString().Replace(":", "").Replace("AM", "").Replace("PM", "");

           

            using(dBaseConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + " Data Source=" + filepath + "; " + "Extended Properties=dBase IV"))

            {

                dBaseConnection.Open();

                OleDbCommand olecommand = dBaseConnection.CreateCommand();

                if ((System.IO.File.Exists(filepath + "" + TableName + ".dbf")))

                {

                    System.IO.File.Delete(filepath + "" + TableName + ".dbf");

                    olecommand.CommandText = "CREATE TABLE [" + TableName + "] ([Column1] int, [Column2] varchar(10), [Column3] varchar(10), [Column4] datetime)";

                    olecommand.ExecuteNonQuery();

                }

                else

                {

                    olecommand.CommandText = "CREATE TABLE [" + TableName + "] ([Column1] int, [Column2] varchar(10), [Column3] varchar(10), [Column4] datetime)";

                    olecommand.ExecuteNonQuery();

                }

                OleDbDataAdapter oleadapter = new OleDbDataAdapter(olecommand);

                OleDbCommand oleinsertCommand = dBaseConnection.CreateCommand();

                foreach (DataRow dr in GenerateData().Rows)

                {

                    string Column1 = dr["Column1"].ToString();

                    string Column2 = dr["Column2"].ToString();

                    string Column3 = dr["Column3"].ToString();

                    DateTime Column4 = Convert.ToDateTime(dr["Column4"]);

                    oleinsertCommand.CommandText = "INSERT INTO [" + TableName + "] ([Column1], [Column2],[Column3],[Column4]) VALUES ('" + Column1 + "','" + Column2 + "','" + Column3 + "','" + Column4 + "')";

                    oleinsertCommand.ExecuteNonQuery();

                }

            }

            FileStream sourceFile = new FileStream(filepath + "" + TableName + ".dbf", FileMode.Open);

            float FileSize = 0;

            FileSize = sourceFile.Length;

            byte[] getContent = new byte[Convert.ToInt32(Math.Truncate(FileSize))];

            sourceFile.Read(getContent, 0, Convert.ToInt32(sourceFile.Length));

            sourceFile.Close();

            Response.ClearContent();

            Response.ClearHeaders();

            Response.Buffer = true;

            Response.ContentType = "application/dbf";

            Response.AddHeader("Content-Length", getContent.Length.ToString());

            Response.AddHeader("Content-Disposition", "attachment; filename=Demo.dbf;");

            Response.BinaryWrite(getContent);

            Response.Flush();

            System.IO.File.Delete(filepath + "" + TableName + ".dbf");

            Response.End();

        }

    }

}

Note :-

Don't forgate to create Download folder inside your project root folder.

If you are getting the below error:-

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Means, In your local machine AccessDatabaseEngine not registered.

So, You need to download it from Microsoft web site check following link for downloading AccessDatabaseEngine

http://www.microsoft.com/download/en/details.aspx?id=13255