Syntax Error

Code samples and hints

Daten in XML-Spalte suchen

Daten in XML-Spalte suchen

   id INT,
   n xml

INSERT INTO #mal (id, n) VALUES (1, '<dd><a>Micky</a><b>Maus</b></dd>') 
INSERT INTO #mal (id, n) VALUES (1, '<dd><a>Donald</a><b>Duck</b></dd>') 

SELECT *, n.value('(/dd/a)[1]', 'varchar(max)') FROM #mal
WHERE n.value('(/dd/a)[1]', 'varchar(max)') = 'Micky'


für kompliziertere XMLs:

<Parameters xmlns:xsi="" xmlns:xsd="">
     <Value xsi:type="xsd:string">Tom</Value>
     <Value xsi:type="xsd:string">Binggeli</Value>
     <Value xsi:type="xsd:int">2</Value>

select * from t_databaseaudit

Simpler XML Export

Simpler XML Export


Daten für XML hierarchisch ausgeben

As described in the topic, Constructing XML Using FOR XML, RAW and AUTO mode do not provide much control over the shape of the XML generated from a query result. However, EXPLICIT mode provides the most flexibility in generating the XML you want from a query result.

The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. Depending on the XML you request, writing EXPLICIT mode queries can be cumbersome. You may find that Using PATH Mode with nesting is a simpler alternative to writing EXPLICIT mode queries.

Because you describe the XML you want as part of the query in EXPLICIT mode, you must ensure that the generated XML is well formed and valid.

Erster nicht-NULL Wert aus einer Liste

Erster nicht-NULL Wert aus einer Liste.

         WHEN COALESCE(PRS_Name, PRS_Zusatzname, PRS_Vorname) IS NULL THEN '' 
         ELSE ', ' + ISNULL(PRS_Name + ' ','') + ISNULL(PRS_Zusatzname + ' ','') + ISNULL(PRS_Vorname,'') 

How to Share Data Between Stored Procedures

This article tackles two related questions:

  • How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored procedure in a SELECT statement?
  • How can I pass a table as a parameter from one stored procedure to another?

In this article I will discuss a number of methods, and also point out their advantages and drawbacks. Some of the methods apply only when you want to reuse a result set, whereas others apply in both situations. In the case you want to reuse a result set, most methods require you to rewrite the stored procedure in one way or another, but there are some methods that do not.

Custom Properties on Control

Custom Properties on a Control with Serialization and Reset:

public class MyLabel : Label
        public MyLabel()


        private static readonly string txtExtendedDefaultValue = "Extended Text Property";
        private string txtExtended = txtExtendedDefaultValue;
        public string TxtExtended
            get { return txtExtended; }
            set { txtExtended = value; }

        void ResetTxtExtended()
            txtExtended = txtExtendedDefaultValue;

        bool ShouldSerializeTxtExtended()
            return txtExtended != txtExtendedDefaultValue;

        private string txtSimple = "Simple Text Property";
        [DefaultValue("Simple Text Property")]
        public string TxtSimple
            get { return txtSimple; }
            set { txtSimple = value; }

Daten vor dem Update in die Datenquelle aufbereiten

Daten vor dem Update in die Datenquelle aufbereiten.

protected void fwUser_ItemUpdating(object sender, FormViewUpdateEventArgs e)
   if (e.OldValues["loginPwd"].Equals(e.NewValues["loginPwd"]))
     e.NewValues["loginPwd"] = null;
     e.NewValues["loginPwd"] = EncryptPwd(e.NewValues["loginName"].ToString(), e.NewValues["loginPwd"].ToString()); 

Checkboxen und DataId aus Grid auslesen

Fills a range of Checkbox-Values to the temporary datastorage.

/// <summary>
/// Fills a range of Checkbox-Values to the temporary datastorage.
/// </summary>
/// <param name="rows">The GridViewRows containing the values</param>
/// <param name="idLabelName">the Control-ID of the label containing the id (must be an int!)</param>
/// <param name="checkBoxName">the Control-ID of the checkbox containing the boolean value</param>
public void AddRange(GridViewRowCollection rows, string idLabelName, string checkBoxName)
   for (int i = 0; i < rows.Count; i++)
       bool value = ((CheckBox)rows[i].FindControl(checkBoxName)).Checked;
       int id = -1;
       if (int.TryParse(((Label)rows[i].FindControl(idLabelName)).Text, out id))
         Add(id, value);


Öffnet ein Bestätigungs-Dialog im Browser.

Button btn = new Button();
btn.OnClientClick = "return confirm('Wirklich ? ')";  

Info-Popup anzeigen

Öffnet ein Info-Popup im Browser

String csname1 = "PopupScript";
Type cstype = this.GetType(); 
ClientScriptManager cs = Page.ClientScript; 
if (!cs.IsStartupScriptRegistered(cstype, csname1))  {
      String cstext1 = "alert('Standardbenachrichtigungen wurden erfolgreich erstellt');";
      cs.RegisterStartupScript(cstype, csname1, cstext1, true);  }


Maximal zulässige Ladezeit einer Webseite über die web.config festlegen

   <httpRuntime executionTimeout="2000" />


Sucht ein Objekt in einem Array

return Array.Exists(intValueArray, delegate(int singleArrayValue) { return (intVergleichValue == singleArrayValue); });


DataRow[] temp = myDataSet.MyTable.Select("ID = 1");
MyDataSet.MyTableRow[] mtRows = 
    Array.ConvertAll<DataRow, MyDataSet.MyTableRow>(temp, delegate(DataRow from) { return (MyDataSet.MyTableRow)from; });

Retrieving Identity or Autonumber Values into Datasets

You can set a column in a DataTable to be an auto-incrementing primary key in order to ensure a unique value for each row in the table. However, you may have multiple clients for your application, and each of those clients can be working with a separate instance of the DataTable. In this case, you might end up with duplicate values between the separate instances of the DataTable. Because all your clients are working with a single data source, you can resolve this conflict by letting the data source define the auto-incremented value. To accomplish this you use Identity fields in Microsoft SQL Server

Using the data source to populate an Identity or Autonumber column for a new row added to a DataSet creates a unique situation because the DataSet has no direct connection to the data source. As a result, the DataSet is unaware of any values generated automatically by the data source. However, with a data source that can create stored procedures with output parameters, such as Microsoft SQL Server, you can specify the automatically generated values, such as a new identity value, as an output parameter and use the DataAdapter to map that value back to the column in the DataSet.

Special Webservice Call

Code um einen Webservice manuell anzusprechen.

using System;
  using System.Net;
  using System.Web.Services.Protocols;

namespace ConsoleApplication1
    internal class Program
        private static void Main(string[] args)
            DlgExport_BezugInterfaceHttpService dlg = new DlgExport_BezugInterfaceHttpService();

            WebProxy webProxy = new WebProxy();
            dlg.Proxy = webProxy;

            string[] egids = new string[1];
            egids[0] = "15647";

            getGebaeudeAllByEgid b = new getGebaeudeAllByEgid();
            b.Egids = egids;

                getGebaeudeAllByEgidResponse response = dlg.getGebaeudeAllByEgid(b);
            catch (SoapException ex)
                throw  ex;

Excel-Export ohne Excel (auch für Web)

Microsoft Excel ist ein beliebtes Werkzeug, um mit Tabellen zu arbeiten. Deshalb wird es bei den Endanwendern hoch geschätzt, wenn eine Anwendung Daten im Excel-Format exportieren kann. Da viele Geschäftsanwendungen heute allerdings Webanwendungen sind, gibt es mit Excel ein Problem. Auf dem Webserver ist Excel nicht installiert. Selbst wenn, würde ein Excel-Export mit COM Fernsteuerung des Excel-Objektmodells niemals schnell und zuverlässig funktionieren. Was nun?

Seit Excel 2003 gibt es einen sehr einfachen Weg, Excel-Dokumente ganz ohne Excel zu erzeugen, und zwar mit XML. Das kann eine PHP- oder ASP.NET-Anwendung ohne Probleme. XML ist ja nur Text und kann theoretisch sogar mit einfacher Stringverkettung erzeugt werden.

Performance Counter

Performance Counter um z.B. den benötigten Arbeitsspeicher auszulesen.

PerformanceCounter ramCounter; 
ramCounter = new PerformanceCounter("Prozess", "Virtuelle Bytes (max.)"); 
ramCounter.InstanceName = Process.GetCurrentProcess().ProcessName; 
Debug.WriteLine(string.Format("max. Speicherbedarf: {0}", (ramCounter.NextValue() / 1024.0)));