0 Comments

Lazy productive I as a developer am, I did not want to write a stored procedure. So I ended up with a lot of SQL statements which needed to be combined and executed only if the previous one succeeds.

However there was a timeout during the execution. It was strange because when I copy pasted the SQL statement in SMSS it worked perfectly and fast too. My mistake was to use a new SqlCommand object which was not part of the transaction. I needed to re-use the same sqlcommand. Here is some working code:

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConString"].ToString()))
{
    con.Open();

    SqlCommand cmd = con.CreateCommand();
    SqlTransaction transaction = con.BeginTransaction();

    cmd.Connection = con;
    cmd.Transaction = transaction;

    try{
    
        cmd.CommandText = "SELECT productcode FROM products WHERE productid = " + productid;
        string code = cmd.ExecuteScalar().ToString();
    
        cmd.CommandText = "SELECT ProductID, ProductWeight, ProductEuroPrice, OrderID FROM ORDERPICKINGPRODUCTS WHERE orderid = " + orderid + " AND productid = " + productid;
        SqlDataReader sdr = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(sdr);
    
        cmd.CommandText = "DELETE FROM ORDERPICKINGPRODUCTS WHERE orderid = " + orderid + " AND productid = " + productid;
        cmd.ExecuteNonQuery();
        transaction.Commit();
    }
    catch(Exception e)
    {
        transaction.Rollback();
    }
}
I know that it is better to use parameters https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters%28v=vs.110%29.aspx

And the AddWithValue method… But this is just pseudo code and not in production. So don’t worry!

This is just a small sample to help people with a strange timeout which you can encounter if you use an sqlcommand outside the transaction.

 

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

Microsoft SQL Server has the ability to format the query output to xml, which is great. I used version “Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)” to output XML to have a nested data structure, but there is unfortunately no first citizen support for JSON which is big on the web these days. Perhaps it is left out on purpose to make sure that people are unable to direct output their data as JSON and are forced to build a layer in between so that it can also authenticate and add security to a request. Web API is great for this. But back to the subject: I wanted to output my nested data as JSON really quickly with the minimum amount of code.

In a nutshell, I got the data as xml and converted that to JSON and send it to the output of a generic handler (*.ashx)

Here is my source code for my easy SQL2JSON solution:

private XDocument GetProducts(string q)
{
    SqlCommand comProd = new SqlCommand(@"SELECT ProductCode, ProductEan13, CategoryName FROM PRODUCTS 
        inner join CATEGORIES on products.CategoryID = CATEGORIES.CategoryID
        WHERE ((FactoryCode LIKE @name) OR (ProductCode LIKE @name)) AND (ProductDeleted = 0) for xml raw ('Product'), ROOT ('Products');");
    comProd.Parameters.AddWithValue("name", q + "%");
    string xml = SsDb.GetScalar(comProd).ToString();
    return XDocument.Parse(xml);
}

This gave me an `XDocument` object which I still have to convert to JSON

Since I was using an generic handler I retrieved the search parameter from the query string and used it as input parameter for the `GetProducts` method.

public void ProcessRequest(HttpContext context)
{
    context.Response.ContentType = "application/json";
    context.Response.Cache.SetCacheability(HttpCacheability.Public);
    context.Response.Cache.SetExpires(DateTime.Now.AddDays(1));
    context.Response.Cache.SetMaxAge(new TimeSpan(24, 0, 0));

    string q = context.Request.QueryString.Get("q");
    context.Response.Write(JsonConvert.SerializeXNode(GetProducts(q)));
}

So the most important line is the last one:

context.Response.Write(JsonConvert.SerializeXNode(GetProducts(q)));

Do not forget to use the one-nuget-package-that-rules-them-all (Install-Package Newtonsoft.Json )

using Newtonsoft.Json;

 

Now you can access your JSON data source with the C# generic handler from your JavaScript

yourHandler.ashx?q=yourproductcode

I hope this has been of any help. good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

I have a table filled with numbers. It is a table with the name numbers and there is only one field named ‘num’ which is an integer (so max value is 2.147.483.647 as seen on MSDN) and the integer is  an identity and unique.

2014-03-05 12_10_47-VM530.ssinternational - dbo.numbers - Microsoft SQL Server Management Studio

The table just contains numbers from 1 until 10.000. I use it to inner join, for instance:

You can have a table with a row: productA is 10 times in stock. You can then inner join the numbers table if you need 10 rows of productA.

select * from products 
inner join numbers on num <= stock

But I only had 10.000 rows in it and encountered a bug in my system because 10.000 was not enough. So I had to increase it. I have found several solutions to create a numbers table. But no snippet to update an existing number table.

So here is my snippet to add additional numbers to your number table:

begin transaction
SET IDENTITY_INSERT [numbers] ON

DECLARE @i INT;
SELECT @i = 10001;
SET NOCOUNT ON
WHILE @i <= 1000000
BEGIN
    INSERT INTO [numbers] (num) VALUES (@i);
    select @i= @i + 1;
end;

SET IDENTITY_INSERT [numbers] OFF
commit
Good luck!
Pin on pinterest Plus on Googleplus Post on LinkedIn
9 Comments
  •   Posted in: 
  • SQL

SQL-Server-Management-Studio-2008

If you want to inner join two tables from different databases, you might encounter some collation errors.


When I executed this query:



SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode = [items].itemCode
where ProductDeleted = 0 

I got this message:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

So when I executed the following query, I found out that I had two different Collations:

SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;
SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;

I thought that it was because one database was migrated from SQL Server 2000 to 2005 to 2008 and the other one (database2) was from 2005 to 2008. But apparently the default collation depends on the region settings of your Windows installation and gets set when you install SQL Server.

Now you can change the collation of your database with various solutions which can be found around the internet, but I wanted to simply join two tables and do one update.

I had to Google a lot to find out, how to define collations when using an inner join. But here is the solution for my query:

SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode collate SQL_Latin1_General_CP1_CI_AS = [items].itemCode
where ProductDeleted = 0 

So you can define collation on the fields which you join. Open-mouthed smile 

By the way: this is a great resource for the different collations: http://msdn.microsoft.com/en-us/library/ms143515%28v=SQL.90%29.aspx

Hope that it helped you, it would have saved me a lot of frustrations and time.

kick it on DotNetKicks.com Shout it

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments
  •   Posted in: 
  • SQL

Our company acquired a brand new database server with 12 gig’s of RAM. It’s really cool, but unfortunately I have to do the migration…

I started with upgrading our test server which is in our LAN. It was running SQL 2005 standard and I wanted to upgrade it to SQL 2008 standard.SQL Server Installation Center

You should expect the worse case scenario when doing stuff like this, but apparently Microsoft did an amazing thing with the installation wizard. If you insert the SQL 2008 DVD it starts to copy the required files for the installation. After that you normally select ‘install new instance’ but there was this ‘Upgrade from 2000 or 2005’ option!

 

[more]It’s under ‘installation’ and then the forth option: ‘upgrade from SQL Server 2000 or SQL Server 2005

The next task was to migrate the SQL 2005 production database to our brand new server which has SQL 2008 installed. All I did was fire up the ‘Microsoft SQL Server Management Studio’ and chose ‘Task –> Backup’ when I right clicked the database. Then i just wrote the whole DB to a *.bak file and rarred it with WinRar to reduce the file size by 80%! Then i moved it to the new server which is accessible by LAN and restored it there.

All there was left for me to do was re-creating users and giving them the correct roles. Now the next task for me is to migrate the Jobs that I had scheduled in the SQL Server Agent…

Pin on pinterest Plus on Googleplus Post on LinkedIn