insertCommand.ExecuteNonQuery ();
}
+ // execute SQL INSERT Command using ExecuteNonQuery()
+ static void InsertEdgeCaseData (IDbConnection cnc) {
+
+ IDbCommand insertCommand = cnc.CreateCommand();
+
+ insertCommand.CommandText =
+ "insert into mono_sql_test (" +
+ "varbinary_value " +
+ ") values (" +
+ "@p20 " +
+ ")";
+
+ SqlParameterCollection parameters = ((SqlCommand) insertCommand).Parameters;
+
+ parameters.Add ("@p20", SqlDbType.VarBinary, 8);
+
+ parameters ["@p20"].Value = new byte[0] {};
+
+ insertCommand.ExecuteNonQuery ();
+ }
+
// execute a SQL SELECT Query using ExecuteReader() to retrieve
// a IDataReader so we retrieve data
static IDataReader SelectData (IDbConnection cnc) {
// used internally by ReadData() to read each result set
static void ReadResult(IDataReader rdr, DataTable dt) {
- \r
- // number of columns in the table\r
- Console.WriteLine(" Total Columns: " +\r
- dt.Rows.Count);\r
-\r
- // display the schema\r
- foreach (DataRow schemaRow in dt.Rows) {\r
- foreach (DataColumn schemaCol in dt.Columns)\r
- Console.WriteLine(schemaCol.ColumnName + \r
- " = " + \r
- schemaRow[schemaCol]);\r
- Console.WriteLine();\r
- }\r
-\r
- int nRows = 0;\r
- int c = 0;\r
- string output, metadataValue, dataValue;\r
- // Read and display the rows\r
- Console.WriteLine("Gonna do a Read() now...");\r
- while(rdr.Read()) {\r
- Console.WriteLine(" Row " + nRows + ": ");\r
- \r
- for(c = 0; c < rdr.FieldCount; c++) {\r
- // column meta data \r
- DataRow dr = dt.Rows[c];\r
- metadataValue = \r
- " Col " + \r
- c + ": " + \r
- dr["ColumnName"];\r
- \r
- // column data\r
- if (rdr.IsDBNull(c) == true)\r
- dataValue = " is NULL";\r
+
+ // number of columns in the table
+ Console.WriteLine(" Total Columns: " +
+ dt.Rows.Count);
+
+ // display the schema
+ foreach (DataRow schemaRow in dt.Rows) {
+ foreach (DataColumn schemaCol in dt.Columns)
+ Console.WriteLine(schemaCol.ColumnName +
+ " = " +
+ schemaRow[schemaCol]);
+ Console.WriteLine();
+ }
+
+ int nRows = 0;
+ int c = 0;
+ string output, metadataValue, dataValue;
+ // Read and display the rows
+ Console.WriteLine("Gonna do a Read() now...");
+ while(rdr.Read()) {
+ Console.WriteLine(" Row " + nRows + ": ");
+
+ for(c = 0; c < rdr.FieldCount; c++) {
+ // column meta data
+ DataRow dr = dt.Rows[c];
+ metadataValue =
+ " Col " +
+ c + ": " +
+ dr["ColumnName"];
+
+ // column data
+ if (rdr.IsDBNull(c) == true)
+ dataValue = " is NULL";
else if ((Type) dr["DataType"] == typeof (byte[]))
dataValue =
": 0x" +
BitConverter.ToString ((byte[]) rdr.GetValue (c)).Replace ("-", "").ToLower ();
- else\r
- dataValue = \r
- ": " + \r
- rdr.GetValue(c);\r
- \r
- // display column meta data and data\r
- output = metadataValue + dataValue; \r
- Console.WriteLine(output);\r
- }\r
- nRows++;\r
- }\r
- Console.WriteLine(" Total Rows Retrieved: " + \r
+ else
+ dataValue =
+ ": " +
+ rdr.GetValue(c);
+
+ // display column meta data and data
+ output = metadataValue + dataValue;
+ Console.WriteLine(output);
+ }
+ nRows++;
+ }
+ Console.WriteLine(" Total Rows Retrieved: " +
nRows);
}
Console.WriteLine("IDataReader has a Null Reference.");
}
else {
- do {\r
- DataTable dt = rdr.GetSchemaTable();\r
- if(rdr.RecordsAffected != -1) {\r
- // Results for \r
- // SQL INSERT, UPDATE, DELETE Commands \r
- // have RecordsAffected >= 0\r
- Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);\r
- }\r
- else if(dt == null)\r
- // Results for\r
- // SQL Commands not INSERT, UPDATE, nor DELETE\r
- // have RecordsAffected == -1\r
- // and GetSchemaTable() returns a null reference\r
- Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);\r
- else {\r
- // Results for\r
- // SQL SELECT Queries\r
- // have RecordsAffected = -1\r
- // and GetSchemaTable() returns a reference to a DataTable\r
- Console.WriteLine("Result is from a SELECT SQL Query. Records Affected: " + rdr.RecordsAffected);\r
- \r
- results++;\r
- Console.WriteLine("Result Set " + results + "...");\r
-\r
- ReadResult(rdr, dt);\r
- }\r
-\r
- } while(rdr.NextResult());\r
- Console.WriteLine("Total Result sets: " + results);\r
- \r
+ do {
+ DataTable dt = rdr.GetSchemaTable();
+ if(rdr.RecordsAffected != -1) {
+ // Results for
+ // SQL INSERT, UPDATE, DELETE Commands
+ // have RecordsAffected >= 0
+ Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
+ }
+ else if(dt == null)
+ // Results for
+ // SQL Commands not INSERT, UPDATE, nor DELETE
+ // have RecordsAffected == -1
+ // and GetSchemaTable() returns a null reference
+ Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
+ else {
+ // Results for
+ // SQL SELECT Queries
+ // have RecordsAffected = -1
+ // and GetSchemaTable() returns a reference to a DataTable
+ Console.WriteLine("Result is from a SELECT SQL Query. Records Affected: " + rdr.RecordsAffected);
+
+ results++;
+ Console.WriteLine("Result Set " + results + "...");
+
+ ReadResult(rdr, dt);
+ }
+
+ } while(rdr.NextResult());
+ Console.WriteLine("Total Result sets: " + results);
+
rdr.Close();
}
}
InsertData (cnc);
Console.WriteLine ("OK");
+ /* Inserts edge case values */
+ Console.WriteLine ("\t\tInsert values that require special coding: ");
+ InsertEdgeCaseData (cnc);
+ Console.WriteLine ("OK");
+
/* Select aggregates */
SelectAggregate (cnc, "count(*)");
// FIXME: still having a problem with avg()
Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE).");
reader = SelectDataUsingInsertCommand(cnc);
ReadData(reader);
-\r
- // Call a Stored Procedure named Version()\r
- Console.WriteLine("\t\tCalling stored procedure sp_server_info()");\r
- object obj = CallStoredProcedure(cnc);\r
- Console.WriteLine("Result: " + obj);\r
-\r
- Console.WriteLine("Database Server Version: " + \r
- ((SqlConnection)cnc).ServerVersion);\r
-\r
+
+ // Call a Stored Procedure named Version()
+ Console.WriteLine("\t\tCalling stored procedure sp_server_info()");
+ object obj = CallStoredProcedure(cnc);
+ Console.WriteLine("Result: " + obj);
+
+ Console.WriteLine("Database Server Version: " +
+ ((SqlConnection)cnc).ServerVersion);
+
/* Clean up */
Console.WriteLine ("Clean up...");
Console.WriteLine ("\t\tDrop table...");
}
}
- [STAThread]\r
- static void Main(string[] args) {\r
- string connectionString = "";\r
- \r
- if(args.Length == 3 || args.Length == 4) {\r
- if(args.Length == 3) {\r
+ [STAThread]
+ static void Main(string[] args) {
+ string connectionString = "";
+
+ if(args.Length == 3 || args.Length == 4) {
+ if(args.Length == 3) {
connectionString = String.Format(
"Server={0};" +
"Database={1};" +
Console.WriteLine("Usage: mono SqlTest.exe sql_server database user_id password");
return;
}
-\r
- SqlConnection cnc = new SqlConnection ();\r
- cnc.ConnectionString = connectionString;\r
-\r
- cnc.Open();\r
+
+ SqlConnection cnc = new SqlConnection ();
+ cnc.ConnectionString = connectionString;
+
+ cnc.Open();
DoSqlTest(cnc);
cnc.Close();
}