[TSQL/C#] Query's combineren

[ThunderNet]

Ik ben bezig met een webapplicatie. Een van de functionaliteiten is dat de gebruiker een "project" (verzameling van instellingen die hij aangepast heeft) kan opslaan in een database. Hiervoor heb ik een functie gemaakt, genaamd SaveProject(string data) ja ja, erg orgineel allemaal.

data is hier een JSON string, waarin de gebruikersnaam en de directory waarin de gebruiker zijn foto's heeft geupload staan opgeslagen.

De procedure heeft 3 grote stappen in de database,
Controleren of een record al bestaat (oftewel, is het project al eens eerder opgeslagen, of is dit de eerste keer). Als record nog niet bestaat, moet deze worden aangemaakt, dit is de 2e stap.
De 3e stap is de data in het inmiddels bestaande record updaten.

C#:

    [WebMethod]
    public bool SaveProject(string data)
    {
        //data is een json object hier moet eerst wat data uitgehaald worden (user, titel, guid)

        Debug.Print(data);      

        TextReader myJsonTextReader = new StringReader(data);
        JsonReader myJsonReader = new JsonReader(myJsonTextReader);
        string strGuid = "";
        string strUser = "";
        string strUserId = "";
        string strTitle = "";

        #region Json uitlezen
        while (myJsonReader.Read())
        {
            Debug.WriteLine(myJsonReader.Value);
            if ((string)myJsonReader.Value == "username")
            {
                myJsonReader.Read();
                strUser = (string)myJsonReader.Value;
            }

            if ((string)myJsonReader.Value == "title")
            {
                myJsonReader.Read();
                strTitle = (string)myJsonReader.Value;
            }

            if ((string)myJsonReader.Value == "guid")
            {
                myJsonReader.Read();
                strGuid = (string)myJsonReader.Value;
            }
        }
        #endregion

        #region UserId opzoeken adhv usernaam
        MembershipUser myUser = Membership.GetUser(strUser);
        strUserId = myUser.ProviderUserKey.ToString();
        #endregion

        #region Controleren of record al bestaat
        string connstr = ConfigurationManager.AppSettings["MyDB"];
        SqlConnection myCon = new SqlConnection(connstr);
        SqlCommand myCom = new SqlCommand();
        myCom.Connection = myCon;
        myCom.CommandText = "SELECT id FROM Projects WHERE(guid = @guid)";
        myCom.Parameters.Add("@guid", SqlDbType.UniqueIdentifier).Value = strGuid;

        myCon.Open();
        myCom.Prepare();
        string myResult = myCom.ExecuteScalar().ToString();
        myCon.Close();
        #endregion

        #region Record aanmaken indien nodig
        if (myResult == null)
        {
            //Moet nieuw record aangemaakt worden
            SqlCommand myCom2 = new SqlCommand();
            myCom2.Connection = myCon;
            myCom2.CommandText = "INSERT INTO Projects (user, title, guid) VALUES (@user, @title, @guid)";
            myCom2.Parameters.Add("@guid", SqlDbType.UniqueIdentifier).Value = strGuid;
            myCom2.Parameters.Add("@title", SqlDbType.NVarChar).Value = strTitle;
            myCom2.Parameters.Add("@user", SqlDbType.UniqueIdentifier).Value = strUserId;
            myCon.Open();
            myCom2.Prepare();
            myCom2.ExecuteNonQuery();
            myCon.Close();
        }
        #endregion

        #region Record updaten
        SqlCommand myCom3 = new SqlCommand();
        myCom3.Connection = myCon;
        myCom3.CommandText = "UPDATE Projects SET title = @title, data = @data WHERE guid = @guid";
        myCom3.Parameters.Add("@guid", SqlDbType.UniqueIdentifier).Value = strGuid;
        myCom3.Parameters.Add("@title", SqlDbType.NVarChar).Value = strTitle;
        myCom3.Parameters.Add("@user", SqlDbType.UniqueIdentifier).Value = strUserId;
        myCom3.Parameters.Add("@data", SqlDbType.NVarChar).Value = data;
        myCon.Open();
        myCom3.Prepare();
        myCom3.ExecuteNonQuery();
        myCon.Close();
        #endregion

        return true;
    }

Hierin komen dus de volgende query's voor:

SQL:

SELECT id FROM Projects WHERE(guid = @guid)

INTO Projects (user, title, guid) VALUES (@user, @title, @guid)

UPDATE Projects SET title = @title, data = @data WHERE guid = @guid

Nu heb ik slechts een basiskennis van SQL, maar ik vraag mij af, hoe ik deze 3 queries in 1 kan combineren, zodat deze functie een stuk korter en dus overzichterlijker kan

[ Voor 15% gewijzigd door ThunderNet op 19-12-2007 10:10 ]

[whoami]

Zowiezo zou ik die ene functie in 3 functies splitten.

Daarnaast: kan je geen class maken van je 'project' ipv dat als string te gebruiken, en binnen het project hou je bij of het om een nieuw project gaat of indien het om een 'oud' project gaat.

[ThunderNet]

Ik heb in een string omdat het dus een json object was. En behalve deze functie die het geheel in de database moet opslaan doe ik serverside er verder niks meer mee. Dus is het dan niet beetje overbodig om er een class van te maken? Het bovenstaande is een webservice functie, had ik misschien beter direct kunnen melden
Ik zal even de volledige functie plaatsen, is waarschijnlijk wat duidelijker.

[ Voor 10% gewijzigd door ThunderNet op 19-12-2007 10:08 ]

[cspare]

Misschien moet je eens kijken naar stored procedures. Lijkt me een handige oplossing voor jouw probleem.

[whoami]

ThunderNet schreef op woensdag 19 december 2007 @ 10:08:
Ik heb in een string omdat het dus een json object was. En behalve deze functie die het geheel in de database moet opslaan doe ik serverside er verder niks meer mee. Dus is het dan niet beetje overbodig om er een class van te maken? Het bovenstaande is een webservice functie, had ik misschien beter direct kunnen melden
Ik zal even de volledige functie plaatsen, is waarschijnlijk wat duidelijker.

Wel, behou je webservice functie, maar splits die functie op in 3 kleinere functies, en roep die 3 kleinere functies aan in je public exposed webservice functie.

[ThunderNet]

goed ik heb de functie versimpeld, en gebruik nu nog maar 2 sql queries. (INSERT en UPDATE)... Door het resultaat van Update (het aantal rows dat is aangepast) te gebruiken om te bepalen of er een INSERT moet plaatsvinden.
Echter krijg ik steeds een foutmelding:

C#:

        SqlConnection myCon = new SqlConnection(connstr);
        SqlCommand myCom = new SqlCommand();
        myCom.Connection = myCon;
        myCom.CommandText = "UPDATE Projects SET title = @title, data = @data WHERE user = @user AND guid = @guid;";
        myCom.Parameters.Add("@guid", SqlDbType.UniqueIdentifier,16);
        myCom.Parameters["@guid"].Value = new Guid(strGuid);
        myCom.Parameters.Add("@title", SqlDbType.NVarChar);
        myCom.Parameters["@title"].Value = strTitle;
        myCom.Parameters.Add("@user", SqlDbType.UniqueIdentifier,16);
        myCom.Parameters["@user"].Value = new Guid(strUserId);
        myCom.Parameters.Add("@data", SqlDbType.NVarChar);
        myCom.Parameters["@data"].Value = data;
        myCon.Open();
        int rowReturn = myCom.ExecuteNonQuery();

Dit loopt op de Execute vast met de volgende melding:

The server method 'SaveProject' failed with the following error: System.Data.SqlClient.SqlException-- Conversion failed when converting from a character string to uniqueidentifier.

Wat ik ook probeer ik blijf deze foutmelding krijgen.

[whoami]

Ik vind jouw manier van versimpelen maar gevaarlijk... Wat als je met optimistic concurrency aan de slag gaat (misschien doe je dat nu al, en gebruik je die guid als 'timestamp' ?), en je row kan niet geupdated worden omdat iemand anders ze eerder ge-updated heeft ? Dan ga je een nieuwe row inserten, terwijl dat eigenlijk niet hoeft ?

Ik zou die method versimpelen, door ze in 3 te splitsen. Een method die checkt of een project bestaat, en dan een update en add method.

Ivm je foutmelding: probeer eens je GUID's als strings toe te kennen in je query.

code:

myCom.Parameters.Add ("@guid", SqlDbType.Varchar, 36).Value = strUserId;

[ThunderNet]

whoami schreef op maandag 24 december 2007 @ 12:55:
Ik vind jouw manier van versimpelen maar gevaarlijk... Wat als je met optimistic concurrency aan de slag gaat (misschien doe je dat nu al, en gebruik je die guid als 'timestamp' ?), en je row kan niet geupdated worden omdat iemand anders ze eerder ge-updated heeft ? Dan ga je een nieuwe row inserten, terwijl dat eigenlijk niet hoeft ?

Die guid is de verwijzing naar een directory die aangemaakt is. In deze directory staan door de bezoeker geuploade bestanden. Een row zou dus alleen aangepast kunnen worden door de gebruiker die hem aangemaakt heeft. Vandaar de userId, wat de Id is uit de membership-table.

Ik zou die method versimpelen, door ze in 3 te splitsen. Een method die checkt of een project bestaat, en dan een update en add method.

Kan ook ik dacht alleen handig bezig te zijn, doordat een UPDATE een number of rows affected terug geeft.

Ivm je foutmelding: probeer eens je GUID's als strings toe te kennen in je query.

code:

myCom.Parameters.Add ("@guid", SqlDbType.Varchar, 36).Value = strUserId;

[ThunderNet]

Goed... m'n query's waren dus foutief... Heb alles ook nu naar 3 functies geplaatst. Echter werkt het nog niet helemaal naar behoren.

C#:

    [WebMethod]
    public bool SaveProject(string data)
    {
        //data is een json object hier moet eerst wat data uitgehaald worden (user, titel, guid)

        Debug.Print(data);      

        TextReader myJsonTextReader = new StringReader(data);
        JsonReader myJsonReader = new JsonReader(myJsonTextReader);
        string strGuid = "";
        Guid gdGuid = new Guid();
        string strUser = "";
        string strUserId = "";
        Guid gdUserId = new Guid();
        string strTitle = "";
        string connstr = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;

        #region Json uitlezen
        while (myJsonReader.Read())
        {
            Debug.WriteLine(myJsonReader.Value);
            if ((string)myJsonReader.Value == "username")
            {
                myJsonReader.Read();
                strUser = (string)myJsonReader.Value;
            }

            if ((string)myJsonReader.Value == "title")
            {
                myJsonReader.Read();
                strTitle = (string)myJsonReader.Value;
                if (strTitle == null) { strTitle = "titel"; }
            }

            if ((string)myJsonReader.Value == "guid")
            {
                myJsonReader.Read();
                strGuid = (string)myJsonReader.Value;
                gdGuid = new Guid(strGuid);
            }
        }
        #endregion

        #region UserId opzoeken adhv usernaam
        MembershipUser myUser = Membership.GetUser(strUser);
        strUserId = myUser.ProviderUserKey.ToString();
        gdUserId = new Guid(strUserId);
        #endregion


        if (ProjectExists(gdUserId, gdGuid) == true)
        {
            return ProjectUpdate(gdUserId, gdGuid, strTitle, data);
        }
        else
        {
            return ProjectInsert(gdUserId, gdGuid, strTitle, data);
        }
    }

    private bool ProjectExists(Guid userid, Guid projectguid)
    {
        //Controleert of record in de database bestaat
        SqlConnection myCon = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
        SqlCommand myCom = new SqlCommand("SELECT Id FROM Projects WHERE([user] = @user) AND (guid = @guid)", myCon);
        myCom.Parameters.AddWithValue("@user", userid);
        myCom.Parameters.AddWithValue("@guid", projectguid);
        myCon.Open();
        int result = myCom.ExecuteReader().FieldCount;
        myCon.Close();
        
        Debug.WriteLine(result);

        if (result != 0)
        {
            Debug.WriteLine("Record bestaat al");
            return true;
        }
        else
        {
            Debug.WriteLine("Record nog niet");
            return false;
        }
    }

    private bool ProjectUpdate(Guid userid, Guid projectguid, string projecttitle, string data)
    {
        //Voert update query uit.
        SqlConnection myCon = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
        SqlCommand myCom = new SqlCommand("UPDATE Projects SET title = @title, data = @data WHERE ([user] = @user) AND (guid = @guid)", myCon);
        myCom.Parameters.AddWithValue("@user", userid);
        myCom.Parameters.AddWithValue("@guid", projectguid);
        myCom.Parameters.AddWithValue("@title", projecttitle);
        myCom.Parameters.AddWithValue("@data", data);
        myCon.Open();
        int result = myCom.ExecuteNonQuery();
        myCon.Close();

        Debug.WriteLine(result);
        if (result != 0)
        {
            Debug.WriteLine("Record Geupdate");
            return true;
        }
        else
        {
            Debug.WriteLine("Record niet geupdate");
            return false;
        }
    }

    private bool ProjectInsert(Guid userid, Guid projectguid, string projecttitle, string data)
    {
        //Voert insert query uit.
        SqlConnection myCon = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
        SqlCommand myCom = new SqlCommand("INSERT INTO Projects ([user], guid, title, data) VALUES (@user,@guid,@title,@data)", myCon);
        myCom.Parameters.AddWithValue("@user", userid);
        myCom.Parameters.AddWithValue("@guid", projectguid);
        myCom.Parameters.AddWithValue("@title", projecttitle);
        myCom.Parameters.AddWithValue("@data", data);
        myCon.Open();
        int result = myCom.ExecuteNonQuery();
        myCon.Close();

        Debug.WriteLine(result);
        if (result != 0)
        {
            Debug.WriteLine("Record toegevoegd");
            return true;
        }
        else
        {
            Debug.WriteLine("Record niet toegevoegd");
            return false;
        }
    }

Als hij nu een update moet doen, doet hij alsnog een insert. Dus ik heb het idee dat mijn select het niet goed doet. Is er een betere manier op te zien of mijn SELECT statement een resultaat teruggeeft?

[cspare]

Geeft FieldCount niet het aantal kolommen terug? En is dat dan niet ongeacht het aantal rijen hetzelfde?

[ThunderNet]

cspare schreef op donderdag 27 december 2007 @ 11:48:
Geeft FieldCount niet het aantal kolommen terug? En is dat dan niet ongeacht het aantal rijen hetzelfde?

Damnit dat zou het gedrag nu heel erg goed kunnen verklaren. Dan moet ik hier dus een andere constructie voor zien te vinden.

[ThunderNet]

Goed, heb het ietsjes aangepast, met een COUNT in de query. Het werkt dus inmiddels.. Bedankt voor de input allen

C#:

    private bool ProjectExists(Guid userid, Guid projectguid)
    {
        //Controleert of record in de database bestaat
        SqlConnection myCon = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
        SqlCommand myCom = new SqlCommand("SELECT COUNT (Id) FROM Projects WHERE([user] = @user) AND (guid = @guid)", myCon);
        myCom.Parameters.AddWithValue("@user", userid);
        myCom.Parameters.AddWithValue("@guid", projectguid);
        myCon.Open();
        int result = Convert.ToInt16(myCom.ExecuteScalar().ToString());
        myCon.Close();
        
        Debug.WriteLine(result);

        if (result != 0)
        {
            Debug.WriteLine("Record bestaat al");
            return true;
        }
        else
        {
            Debug.WriteLine("Record nog niet");
            return false;
        }
    }

[whoami]

Probleem is nu wel dat je voor iedere method aanroep een andere connectie gaat gebruiken / openen / sluiten. Niet alleen is dat niet echt efficient, maar zo heb je ook niet de mogelijkheid om je hele procedure waarbij je gaat checken of een nieuw record geinserted moet worden, of geupdated worden en het inserten/updaten zelf binnen één transactie uit te voeren ...

Volgens mij ga je beter je transactie of connectie doorgeven als parameter aan die functies, zodanig dat je je 'transactie-control' zelf buiten die functies kan regelen.

code:

SqlConnection conn = new SqlConnection ( ... );
conn.Open();
SqlTransaction thx = conn.BeginTransaction();
try
{
   
   if( ProjectExists (thx, userId, projectGuid) )
   {
      // bla
   }
   else
   {
      // bliep
   }


   thx.Commit();
}
catch
{
   thx.Rollback();
}
finally
{
   conn.Close();
}