In this article, we are going to see , paging wise insert record using asp.net web api
IF OBJECT_ID('TEMPDB..##EMP') IS NOT NULL DROP TABLE ##EMP
GO
CREATE TABLE ##EMP
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[NAME] [VARCHAR](50) NULL,
[AGE] [INT] NULL,
[SALARY] [BIGINT] NULL
)
GO
DECLARE @Counter INT = 1
WHILE (@Counter <= 10000)
BEGIN
SET NOCOUNT ON;
INSERT INTO ##EMP VALUES (CONCAT('DEV_',@Counter),20 + CONVERT(INT,(80-20+1)*RAND()), 35000 + @Counter)
SET @Counter = @Counter + 1;
END
SELECT * FROM ##EMP
GO
/*
EXEC SPB_GETEMP 10,1
EXEC SPB_GETEMP 1,2
*/
IF OBJECT_ID('SPB_GETEMP') IS NOT NULL DROP PROC SPB_GETEMP
GO
CREATE PROC SPB_GETEMP
@PageSize INT
,@CurrentPage INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX),@PAGING NVARCHAR(MAX)
SELECT @SQL = 'SELECT ID,NAME,AGE,SALARY FROM ##EMP ORDER BY ID';
SELECT @PAGING = ' OFFSET ' + CONVERT(NVARCHAR(10), (@PageSize)*(@CurrentPage-1)) + ' ROWS FETCH NEXT ' + CONVERT(NVARCHAR(10),@PageSize) + ' ROWS ONLY '
IF @CurrentPage > 0
BEGIN
EXECUTE(@SQL + @PAGING)
PRINT @SQL + @PAGING
END
ELSE
BEGIN
EXECUTE(@SQL)
PRINT @SQL
END
END
#region database connectivity & retriving data
private SqlConnection connection;
protected string ConnectionString
{
get
{
//return ConfigurationManager.ConnectionStrings["DBString"].ConnectionString;
return @"Data Source=192.168.0.100;Initial Catalog=TESTDB;User ID=sa;Password=test@12";
}
}
protected SqlConnection GetConnection()
{
try
{
connection = new SqlConnection(ConnectionString);
connection.Open();
}
catch (Exception)
{
throw;
}
return connection;
}
protected void CloseConnection()
{
try
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
catch (Exception)
{
throw;
}
}
public enum FillBy
{
DataTable,
DataSet
}
private dynamic FillAdapter(SqlDataAdapter adapter, FillBy fillBy, string str_table = "")
{
if (fillBy.Equals(FillBy.DataTable))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
else
{
DataSet ds = new DataSet();
if (str_table.Length > 0)
adapter.Fill(ds, str_table);
else
adapter.Fill(ds);
return ds;
}
}
protected dynamic ExecuteQuery(SqlCommand command, FillBy optionalParam = FillBy.DataTable)
{
try
{
DataSet ds = new DataSet();
command.Connection = GetConnection();
command.CommandTimeout = 0;
SqlDataAdapter adapter = new SqlDataAdapter(command);
return FillAdapter(adapter, optionalParam);
}
catch (Exception)
{
throw;
}
finally
{
CloseConnection();
}
}
//employee service
public DataTable EmployeeService(int PageSize, int CurrentPage)
{
try
{
SqlCommand command = new SqlCommand();
command.CommandText = "SPB_GETEMP";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@PageSize", PageSize);
command.Parameters.AddWithValue("@CurrentPage", CurrentPage);
return ExecuteQuery(command);
}
catch
{
throw;
}
}
#endregion
#region manage response & send data
[HttpGet]
public HttpResponseMessage GetAllEmployee(int PageSize, int CurrentPage)
{
try
{
DataTable dtEmployee = EmployeeService(PageSize, CurrentPage);
return Request.CreateResponse(HttpStatusCode.OK, dtEmployee);
}
catch (Exception ex)
{
return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
}
}
[HttpGet]
public HttpResponseMessage SendAllEmployee(int PageSize)
{
try
{
int currentPage = 0;
bool isData = true;
HttpClient client = new HttpClient();
client.BaseAddress = new Uri("http://192.168.1.100:1008/api/DEMOAPI/");
while (isData)
{
currentPage++;
HttpResponseMessage response = client.GetAsync("GetAllEmployee?PageSize=" + PageSize + "&CurrentPage=" + currentPage).Result;
if (response.IsSuccessStatusCode)
{
DataTable empDt = (DataTable)JsonConvert.DeserializeObject(response.Content.ReadAsStringAsync().Result, typeof(DataTable));
if (empDt != null && empDt.Rows.Count > 0)
{
isData = true;
// SaveEmployee(empDt) //you can create save method insert data in another database
}
else
{
isData = false;
}
}
}
return Request.CreateResponse(HttpStatusCode.OK);
}
catch (Exception ex)
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex.Message);
}
}
#endregion
for remove xml response add below line of code
Global.asax.cs :
GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);
0 Comments
if you have any doubts , please let me know