Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It's not complete but perhaps the following T4 can serve as some inspiration.</p> <p>This is how it can be used:</p> <pre><code>using (var sqlConnection = new SqlConnection (@"Data Source=localhost\SQLExpress;Initial Catalog=MinTest;Integrated Security=True")) using (var sqlCommand = sqlConnection.CreateCommand ()) { sqlConnection.Open (); var customers = sqlCommand.Get_Customer (Id:10, CompId:20).ToArray (); foreach (var customer in customers) { Console.WriteLine (customer.Id); Console.WriteLine (customer.CompId); Console.WriteLine (customer.FirstName); Console.WriteLine (customer.LastName); Console.WriteLine (customer.OrgNo); } var deleteCount = sqlCommand.Delete_Customer (Id:20, CompId:20); Console.WriteLine ("Deleted rows: {0}", deleteCount); Console.ReadKey (); } </code></pre> <p>T4:</p> <pre><code>&lt;#@ template language = "C#" #&gt; &lt;#@ assembly name = "Microsoft.SqlServer.Management.Sdk.Sfc" #&gt; &lt;#@ assembly name = "Microsoft.SqlServer.ConnectionInfo" #&gt; &lt;#@ assembly name = "Microsoft.SqlServer.Smo" #&gt; &lt;#@ assembly name = "System.Core" #&gt; &lt;#@ assembly name = "System.XML" #&gt; &lt;#@ import namespace = "Microsoft.SqlServer.Management.Smo" #&gt; &lt;#@ import namespace = "System.Collections.Generic" #&gt; &lt;#@ import namespace = "System.Linq" #&gt; &lt;#@ import namespace = "System.Text" #&gt; // ReSharper disable InconsistentNaming // ReSharper disable PartialTypeWithSinglePart &lt;# // Update SQL Server and namespace var nameSpace = "MyNameSpace"; var server = new Server (@"localhost\SQLExpress"); var database = new Database (server,"MyDB"); var maxTables = int.MaxValue; database.Refresh (); #&gt; namespace &lt;#=nameSpace#&gt; { using System; using System.Collections.Generic; using System.Data.SqlClient; static partial class Extensions { static int Lookup (Dictionary&lt;string, int&gt; dic, string key) { int value; return dic.TryGetValue (key, out value) ? value : -1; } static byte[] GetByteArray (this SqlDataReader reader, int index) { var byteLength = reader.GetBytes (index, 0, null, 0, 0); var bytes = new byte[byteLength]; reader.GetBytes (index, 0, bytes, 0, bytes.Length); return bytes; } &lt;# foreach (var table in database.Tables.OfType&lt;Table&gt; ().Take (maxTables)) { #&gt; // -------------------------------------------------------------------- // Deletes &lt;#=table.Name#&gt; // -------------------------------------------------------------------- public static int Delete_&lt;#=table.Name#&gt; ( this SqlCommand command &lt;# foreach (var column in table.Columns.OfType&lt;Column&gt; ().Where (c =&gt; c.InPrimaryKey)) { #&gt; , &lt;#=GetType (column)#&gt; &lt;#=column.Name#&gt; &lt;# } #&gt; ) { if (command != null) { command.CommandText = "DELETE FROM &lt;#=table.Schema#&gt;.&lt;#=table.Name#&gt; &lt;#=GetWhereClause (table)#&gt;"; command.Parameters.Clear (); &lt;# foreach (var column in table.Columns.OfType&lt;Column&gt; ().Where (c =&gt; c.InPrimaryKey)) { #&gt; command.Parameters.AddWithValue (@"&lt;#=column.Name#&gt;", &lt;#=column.Name#&gt;); &lt;# } #&gt; return command.ExecuteNonQuery (); } else { return 0; } } // -------------------------------------------------------------------- // -------------------------------------------------------------------- // Gets &lt;#=table.Name#&gt; // -------------------------------------------------------------------- public static IEnumerable&lt;&lt;#=table.Name#&gt;&gt; Get_&lt;#=table.Name#&gt; ( this SqlCommand command &lt;# foreach (var column in table.Columns.OfType&lt;Column&gt; ().Where (c =&gt; c.InPrimaryKey)) { #&gt; , &lt;#=GetType (column)#&gt; &lt;#=column.Name#&gt; &lt;# } #&gt; ) { if (command != null) { command.CommandText = "SELECT * FROM &lt;#=table.Schema#&gt;.&lt;#=table.Name#&gt; &lt;#=GetWhereClause (table)#&gt;"; command.Parameters.Clear (); &lt;# foreach (var column in table.Columns.OfType&lt;Column&gt; ().Where (c =&gt; c.InPrimaryKey)) { #&gt; command.Parameters.AddWithValue (@"&lt;#=column.Name#&gt;", &lt;#=column.Name#&gt;); &lt;# } #&gt; using (var reader = command.ExecuteReader ()) { foreach (var row in reader.To_&lt;#=table.Name#&gt; ()) { yield return row; } } } } // -------------------------------------------------------------------- // -------------------------------------------------------------------- // Deserializes a &lt;#=table.Name#&gt; // -------------------------------------------------------------------- public static IEnumerable&lt;&lt;#=table.Name#&gt;&gt; To_&lt;#=table.Name#&gt; (this SqlDataReader reader) { if (reader != null) { var fieldCount = reader.FieldCount; var dictionary = new Dictionary&lt;string, int&gt; (); for (var iter = 0; iter &lt; fieldCount; ++iter) { dictionary[reader.GetName (iter)] = iter; } while (reader.Read ()) { var instance = new &lt;#=table.Name#&gt; (); &lt;# foreach (var column in table.Columns.OfType&lt;Column&gt; ()) { #&gt; { var index = Lookup (dictionary, "&lt;#=column.Name#&gt;"); if (index &gt; -1) { instance.&lt;#=column.Name #&gt; = reader.&lt;#=GetGetter (column)#&gt; (index); } } &lt;# } #&gt; yield return instance; } } } // -------------------------------------------------------------------- &lt;# } #&gt; } &lt;# foreach (var table in database.Tables.OfType&lt;Table&gt; ().Take (maxTables)) { #&gt; // ------------------------------------------------------------------------ // Table: &lt;#=table.Name#&gt; // ------------------------------------------------------------------------ partial class &lt;#=table.Name#&gt; { &lt;# foreach (var column in table.Columns.OfType&lt;Column&gt; ()) { #&gt; // &lt;#=column.DataType.Name#&gt; (&lt;#=column.Nullable#&gt;, &lt;#=column.DataType.MaximumLength#&gt;, &lt;#=column.DataType.SqlDataType#&gt;) public &lt;#=GetType (column)#&gt; &lt;#=column.Name#&gt; {get;set;} &lt;# } #&gt; } // ------------------------------------------------------------------------ &lt;# } #&gt; } &lt;#+ sealed class DataTypeDescriptor { public readonly bool IsValueType; public readonly string Type; public readonly string Getter; public DataTypeDescriptor ( bool isValueType, string type, string getter = null ) { IsValueType = isValueType; Type = type; Getter = getter ?? ("Get" + type); } } readonly static Dictionary&lt;SqlDataType, DataTypeDescriptor&gt; s_typeDescriptors = new Dictionary&lt;SqlDataType, DataTypeDescriptor&gt; () { // Unsupported: // ------------ // None, // UserDefinedDataType, // UserDefinedDataType, // XML, // SysName, // UserDefinedTableType, // HierarchyId, // Geometry, // Geography {SqlDataType.BigInt , new DataTypeDescriptor (true , "Int64" )}, {SqlDataType.Binary , new DataTypeDescriptor (true , "Byte" )}, {SqlDataType.Bit , new DataTypeDescriptor (true , "Boolean" )}, {SqlDataType.Char , new DataTypeDescriptor (false , "String" )}, {SqlDataType.DateTime , new DataTypeDescriptor (true , "DateTime" )}, {SqlDataType.Decimal , new DataTypeDescriptor (true , "Decimal" )}, {SqlDataType.Float , new DataTypeDescriptor (true , "Double" )}, {SqlDataType.Image , new DataTypeDescriptor (false , "byte[]" , "GetByteArray" )}, {SqlDataType.Int , new DataTypeDescriptor (true , "Int32" )}, {SqlDataType.Money , new DataTypeDescriptor (true , "Decimal" )}, {SqlDataType.NChar , new DataTypeDescriptor (false , "String" )}, {SqlDataType.NText , new DataTypeDescriptor (false , "String" )}, {SqlDataType.NVarChar , new DataTypeDescriptor (false , "String" )}, {SqlDataType.NVarCharMax , new DataTypeDescriptor (false , "String" )}, {SqlDataType.Real , new DataTypeDescriptor (true , "Double" )}, {SqlDataType.SmallDateTime , new DataTypeDescriptor (true , "DateTime" )}, {SqlDataType.SmallInt , new DataTypeDescriptor (true , "Int16" )}, {SqlDataType.SmallMoney , new DataTypeDescriptor (true , "Decimal" )}, {SqlDataType.Text , new DataTypeDescriptor (false , "String" )}, {SqlDataType.Timestamp , new DataTypeDescriptor (false , "byte[]" , "GetByteArray" )}, {SqlDataType.TinyInt , new DataTypeDescriptor (true , "Byte" )}, {SqlDataType.UniqueIdentifier , new DataTypeDescriptor (false , "byte[]" , "GetByteArray" )}, {SqlDataType.VarBinary , new DataTypeDescriptor (false , "byte[]" , "GetByteArray" )}, {SqlDataType.VarBinaryMax , new DataTypeDescriptor (false , "byte[]" , "GetByteArray" )}, {SqlDataType.VarChar , new DataTypeDescriptor (false , "String" )}, {SqlDataType.VarCharMax , new DataTypeDescriptor (false , "String" )}, {SqlDataType.Variant , new DataTypeDescriptor (false , "object" , "GetValue")}, {SqlDataType.Numeric , new DataTypeDescriptor (true , "Decimal" )}, {SqlDataType.Date , new DataTypeDescriptor (true , "DateTime" )}, {SqlDataType.Time , new DataTypeDescriptor (true , "TimeSpan" )}, {SqlDataType.DateTimeOffset , new DataTypeDescriptor (true , "DateTimeOffset" )}, {SqlDataType.DateTime2 , new DataTypeDescriptor (true , "DateTime" )}, }; static string GetGetter (Column column) { var dataType = column.DataType; DataTypeDescriptor descriptor; if (!s_typeDescriptors.TryGetValue (dataType.SqlDataType, out descriptor)) { return "Error__Unsupported_Type_" + dataType.SqlDataType ; } return descriptor.Getter; } static string GetType (Column column) { var dataType = column.DataType; DataTypeDescriptor descriptor; if (!s_typeDescriptors.TryGetValue (dataType.SqlDataType, out descriptor)) { return "Error__Unsupported_Type_" + dataType.SqlDataType ; } if (column.Nullable &amp;&amp; descriptor.IsValueType) { return descriptor.Type + "?"; } return descriptor.Type; } static string GetWhereClause (Table table) { var sb = new StringBuilder ("WHERE "); var first = true; foreach (var column in table.Columns.OfType&lt;Column&gt; ().Where (c =&gt; c.InPrimaryKey)) { if (first) { first = false; } else { sb.Append (" AND "); } sb.AppendFormat ("{0} = @{0}", column.Name); } return sb.ToString (); } #&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POhow to generate a Custom Class via t4?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJust another metaprogrammer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload