Recently I discovered a new function to define your own code templates. This functionality is very often used by programmers, but can work wonders. In writing a business application always comes a time, that you need to write code to write data to the database, as well as their reading.
In this case, 90% code is the same. First, read the column number, which stores data in such. SQLDataReader. And then we build on the basis of data objects. When reading data from the database should be programmed as the situation where there is a null. And this usually works if the column is read is null. If this is substituted with a default. If there is no nulla value is read from the database.
I come here with the help of snippets. Miraculous code templates, which allow to minimize the amount of time the need to implement database access. Let’s start with the first part of the problem. The code in this situation often looks so:
SQLDataReader rdr = (...)
int id_ndx = rdr.GetOrdinal("id");
int name_ndx = rdr.GetOrdinal("name");
int surname_ndx = rdr.GetOrdinal("surname");
(...)
Then followed the most read data:
int id = rdr.IsDBNull(id_ndx) ? 0 : $reader$.GetInt32(id_ndx); string name = rdr.IsDBNull(name_ndx) ? string.Empty : rdr.GetString(name_ndx); string surname = rdr.IsDBNull(surname_ndx) ? string.Empty : rdr.GetString(surname_ndx);
As shown in the example above, a significant part of the code is repeated. So I will start from the first case. Assuming, we want, to variables called in the same way as the columns in the table in the database, only with the tip “_ndx” can propose the following solution:
<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>GetOrdinalFromSQL</Title>
<Shortcut>MGetOrdinal</Shortcut>
<Description>Odczyt indexu kolumny z bazy danych</Description>
<Author>Michal Jankowski</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>column</ID>
<Default>columnname</Default>
<ToolTip>Column name</ToolTip>
</Literal>
</Declarations>
<Code Language="csharp"><![CDATA[int $column$_ndx = rdr.GetOrdinal("$column$");
$end$]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets[/code]
<p style="text-align: justify;">And now, step by step what to do. First, you must create an XML file with the extension <em>snippet</em>. Then add the things that defines a standard XML file. Interesting things begin only from the element <em>Header</em>. In it are defined as basic information on snippetu:</p>
<ul>
<li><em>Title</em> - title,</li>
<li><em>Shortcut</em> - string, which will have to be introduced in Visual Studio to use the snippet,</li>
<li><em>Description</em> - opis snippetu,</li>
<li><em>Autor</em> - information about the author,</li>
<li><em>SnippetTypes</em>- here defined as behavior:
<ul>
<li><em>SurroundsWith</em> - snippet can be used to code around the selected parts,</li>
<li><em>Expansion</em> - snippet can be used in place of the cursor,</li>
<li><em>Refactoring</em> - snippet is used during refactoring. Snippet of this type can not be defined by the user.</li>
</ul>
</li>
</ul>
<p style="text-align: justify;">The second part of the XML file - element <em>Snippet</em> - define how you want your snippet. First, the variables must be declared, that will be used in Snipp - element <em>Declarations</em>. In this case there are two possibilities, You can declare a variable as:</p>
<ul>
<li>text value - <em>Literal</em>,</li>
<li>object - then you should add a section <em>Object</em>.</li>
</ul>
<p style="text-align: justify;">As the name suggests, variables, know which type should be declared as an object. Thanks to this type of testing will be forced to input object. In our example, this is not necessary and therefore are only used items <em>Literal</em>. Variable declaration consists of the following elements:</p>
<ul>
<li>ID - <em>ID</em>,</li>
<li style="text-align: justify;">the default value which is to adopt - <em>Default</em> - there should be a moment to think and write such, which is likely to be most commonly used in the code,</li>
<li>Hints - <em>ToolTip</em>,</li>
<li>a - <em>Type</em> - only as an object variable declaration.</li>
</ul>
<p style="text-align: justify;">And at the very end you only have to define what a snippet to do. Just keep in mind, that one begins a variable name and end a <em>$</em>. A string <em>$end$</em> specifies the location where you want to find the cursor after the snippetu.</p>
<p style="text-align: justify;">Now you can import a snippet for Visual Studio (<em>Tools - > Code Snippets Manager -> Import</em>) and start to use. To do this, simply start typing the text defined in the element <em>Shortcut</em>. As soon as you are our snippet press the TAB key and enter the required information. Transition occurs between variables using the TAB key. While editing the horse and press ENTER.</p>
<p style="text-align: justify;">With things, I noticed that this is a fact, with Visual Studio does not like how they are used in Polish characters snippetach. I did not have to check this for a long time, may would help change the encoding, but I did not import the snippety, where I used Polish letters.</p>
<p style="text-align: justify;">At the end I put more example snippetu to the second part of the problem for int:</p>
[code lang="xml"]<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>GetIntFromSQL</Title>
<Shortcut>MGetInt</Shortcut>
<Description>Odczyt int z bazy danych</Description>
<Author>Michal Jankowski</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>index</ID>
<Default>column_ndx</Default>
<ToolTip>Index</ToolTip>
</Literal>
<Literal>
<ID>reader</ID>
<Default>rdr</Default>
<ToolTip>Wprowadz nazwe SQL readera</ToolTip>
</Literal>
</Declarations>
<Code Language="csharp"><![CDATA[$reader$.IsDBNull($index$) ? 0 : $reader$.GetInt32($index$);
$selected$ $end$]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>
And as the whole set of files snippets (further reading of the base types: string, DateTime, double, decimal):
Leave A Comment