SQL C#
Insert a Data Row with All Types
This C# program demonstrates how to create an SQL database table with all data types in it and insert a row of all data types in a database for Microsoft SQL in C#.
Program.cs
// Create a table with one value of each type using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.IO; namespace CreateTable { class Program { static void Main(string[] args) { Console.WriteLine(CreateTable()); Console.WriteLine( Insert( 92985290495, new Byte[]{ 255, 224, 196, 9 }, true, "Edward", DateTime.Now, DateTime.Now, DateTime.Now, DateTimeOffset.Now, 7853.28M, new Byte[] { (byte)'f', (byte)'i', (byte)'l', (byte)'e' }, 3.14159, // Alternative image file stream: yaImageBytes // Alternatively, this could be used to load an image file /* // Read an image into a byte stream FileStream qFileStream; qFileStream = new FileStream("SaintAugustine.png", FileMode.Open, FileAccess.Read); byte[] yaImageBytes = new byte[qFileStream.Length]; qFileStream.Read(yaImageBytes, 0, System.Convert.ToInt32(qFileStream.Length)); qFileStream.Close(); */ new Byte[] { (byte)'i', (byte)'m', (byte)'a', (byte)'g', (byte)'e' }, 287, 3.50M, "James", "Hello", 8.23M, "Peter", 7.19f, // The row version is set automatically. This is just a place holder. new Byte[] { (byte)'a', (byte)'u', (byte)'t', (byte)'o' }, DateTime.Now, 34, 3.50M, null, "Goodbye", new TimeSpan(5, 30, 20), 34, new Guid(10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20), new Byte[] { (byte)'b', (byte)'i', (byte)'n' }, "Paul", "<root></root>" ) ); } // Press Ctrl+Shift+R to refresh the screen and remove the "Invalid Column name" message in Microsoft SQL Server Management Studio public static int CreateTable() { try { // The using block ensures that the connection is closed when it exits this block. using (SqlConnection qConnection = new SqlConnection( @"Server=LAPTOP-FHJLCP14\SQLEXPRESS;Database=xoax;Trusted_Connection=True;")) { SqlCommand qCommand = qConnection.CreateCommand(); // The first column is the primary key. qCommand.CommandText = "CREATE TABLE all_types(" + "id int IDENTITY(3,2) PRIMARY KEY," + " large_64bit_int bigint," // Up to 4 bytes + " bits_as_bytes binary(4)," + " boolean_value bit," // Up to 30 characters + " char_value char(30)," + " just_a_date date," + " date_time datetime," + " date_time2 datetime2," + " date_time_offset datetimeoffset," // 10 digits with 4 after the decimal point + " decimal_value decimal(10, 4)," // Up to 5 bytes + " file_stream varbinary(5)," + " float_value float," + " image_value image," + " int_value int," + " money_value money," // Like char, but uses unicode characters in two bytes, instead of single-byte chars + " nchar_value nchar(30)," + " ntext_value ntext," + " numeric_value numeric," + " nvarchar_value nvarchar(10)," + " real_value real," // Automatically generated + " rowversion_value rowversion," + " smalldatetime_value smalldatetime," + " smallint_value smallint," + " smallmoney_value smallmoney," + " sql_variant_value sql_variant," + " text_value text," + " time_value time," // We can not have two timestamp columns, rowversion is already one //+ " timestamp_value timestamp," + " tinyint_value tinyint," + " uniqueidentifier_value uniqueidentifier," + " varbinary_value varbinary(10)," + " varchar_value varchar(10)," + " xml_value xml" + " );"; qCommand.Connection.Open(); return qCommand.ExecuteNonQuery(); } } catch (Exception e) { Console.WriteLine(e.Message); Console.ReadKey(); return -5; } } public static int Insert(Int64 iLargeInt64, Byte[] yaBits, Boolean bBoolean, String sCharValue, DateTime qJustDate, DateTime qDateTime, DateTime qDateTime2, DateTimeOffset qDateTimeOffset, Decimal tDecimal, Byte[] yaFileStream, Double dFloat, Byte[] yaImage, int iInt, Decimal tMoney, String sNChar, String sNText, Decimal tNumeric, String sNVarChar, Single fReal, Byte[] yaRowVersion, DateTime qSmallDateTime, Int16 iSmallInt16, Decimal tSmallMoney, Object xSql_Variant, String sText, TimeSpan qTime, Byte yTinyInt8, Guid qGUID, Byte[] yaVarbinary, String sVarChar, String sXml) { // The using block ensures that the connection is closed when it exits this block. using (SqlConnection qConnection = new SqlConnection( @"Server=LAPTOP-FHJLCP14\SQLEXPRESS;Database=xoax;Trusted_Connection=True;")) { SqlCommand qCommand = qConnection.CreateCommand(); // The first column is the primary key. // It is also an int value that is automatically generated, // starting at 3 and incrementing by 2 for each entry. qCommand.CommandText = "INSERT INTO all_types(large_64bit_int, bits_as_bytes, boolean_value, char_value, just_a_date, date_time, date_time2, date_time_offset, decimal_value," + " file_stream, float_value, image_value, int_value, money_value, nchar_value, ntext_value, numeric_value, nvarchar_value, real_value,"//, rowversion_value" + " smalldatetime_value, smallint_value, smallmoney_value,"/*, sql_variant_value"*/ + "text_value, time_value, tinyint_value, uniqueidentifier_value, varbinary_value," + " varchar_value, xml_value" + ") VALUES (@LARGE_64BIT_INT, @BITS_AS_BYTES, @BOOLEAN_VALUE, @CHAR_VALUE, @JUST_A_DATE, @DATE_TIME, @DATE_TIME2, @DATE_TIME_OFFSET, @DECIMAL_VALUE," + " @FILE_STREAM, @FLOAT_VALUE, @IMAGE_VALUE, @INT_VALUE, @MONEY_VALUE, @NCHAR_VALUE, @NTEXT_VALUE, @NUMERIC_VALUE, @NVARCHAR_VALUE, @REAL_VALUE,"//, @ROWVERSION_VALUE" + " @SMALLDATETIME_VALUE, @SMALLINT_VALUE, @SMALLMONEY_VALUE,"/*, @SQL_VARIANT_VALUE"*/ + "@TEXT_VALUE, @TIME_VALUE, @TINYINT_VALUE, @UNIQUEIDENTIFIER_VALUE, @VARBINARY_VALUE," + " @VARCHAR_VALUE, @XML_VALUE" + ")"; qCommand.Parameters.AddWithValue("@LARGE_64BIT_INT", iLargeInt64); qCommand.Parameters.AddWithValue("@BITS_AS_BYTES", yaBits); qCommand.Parameters.AddWithValue("@BOOLEAN_VALUE", bBoolean); qCommand.Parameters.AddWithValue("@CHAR_VALUE", sCharValue); qCommand.Parameters.AddWithValue("@JUST_A_DATE", qJustDate); qCommand.Parameters.AddWithValue("@DATE_TIME", qDateTime); qCommand.Parameters.AddWithValue("@DATE_TIME2", qDateTime2); qCommand.Parameters.AddWithValue("@DATE_TIME_OFFSET", qDateTimeOffset); qCommand.Parameters.AddWithValue("@DECIMAL_VALUE", tDecimal); qCommand.Parameters.AddWithValue("@FILE_STREAM", yaFileStream); qCommand.Parameters.AddWithValue("@FLOAT_VALUE", dFloat); qCommand.Parameters.AddWithValue("@IMAGE_VALUE", yaImage); qCommand.Parameters.AddWithValue("@INT_VALUE", iInt); qCommand.Parameters.AddWithValue("@MONEY_VALUE", tMoney); qCommand.Parameters.AddWithValue("@NCHAR_VALUE", sNChar); qCommand.Parameters.AddWithValue("@NTEXT_VALUE", sNText); qCommand.Parameters.AddWithValue("@NUMERIC_VALUE", tNumeric); qCommand.Parameters.AddWithValue("@NVARCHAR_VALUE", sNVarChar); qCommand.Parameters.AddWithValue("@REAL_VALUE", fReal); //qCommand.Parameters.AddWithValue("@ROWVERSION_VALUE", yaRowVersion); qCommand.Parameters.AddWithValue("@SMALLDATETIME_VALUE", qSmallDateTime); qCommand.Parameters.AddWithValue("@SMALLINT_VALUE", iSmallInt16); qCommand.Parameters.AddWithValue("@SMALLMONEY_VALUE", tSmallMoney); //qCommand.Parameters.AddWithValue("@SQL_VARIANT_VALUE", NULL); qCommand.Parameters.AddWithValue("@TEXT_VALUE", sText); qCommand.Parameters.AddWithValue("@TIME_VALUE", qTime); qCommand.Parameters.AddWithValue("@TINYINT_VALUE", yTinyInt8); qCommand.Parameters.AddWithValue("@UNIQUEIDENTIFIER_VALUE", qGUID); qCommand.Parameters.AddWithValue("@VARBINARY_VALUE", yaVarbinary); qCommand.Parameters.AddWithValue("@VARCHAR_VALUE", sVarChar); qCommand.Parameters.AddWithValue("@XML_VALUE", sXml); qCommand.Connection.Open(); return qCommand.ExecuteNonQuery(); } } } }
Output
-1 1 Press any key to continue . . .
Before
After
© 2007–2025 XoaX.net LLC. All rights reserved.