public static void MultipleDataSets(string strSQL) { DataSetSerialization ser = new DataSetSerialization(); using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL , System.Data.CommandBehavior.CloseConnection | System.Data.CommandBehavior.SequentialAccess ) ) { Table tbl = null; do { tbl = new Table(); for (int i = 0; i < dr.FieldCount; ++i) { tbl.Columns.Add( new ColumnInfo() { ColumnName = dr.GetName(i), FieldType = dr.GetFieldType(i) } ); } // Next i if (dr.HasRows) { while (dr.Read()) { object[] thisRow = new object[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; ++i) { thisRow[i] = dr.GetValue(i); } // Next i tbl.Rows.Add(thisRow); } // Whend } // End if (dr.HasRows) ser.Tables.Add(tbl); } while (dr.NextResult()); } // End Using dr string str = EasyJSON.JsonHelper.SerializePretty(ser); System.Console.WriteLine(str); DataSetSerialization ser2 = EasyJSON.JsonHelper.Deserialize <DataSetSerialization>(str); System.Console.WriteLine(ser2); } // End Sub MultipleDataSets
private void NextResult() { if (reader.NextResult()) { readCount++; gridIndex++; IsConsumed = false; } else { // happy path; close the reader cleanly - no // need for "Cancel" etc reader.Dispose(); reader = null; callbacks?.OnCompleted(); Dispose(); } }
public async Task <object> InsertNewLabListWithSelect(Lab_list_detail ldata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Lab_list_detail> result = new List <Lab_list_detail>(); string temp1tablename = "#temp1"; string temp2tablename = "#temp2"; string createtabletemp1 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NOT NULL," + "PRIMARY KEY ([row_num])) ", temp1tablename, FieldName.LAB_NUM); string createtabletemp2 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NULL," + "PRIMARY KEY ([row_num])) " , temp2tablename, Lab_officer.FieldName.OFFICER); string insertintotemp1 = string.Format("INSERT INTO {0} " + "select * from " + "(insert into {1} output inserted.{2} values " + "('{3}','{4}','{5}',{6})) as outputinsert ", temp1tablename, FieldName.TABLE_NAME, FieldName.LAB_NUM, ldata.name, ldata.room, ldata.curri_id, ldata.aca_year); string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename); foreach (Personnel_with_t_name p in ldata.officer) { insertintotemp2 += string.Format(",({0})", p.user_id); } string insertintolabofficer = string.Format(" INSERT INTO {0} " + "select {1},{2} from {3},{4} where {2} is not null ", Lab_officer.FieldName.TABLE_NAME, FieldName.LAB_NUM, Lab_officer.FieldName.OFFICER, temp1tablename, temp2tablename); curri_id = ldata.curri_id; aca_year = ldata.aca_year; string selectcmd = getSelectByCurriculumAcademicCommand(); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2, insertintotemp1, insertintotemp2, insertintolabofficer, selectcmd); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { if (data.Columns.Contains(FieldName.ROOM)) { result.Add(new Lab_list_detail { lab_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), room = item.ItemArray[data.Columns[FieldName.ROOM].Ordinal].ToString() }); } else { int labid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]); result.First(t => t.lab_num == labid).officer.Add(new Personnel_with_t_name { user_id = Convert.ToInt32(item.ItemArray[data.Columns[Lab_officer.FieldName.OFFICER].Ordinal]), t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString() }); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
public async Task <object> UpdateLabListWithSelect(Lab_list_detail ldata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Lab_list_detail> result = new List <Lab_list_detail>(); string updatelablistcmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}' " + "where {5} = {6} ", FieldName.TABLE_NAME, FieldName.NAME, ldata.name, FieldName.ROOM, ldata.room, FieldName.LAB_NUM, ldata.lab_num); string deletefromlabofficer = string.Format("DELETE FROM {0} where {1} = {2} ", Lab_officer.FieldName.TABLE_NAME, Lab_officer.FieldName.LAB_NUM, ldata.lab_num); string insertintolabofficer = string.Format("INSERT INTO {0} values ", Lab_officer.FieldName.TABLE_NAME); foreach (Personnel_with_t_name p in ldata.officer) { insertintolabofficer += string.Format("({0},{1})", ldata.lab_num, p.user_id); if (p != ldata.officer.Last()) { insertintolabofficer += ","; } } curri_id = ldata.curri_id; aca_year = ldata.aca_year; string selectcmd = getSelectByCurriculumAcademicCommand(); string updatecondition = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME, FieldName.LAB_NUM, ldata.lab_num); d.iCommand.CommandText = string.Format("{4} BEGIN {0} {1} {2} {3} END", updatelablistcmd, deletefromlabofficer, insertintolabofficer, selectcmd, updatecondition); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { if (data.Columns.Contains(FieldName.ROOM)) { result.Add(new Lab_list_detail { lab_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), room = item.ItemArray[data.Columns[FieldName.ROOM].Ordinal].ToString() }); } else { int labid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]); result.First(t => t.lab_num == labid).officer.Add(new Personnel_with_t_name { user_id = Convert.ToInt32(item.ItemArray[data.Columns[Lab_officer.FieldName.OFFICER].Ordinal]), t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString() }); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
public async Task <object> SelectByCurriculumAndTitle() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } Extra_privilege_by_type_with_privilege_choices result = new Extra_privilege_by_type_with_privilege_choices(); string temp5tablename = "#temp5"; string selectTitle = string.Format("select * from {0} where {1} = {2} ", Title.FieldName.TABLE_NAME, Title.FieldName.TITLE_CODE, title_code); string selectPrivilegeChoices = string.Format("select * from {0} where {1} = {2} ", Title_privilege.FieldName.TABLE_NAME, Title_privilege.FieldName.TITLE_CODE, title_code); string createtabletemp5 = string.Format("create table {0} ( " + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NOT NULL," + "[{2}] {6} NOT NULL," + "[{3}] INT NOT NULL," + "[{4}] INT NOT NULL," + "[{5}] VARCHAR(40) NOT NULL, " + "PRIMARY KEY([row_num]) " + ") " + "alter table {0} " + "alter column [{2}] {6} collate database_default " + "alter table {0} " + "alter column [{5}] VARCHAR(40) collate database_default ", temp5tablename, FieldName.USER_TYPE_ID, FieldName.CURRI_ID, FieldName.TITLE_CODE, FieldName.TITLE_PRIVILEGE_CODE, User_type.FieldName.USER_TYPE_NAME, DBFieldDataType.CURRI_ID_TYPE ); string insertintotemp5_maindata = string.Format("insert into {9} " + "select {0}.*,{1} from {0},{2} " + "where {3} = {4} and {5} = '{6}' " + "and {0}.{7} = {2}.{8} ", FieldName.TABLE_NAME, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME, FieldName.TITLE_CODE, title_code, FieldName.CURRI_ID, curri_id, FieldName.USER_TYPE_ID, User_type.FieldName.USER_TYPE_ID, temp5tablename ); string insertintotemp5_setdefaultdata = string.Format("insert into {12} " + "select {0}.{1},'{2}' as {3},{4},{5},{6} " + "from {0},{7} " + "where {4} = {8} " + "and not exists " + "(select * from {12} where " + "{0}.{1} = {9} " + "and {0}.{4} = {10}) " + "and {0}.{1} = {7}.{11} ", Default_privilege_by_type.FieldName.TABLE_NAME, Default_privilege_by_type.FieldName.USER_TYPE_ID, curri_id, FieldName.CURRI_ID, Default_privilege_by_type.FieldName.TITLE_CODE, Default_privilege_by_type.FieldName.TITLE_PRIVILEGE_CODE, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME, title_code, FieldName.USER_TYPE_ID, FieldName.TITLE_CODE, User_type.FieldName.USER_TYPE_ID, temp5tablename ); string selectfromtemp5 = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.USER_TYPE_ID); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp5, insertintotemp5_maindata, insertintotemp5_setdefaultdata, selectTitle, selectPrivilegeChoices, selectfromtemp5); try { //Set result's curri_id result.curri_id = curri_id; System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable tabledata = new DataTable(); tabledata.Load(res); foreach (DataRow item in tabledata.Rows) { if (tabledata.Columns.Contains(Title.FieldName.NAME)) { //Set title name from title table result result.title_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title.FieldName.TITLE_CODE].Ordinal]); result.name = item.ItemArray[tabledata.Columns[Title.FieldName.NAME].Ordinal].ToString(); } else if (tabledata.Columns.Contains(Title_privilege.FieldName.PRIVILEGE)) { //Set privilege choice for target title result.choices.Add(new Privilege_choice { title_privilege_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]), privilege = item.ItemArray[tabledata.Columns[Title_privilege.FieldName.PRIVILEGE].Ordinal].ToString() }); } else { //Read main privilege data int title_priv_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.TITLE_PRIVILEGE_CODE].Ordinal]); result.privilege_list.Add(new User_type_privilege { user_type_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.USER_TYPE_ID].Ordinal]), user_type = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(), privilege = new Privilege_choice { title_privilege_code = title_priv_code, //Find privilege caption from choices array privilege = result.choices.First(t => t.title_privilege_code == title_priv_code).privilege } }); } } tabledata.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
}// End Sub public static void SerializeDataTableAsAssociativeJsonArray( System.Data.Common.DbCommand cmd , Microsoft.AspNetCore.Http.HttpContext context , bool pretty , System.Text.Encoding enc) { SqlService service = (SqlService)context.RequestServices.GetService(typeof(SqlService)); using (System.IO.TextWriter sw = new System.IO.StreamWriter(context.Response.Body, enc)) { using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(sw)) { if (pretty) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; } // jsonWriter.WriteStartObject(); // jsonWriter.WritePropertyName("tables"); // jsonWriter.WriteStartArray(); using (System.Data.Common.DbConnection con = service.Connection) { cmd.Connection = con; if (con.State != System.Data.ConnectionState.Open) { con.Open(); } try { using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection )) { do { // jsonWriter.WriteStartObject(); // tbl = new Table(); //jsonWriter.WritePropertyName("columns"); //// WriteArray(jsonWriter, dr); //WriteAssociativeArray(jsonWriter, dr); //jsonWriter.WritePropertyName("rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { string[] columns = new string[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; i++) { columns[i] = dr.GetName(i); } // Next i while (dr.Read()) { // jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount]; jsonWriter.WriteStartObject(); // tbl = new Table(); for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WritePropertyName(columns[i]); object obj = dr.GetValue(i); if (obj == System.DBNull.Value) { obj = null; } jsonWriter.WriteValue(obj); } // Next i // jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow); jsonWriter.WriteEndObject(); } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); // jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl); } while (dr.NextResult()); } // End using dr } catch (System.Exception ex) { System.Console.WriteLine(ex.Message); throw; } if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End using con // jsonWriter.WriteEndArray(); // jsonWriter.WriteEndObject(); jsonWriter.Flush(); } // End Using jsonWriter } // End Using sw } // End Sub SerializeDataTableAsAssociativeJsonArray
public override void ExecuteResult(System.Web.Mvc.ControllerContext context) { context.HttpContext.Response.ContentType = "application/json"; context.HttpContext.Response.ContentEncoding = System.Text.Encoding.UTF8; using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.HttpContext.Response.Output)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("Tables"); jsonWriter.WriteStartArray(); using (System.Data.Common.DbConnection con = SQL.CreateConnection()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (System.Data.Common.DbCommand cmd = this.GetCommand(con)) { using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection )) { do { jsonWriter.WriteStartObject(); // tbl = new Table(); jsonWriter.WritePropertyName("Columns"); jsonWriter.WriteStartArray(); for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("ColumnName"); jsonWriter.WriteValue(dr.GetName(i)); jsonWriter.WritePropertyName("FieldType"); jsonWriter.WriteValue(SQL.GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i))); jsonWriter.WriteEndObject(); } // Next i jsonWriter.WriteEndArray(); jsonWriter.WritePropertyName("Rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { while (dr.Read()) { object[] thisRow = new object[dr.FieldCount]; jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteValue(dr.GetValue(i)); } // Next i jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow); } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl); } while (dr.NextResult()); } // End using dr } // End using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End using con jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); jsonWriter.Flush(); } // End Using jsonWriter context.HttpContext.Response.Output.Flush(); context.HttpContext.Response.OutputStream.Flush(); context.HttpContext.Response.Flush(); } // End Sub SerializeLargeDataset
public static void LinqTest() { System.Collections.Generic.List <string> ls = new System.Collections.Generic.List <string>(); ls.Add("foo"); ls.Add("bar"); ls.Add("foobar"); int oobj = 123; Person someOne = new Person() { Name = "foo", Email = "*****@*****.**", SnailMail = "Snail" }; // object inexistant = GetProperty(someOne, "Inexistant"); object myName = GetProperty(someOne, "Name"); string myNameString = GetProperty <Person, string>(someOne, "Name"); int?nullMe = GetProperty <Person, int?>(someOne, "NullableNumber"); object nullNumObj = GetProperty(someOne, "NullableNumber"); int? nullNum = GetProperty <Person, int?>(someOne, "NullableNumber"); System.Console.WriteLine(nullNum); SetProperty(someOne, "NullableNumber", null); System.Console.WriteLine(someOne); SetProperty(someOne, "NullableNumber", -123); System.Console.WriteLine(someOne); SetProperty(someOne, "NullableNumber", "-123"); System.Console.WriteLine(someOne); SetProperty(someOne, "NullableNumber", System.DBNull.Value); System.Console.WriteLine(someOne); // object obj = System.DBNull.Value; // SetProperty(someOne, "NullableNumber", obj); System.Console.WriteLine(myName); System.Console.WriteLine(myNameString); // SetProperty(someOne, "Anumber", oobj); // SetProperty(someOne, "SnailMail", "Turtle Mail"); // SetProperty(someOne, "Email", "SpamMail"); T_User ben = new T_User(); int cnt = GetProperty <System.Collections.Generic.List <string>, int>(ls, "cOuNt"); object objCount = GetProperty(ls, "cOuNt"); System.Console.WriteLine(cnt); // b15186d6-adb1-4c8a-bbfa-830b24417e8b string SQL = @"SELECT 'B15186D6-ADB1-4C8A-BBFA-830B24417E8B' AS BE_UID, '123' AS BE_ID, 'Carbon Unit' AS BE_Name, '*****@*****.**' AS EMail, 'omg' AS SnailMail, CAST(NULL AS integer) AS NullableNumber;"; // SQL = @"SELECT CAST(NULL AS uniqueidentifier) AS BE_UID"; // Test NULLing non-null type error message... using (System.Data.Common.DbDataReader rdr = null) // CoinBaseSharp.SQL.ExecuteReader(SQL)) { do { int fieldCount = rdr.FieldCount; System.Type[] ts = new System.Type[fieldCount]; string[] fieldNames = new string[fieldCount]; System.Action <T_User, object>[] fieldSetters = new System.Action <T_User, object> [fieldCount]; for (int i = 0; i < fieldCount; ++i) { ts[i] = rdr.GetFieldType(i); fieldNames[i] = rdr.GetName(i); fieldSetters[i] = GetSetter <T_User>(fieldNames[i]); } // Next i if (rdr.HasRows) { while (rdr.Read()) { for (int i = 0; i < fieldCount; ++i) { object objValue = rdr.GetValue(i); // if (object.ReferenceEquals(objValue, System.DBNull.Value)) objValue = null; System.Console.WriteLine(ts[i]); //int abc = 123; // SetProperty(ben, fieldNames[i], abc); // SetProperty(ben, fieldNames[i], objValue); fieldSetters[i](ben, objValue); System.Console.WriteLine(objValue); } // Next i } // Whend } // End if (rdr.HasRows) } while (rdr.NextResult()); } // End Using rdr System.Console.WriteLine(ben.BE_UID); } // End Sub LinqTest
public static void Test() { System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder(); csb.DataSource = System.Environment.MachineName; csb.IntegratedSecurity = true; if (!csb.IntegratedSecurity) { csb.UserID = "DAL_Test"; csb.Password = "******"; } // End if (!csb.IntegratedSecurity) csb.InitialCatalog = "COR_Basic_Demo_V4"; cDAL DAL = cDAL.CreateInstance(); DAL.ConnectionString = csb.ConnectionString; using (System.Data.Common.DbConnection dbConnection = DAL.GetConnection()) { object objUser1 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_User;", dbConnection); object objUser2 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_ID;", dbConnection); DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection); DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection); #if WITH_CONNECTION //using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", dbConnection)) using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader_Buggy("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;")) #else DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", delegate(System.Data.Common.DbDataReader reader) #endif { do { for (int i = 0; i < reader.FieldCount; ++i) { string fieldName = reader.GetName(i); System.Type fieldType = reader.GetFieldType(i); System.Console.WriteLine("{0}:\t{1}\t{2}", i, fieldName, fieldType.ToString()); } // Next i if (reader.HasRows) { int rowCount = 1; while (reader.Read()) { System.Console.WriteLine(@"Row {0}", rowCount); for (int i = 0; i < reader.FieldCount; ++i) { string fieldName = reader.GetName(i); object fieldValue = reader.GetValue(i); System.Console.WriteLine(@" - {0}: {1}", fieldName, System.Convert.ToString(fieldValue)); } // Next i ++rowCount; } // Whend --rowCount; } // End if (reader.HasRows) } while (reader.NextResult()); } // End Using reader #if !WITH_CONNECTION ); #endif object objUser3 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Hash;", dbConnection); object objUser4 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Passwort;", dbConnection); } // End Using dbConnection } // End Sub Test
public async Task <object> SelectAllCurriculumsAndAllPresidents() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } Curriculums_presidents_detail result = new Curriculums_presidents_detail(); string selcurriculumdata = string.Format("select {0}.{1},{2} " + "from {0},{3} " + "where {0}.{1} = {3}.{4} and {5} = {6} ", Cu_curriculum.FieldName.TABLE_NAME, Cu_curriculum.FieldName.CURRI_ID, Cu_curriculum.FieldName.CURR_TNAME, Curriculum_academic.FieldName.TABLE_NAME, Curriculum_academic.FieldName.CURRI_ID, Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR); string selallpresident = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " + "where {6} = {7} and {8} = {9} ", FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC, Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.TEACHER_ID, FieldName.ACA_YEAR, ParameterName.ACA_YEAR); string selallteacherwithcurri = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " + "where {6} = 1 and {0}.{7} = {5}.{8} " + "and {9} in (select {10} from {11} where {12} = {13}) ", User_curriculum.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC, Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_curriculum.FieldName.USER_ID, User_list.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, Curriculum_academic.FieldName.CURRI_ID, Curriculum_academic.FieldName.TABLE_NAME, Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year)); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} END", selcurriculumdata, selallpresident, selallteacherwithcurri); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); if (data.Columns.Count == 2) { foreach (DataRow item in data.Rows) { curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString(); result.all_curri_id.Add(curri_id); result.all_presidents[curri_id] = new Curri_with_pres_and_cand { curri_tname = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString() }; } } else if (data.Columns.Count == 7) { foreach (DataRow item in data.Rows) { curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString(); result.all_presidents[curri_id].presidents.Add(new Personnel_brief_detail { tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(), pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()), email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(), user_id = item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString() }); } } else { foreach (DataRow item in data.Rows) { curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString(); result.all_presidents[curri_id].candidates.Add(new Personnel_brief_detail { tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(), pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()), email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(), user_id = item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal].ToString() }); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
public async Task <object> SelectByIndicator() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } Others_evaluation_s_indic_name_list_with_file_name result = new Others_evaluation_s_indic_name_list_with_file_name(); string temp5tablename = "#temp5"; string createtabletemp5 = string.Format("CREATE TABLE {0}(" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] VARCHAR(2000) NULL," + "[{2}] INT NULL," + "[{3}] INT NULL," + "[{4}] INT NULL," + "[{5}] {16} NULL," + "[{6}] CHAR NULL," + "[{7}] VARCHAR(MAX) NULL," + "[{8}] VARCHAR(MAX) NULL," + "[{9}] DATE NULL," + "[{10}] TIME(0) NULL," + "[{11}] {15} NULL," + "[{12}] {14} NULL," + "[{13}] INT NULL," + "[{17}] VARCHAR(16) NULL," + "[{18}] VARCHAR(60) NULL," + "PRIMARY KEY([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN [{1}] VARCHAR(2000) collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{5}] {16} collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{6}] CHAR collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{7}] VARCHAR(MAX) collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{8}] VARCHAR(MAX) collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{11}] {15} collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{12}] {14} collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{17}] VARCHAR(16) collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{18}] VARCHAR(60) collate DATABASE_DEFAULT ", temp5tablename, Sub_indicator.FieldName.SUB_INDICATOR_NAME, FieldName.OTHERS_EVALUATION_ID, FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, FieldName.ASSESSOR_ID, FieldName.EVALUATION_SCORE, FieldName.STRENGTH, FieldName.IMPROVE, FieldName.DATE, FieldName.TIME, Evidence.FieldName.FILE_NAME, FieldName.CURRI_ID, FieldName.ACA_YEAR, DBFieldDataType.CURRI_ID_TYPE, DBFieldDataType.FILE_NAME_TYPE, DBFieldDataType.USERNAME_TYPE, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME); string insertintotemp5_1 = string.Format("insert into {13} " + "select {2}, {0}.*,{17},{18} " + "from {0}, {1}, {14} " + "where {0}.{3} = {4} and " + "{0}.{5} = {1}.{6} and " + "{0}.{3} = {1}.{7} and " + "{15} = {16} and " + //user_id = assessor_id "{1}.{8} = " + "(select max(s1.{8}) from {1} as s1 where s1.{8} <= {9}) and " + "{0}.{10} = '{11}' and " + "{0}.{12} = {9} ", FieldName.TABLE_NAME, Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.SUB_INDICATOR_NAME, FieldName.INDICATOR_NUM, indicator_num, FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.ACA_YEAR, aca_year, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, temp5tablename, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.ASSESSOR_ID, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME); string insertintotemp5_2 = string.Format("insert into {12} " + "select {1},0,{2},{3}," + "'','0','','',null,null,'','{4}',{6},null,null " + "from {0} where " + "{2} = {13} and {5} = " + "(select max(s1.{5}) from {0} as s1 where s1.{5} <= {6}) " + "and not exists(select * from {7} " + "where {8} = '{4}' and {9} = {6} and " + "{0}.{2} = {7}.{10} " + "and {0}.{3} = {7}.{11}) ", Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.SUB_INDICATOR_NAME, Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM, curri_id, Sub_indicator.FieldName.ACA_YEAR, aca_year, FieldName.TABLE_NAME, FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, temp5tablename, indicator_num); string insertintotemp5_3truecase = string.Format("insert into {0} " + "select {1}," + "{2}.*," + "{3}, {4} from {2}, {5}, {6} " + "where {2}.{7} = {8} " + "and {2}.{7} = {5}.{9} " + "and {2}.{10} = 0 " + "and {11} = {12} " + "and {5}.{13} = " + "(select max(s1.{13}) from {5} as s1 where s1.{13} <= {14}) " + "and {2}.{15} = '{16}' and {2}.{17} = {14} ", temp5tablename, Indicator.FieldName.INDICATOR_NAME_E, /*2 others*/ FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, /*5 ind*/ Indicator.FieldName.TABLE_NAME, /*6 usr*/ User_list.FieldName.TABLE_NAME, FieldName.INDICATOR_NUM, indicator_num, Indicator.FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, User_list.FieldName.USER_ID, FieldName.ASSESSOR_ID, Indicator.FieldName.ACA_YEAR, aca_year, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR ); string insertintotemp5_3falsecase = string.Format("insert into {0} select " + "{1}," + "0,{2},0,'','0','','',null,null,'','{3}',{4},null,null " + "from {5} " + "where {6} = {2} " + "and {5}.{7} = (select max({7}) from {5} where {7} <= {4}) ", temp5tablename, Indicator.FieldName.INDICATOR_NAME_E, indicator_num, curri_id, aca_year, Indicator.FieldName.TABLE_NAME, Indicator.FieldName.INDICATOR_NUM, Indicator.FieldName.ACA_YEAR); string insertintotemp5_3 = string.Format("if exists (select * from {0} where {1} = 0 and {2} = {3} and {4} = '{5}' and {6} = {7}) " + " BEGIN " + insertintotemp5_3truecase + " END " + " else " + " BEGIN " + insertintotemp5_3falsecase + " END ", FieldName.TABLE_NAME, FieldName.SUB_INDICATOR_NUM, FieldName.INDICATOR_NUM, indicator_num, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year); string selectcmd = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.SUB_INDICATOR_NUM); string selectselfscorecmd = string.Format("select {0},{1} " + "from {2} " + "where {3} = '{4}' and {5} = {6} and {7} = {8} ", Self_evaluation.FieldName.SUB_INDICATOR_NUM, Self_evaluation.FieldName.EVALUATION_SCORE, Self_evaluation.FieldName.TABLE_NAME, Self_evaluation.FieldName.CURRI_ID, curri_id, Self_evaluation.FieldName.ACA_YEAR, aca_year, Self_evaluation.FieldName.INDICATOR_NUM, indicator_num); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp5, insertintotemp5_1, insertintotemp5_2, insertintotemp5_3, selectcmd, selectselfscorecmd); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); if (data.Columns.Count > 2) { foreach (DataRow item in data.Rows) { if (Convert.ToInt32(item.ItemArray[data.Columns[FieldName.OTHERS_EVALUATION_ID].Ordinal]) != 0) { string h, m; DateTime timeofday = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.TIME].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture); h = timeofday.Hour.ToString(); m = timeofday.Minute.ToString(); result.evaluation_detail.Add(new Others_evaluation_sub_indicator_name { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), others_evaluation_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.OTHERS_EVALUATION_ID].Ordinal]), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), assessor_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ASSESSOR_ID].Ordinal]), t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(), date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], time = (timeofday.Hour > 9 ? "" : "0") + h + '.' + (timeofday.Minute > 9 ? "" : "0") + m, strength = item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString(), improve = item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString(), evaluation_score = item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal]) : 0, indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]), sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]), sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString() }); if (item.ItemArray[data.Columns[Evidence.FieldName.FILE_NAME].Ordinal].ToString() != "") { result.file_name = item.ItemArray[data.Columns[Evidence.FieldName.FILE_NAME].Ordinal].ToString(); } } else { result.evaluation_detail.Add(new Others_evaluation_sub_indicator_name { curri_id = this.curri_id, others_evaluation_id = 0, aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), assessor_id = 31, date = "", time = "", strength = "", improve = "", evaluation_score = 0, indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]), sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]), sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString() }); } } } else { foreach (DataRow item in data.Rows) { sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.SUB_INDICATOR_NUM].Ordinal]); result.evaluation_detail.First(t => t.sub_indicator_num == sub_indicator_num).self_score = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal]); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
public async Task <object> SelectByTitle() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } Default_privilege_by_type_with_privilege_choices result = new Default_privilege_by_type_with_privilege_choices(); string selectTitle = string.Format("select * from {0} where {1} = {2} ", Title.FieldName.TABLE_NAME, Title.FieldName.TITLE_CODE, title_code); string selectPrivilegeChoices = string.Format("select * from {0} where {1} = {2} ", Title_privilege.FieldName.TABLE_NAME, Title_privilege.FieldName.TITLE_CODE, title_code); string selectMainData = string.Format("select {0}.*,{1} " + "from {0},{2} " + "where {3} = {4} " + "and {0}.{5} = {2}.{6} ", FieldName.TABLE_NAME, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME, FieldName.TITLE_CODE, title_code, FieldName.USER_TYPE_ID, User_type.FieldName.USER_TYPE_ID); string selectSetDefaultData = string.Format("select {0}, {1},1 as {2},{3} " + "from {4},{5} " + "where {1} = {6} and {0} != 7 " + "and not exists(select * from {7} " + "where {7}.{8} = {5}.{1} " + "and {7}.{9} = {4}.{0}) ", User_type.FieldName.USER_TYPE_ID, Title.FieldName.TITLE_CODE, Title_privilege.FieldName.TITLE_PRIVILEGE_CODE, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME, Title.FieldName.TABLE_NAME, title_code, FieldName.TABLE_NAME, FieldName.TITLE_CODE, FieldName.USER_TYPE_ID); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} END", selectTitle, selectPrivilegeChoices, selectMainData, selectSetDefaultData); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable tabledata = new DataTable(); tabledata.Load(res); foreach (DataRow item in tabledata.Rows) { if (tabledata.Columns.Contains(Title.FieldName.NAME)) { //Set title name from title table result result.title_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title.FieldName.TITLE_CODE].Ordinal]); result.name = item.ItemArray[tabledata.Columns[Title.FieldName.NAME].Ordinal].ToString(); } else if (tabledata.Columns.Contains(Title_privilege.FieldName.PRIVILEGE)) { //Set privilege choice for target title result.choices.Add(new Privilege_choice { title_privilege_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]), privilege = item.ItemArray[tabledata.Columns[Title_privilege.FieldName.PRIVILEGE].Ordinal].ToString() }); } else { //Read main privilege data int title_priv_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.TITLE_PRIVILEGE_CODE].Ordinal]); result.privilege_list.Add(new User_type_privilege { user_type_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.USER_TYPE_ID].Ordinal]), user_type = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(), privilege = new Privilege_choice { title_privilege_code = title_priv_code, //Find privilege caption from choices array privilege = result.choices.First(t => t.title_privilege_code == title_priv_code).privilege } }); } } tabledata.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
public void SerializeLargeDataset(HttpContext context) { string strSQL = @" SELECT TOP 10 * FROM T_Benutzer; SELECT TOP 10 * FROM T_Benutzergruppen; -- SELECT * FROM T_Benutzer LIMIT 10; -- SELECT * FROM T_Benutzergruppen LIMIT 10; -- SELECT * FROM T_Benutzer OFFSET 0 FETCH NEXT 10 ROWS ONLY; -- SELECT * FROM T_Benutzergruppen OFFSET 0 FETCH NEXT 10 ROWS ONLY; "; Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer(); using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.Response.Output)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("Tables"); jsonWriter.WriteStartArray(); using (System.Data.Common.DbConnection con = SQL.CreateConnection()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = strSQL; using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection )) { do { jsonWriter.WriteStartObject(); // tbl = new Table(); jsonWriter.WritePropertyName("Columns"); jsonWriter.WriteStartArray(); for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("ColumnName"); jsonWriter.WriteValue(dr.GetName(i)); jsonWriter.WritePropertyName("FieldType"); jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i))); jsonWriter.WriteEndObject(); } // Next i jsonWriter.WriteEndArray(); jsonWriter.WritePropertyName("Rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { while (dr.Read()) { object[] thisRow = new object[dr.FieldCount]; jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteValue(dr.GetValue(i)); } // Next i jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow); } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl); } while (dr.NextResult()); } // End using dr } // End using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End using con jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); jsonWriter.Flush(); } // End Using jsonWriter context.Response.Output.Flush(); context.Response.OutputStream.Flush(); context.Response.Flush(); } // End Sub SerializeLargeDataset
public async Task <object> SelectByCurriculumAcademic() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Lab_list_detail> result = new List <Lab_list_detail>(); d.iCommand.CommandText = getSelectByCurriculumAcademicCommand(); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { if (data.Columns.Contains(FieldName.ROOM)) { result.Add(new Lab_list_detail { lab_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), room = item.ItemArray[data.Columns[FieldName.ROOM].Ordinal].ToString() }); } else { int labid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]); result.First(t => t.lab_num == labid).officer.Add(new Personnel_with_t_name { user_id = Convert.ToInt32(item.ItemArray[data.Columns[Lab_officer.FieldName.OFFICER].Ordinal]), t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString() }); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
public async Task <object> getSectionSaveDataForSAR() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } SAR result = new SAR(); string selectindicator = string.Format("select {0}, {1}, {2} " + "from {3} " + "where {4} = (select max({4}) from {3} where {4} <= {5}) order by {0} ", Indicator.FieldName.INDICATOR_NUM, Indicator.FieldName.INDICATOR_NAME_T, Indicator.FieldName.INDICATOR_NAME_E, Indicator.FieldName.TABLE_NAME, Indicator.FieldName.ACA_YEAR, aca_year); string selectsubindicator = string.Format("select {0},{1},{2} " + "from {3} " + "where {4} = (select max({4}) from {3} where {4} <= {5}) order by {0},{1} ", Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NAME, Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.ACA_YEAR, aca_year); string selectsectionsave = string.Format("select {0},{1},{2},{3},{4},{5} " + "from {6} " + "where {7} = '{8}' and {9} = {10} order by {0},{1} ", FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, FieldName.DETAIL, FieldName.STRENGTH, FieldName.IMPROVE, FieldName.WEAKNESS, FieldName.TABLE_NAME, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year); string selectevidence = string.Format("select {0},{1},{2} " + "from {3} " + "where {4} = '{5}' and {6} = {7} order by {0},{1} ", Evidence.FieldName.INDICATOR_NUM, Evidence.FieldName.EVIDENCE_REAL_CODE, Evidence.FieldName.EVIDENCE_NAME, Evidence.FieldName.TABLE_NAME, Evidence.FieldName.CURRI_ID, curri_id, Evidence.FieldName.ACA_YEAR, aca_year); string selectselfevaluation = string.Format("select {0},{1},{2} " + "from {3} " + "where {4} = '{5}' and {6} = {7} order by {0},{1} ", Self_evaluation.FieldName.INDICATOR_NUM, Self_evaluation.FieldName.SUB_INDICATOR_NUM, Self_evaluation.FieldName.EVALUATION_SCORE, Self_evaluation.FieldName.TABLE_NAME, Self_evaluation.FieldName.CURRI_ID, curri_id, Self_evaluation.FieldName.ACA_YEAR, aca_year); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} END", selectindicator, selectsubindicator, selectsectionsave, selectevidence, selectselfevaluation); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); //Case current resultset is indicator table if (data.Columns.Contains("indicator_name_t")) { foreach (DataRow item in data.Rows) { string indicator_namet = item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NAME_T].Ordinal].ToString(); //Use thai indicator name if it exists if (indicator_namet != "") { result.indicator_section_save_list.Add(new Indicator_with_section_save_list { indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal]), indicator_name = item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NAME_T].Ordinal].ToString() }); } //Otherwise use engish normally else { result.indicator_section_save_list.Add(new Indicator_with_section_save_list { indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal]), indicator_name = item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NAME_E].Ordinal].ToString() }); } result.indicator_self_evaluation_list.Add(new Indicator_with_self_evaluation_tiny_obj_list { indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal]) }); //Force to add self_evaluation with sub_indicator_num = 0 (overall result for each indicator) result.indicator_self_evaluation_list.Last().self_evaluation_list.Add(new Self_evaluation_tiny_detail { sub_indicator_num = 0, evaluation_score = 0 //Default score }); } } //Case current resultset is sub_indicator table else if (data.Columns.Contains("sub_indicator_name")) { foreach (DataRow item in data.Rows) { int indnum = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.INDICATOR_NUM].Ordinal]); result.indicator_section_save_list.First(t => t.indicator_num == indnum).section_save_list.Add(new Section_save_with_sub_indicator_detail { detail = "--ไม่พบข้อมูล--", strength = "--ไม่พบข้อมูล--", weakness = "--ไม่พบข้อมูล--", improve = "--ไม่พบข้อมูล--", sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]), indicator_num = indnum, sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString() }); //Force to add self_evaluation with sub_indicator_num equal to => current read value result.indicator_self_evaluation_list.First(t => t.indicator_num == indnum).self_evaluation_list.Add(new Self_evaluation_tiny_detail { sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]), evaluation_score = 0 //Default score }); } } //Case current resultset is section_save table else if (data.Columns.Contains("detail")) { foreach (DataRow item in data.Rows) { int indnum = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]); int subindnum = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]); Section_save_with_sub_indicator_detail target = result.indicator_section_save_list.First(t => t.indicator_num == indnum). section_save_list.First(u => u.sub_indicator_num == subindnum); string readdetail = item.ItemArray[data.Columns[FieldName.DETAIL].Ordinal].ToString(); if (readdetail != "") { target.detail = readdetail; } string readstrength = item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString(); if (readstrength != "") { target.strength = readstrength; } string readweak = item.ItemArray[data.Columns[FieldName.WEAKNESS].Ordinal].ToString(); if (readweak != "") { target.weakness = readweak; } string readimprove = item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString(); if (readimprove != "") { target.improve = readimprove; } } } //Case current resultset is evidence table else if (data.Columns.Contains(Evidence.FieldName.EVIDENCE_NAME)) { foreach (DataRow item in data.Rows) { int indnum = Convert.ToInt32(item.ItemArray[data.Columns[Evidence.FieldName.INDICATOR_NUM].Ordinal]); result.indicator_section_save_list.First(t => t.indicator_num == indnum).evidence_list.Add(new Evidence_detail_for_SAR { indicator_num = indnum.ToString(), evidence_real_code = item.ItemArray[data.Columns[Evidence.FieldName.EVIDENCE_REAL_CODE].Ordinal].ToString(), evidence_name = item.ItemArray[data.Columns[Evidence.FieldName.EVIDENCE_NAME].Ordinal].ToString() }); } } //Case current resultset is self_evaluation else { foreach (DataRow item in data.Rows) { int indnum = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.INDICATOR_NUM].Ordinal]); int subindnum = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.SUB_INDICATOR_NUM].Ordinal]); result.indicator_self_evaluation_list.First(t => t.indicator_num == indnum). self_evaluation_list.First(u => u.sub_indicator_num == subindnum). evaluation_score = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal]); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
public static void Test() { System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder(); csb.DataSource = System.Environment.MachineName; csb.DataSource = @"10.1.1.8"; // Must be IP, NETBIOS doesn't resolve on Linux // SQL Server Configuration Manager // SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc // SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc // SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc // SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc // in Network-Configuration: Activate TCP/IP & Restart Service // Open firewall port for SQL-Server // - Windows 10: // netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433 // - Windows < 10: // netsh firewall set portopening TCP 1433 "SQLServer" // https://support.microsoft.com/en-us/kb/968872 // https://blog.brankovucinec.com/2015/12/04/scripts-to-open-windows-firewall-ports-for-sql-server/ csb.IntegratedSecurity = false; if (!csb.IntegratedSecurity) { csb.UserID = "LoggyWebServices"; csb.Password = "******"; } csb.InitialCatalog = "TestDB"; Loggy.cDAL DAL = Loggy.cDAL.CreateInstance(); DAL.ConnectionString = csb.ConnectionString; using (System.Data.Common.DbConnection dbConnection = DAL.GetConnection()) { object objUser1 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_User;", dbConnection); object objUser2 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_ID;", dbConnection); DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection); DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection); #if WITH_CONNECTION //using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", dbConnection)) using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader_Buggy("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;")) #else DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", delegate(System.Data.Common.DbDataReader reader) #endif { do { for (int i = 0; i < reader.FieldCount; ++i) { string fieldName = reader.GetName(i); System.Type fieldType = reader.GetFieldType(i); System.Console.WriteLine("{0}:\t{1}\t{2}", i, fieldName, fieldType.ToString()); } // Next i if (reader.HasRows) { int rowCount = 1; while (reader.Read()) { System.Console.WriteLine(@"Row {0}", rowCount); for (int i = 0; i < reader.FieldCount; ++i) { string fieldName = reader.GetName(i); object fieldValue = reader.GetValue(i); System.Console.WriteLine(@" - {0}: {1}", fieldName, System.Convert.ToString(fieldValue)); } // Next i ++rowCount; } // Whend --rowCount; } // End if (reader.HasRows) } while (reader.NextResult()); } // End Using reader #if !WITH_CONNECTION ); #endif object objUser3 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Hash;", dbConnection); object objUser4 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Passwort;", dbConnection); } // End Using dbConnection } // End Sub Test
} // End Sub WriteArray public static void AnyDataReaderToAnyJson( string sql , SqlService service , System.Collections.Generic.Dictionary <string, object> pars , System.Web.HttpContext context , RenderType_t format) { using (System.Data.Common.DbConnection con = service.Connection) { using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = sql; service.AddParameterList(pars, cmd); // cmd.ExecuteNonQuery // cmd.ExecuteReader // cmd.ExecuteScalar using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader( System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection)) { using (System.IO.StreamWriter output = new System.IO.StreamWriter(context.Response.OutputStream)) { using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output)) // context.Response.Output) { if (format.HasFlag(RenderType_t.Indented)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; } context.Response.StatusCode = (int)System.Net.HttpStatusCode.OK; context.Response.ContentType = "application/json"; jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("tables"); jsonWriter.WriteStartArray(); do { if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("columns"); if (format.HasFlag(RenderType_t.Columns_Associative)) { WriteAssociativeColumnsArray(jsonWriter, dr, format); } else if (format.HasFlag(RenderType_t.Columns_ObjectArray)) { WriteComplexArray(jsonWriter, dr, format); } else // (format.HasFlag(RenderType_t.Array)) { WriteArray(jsonWriter, dr); } } // End if (!format.HasFlag(RenderType_t.Data_Only)) if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WritePropertyName("rows"); } // End if (!format.HasFlag(RenderType_t.Data_Only)) jsonWriter.WriteStartArray(); if (dr.HasRows) { string[] columns = null; if (format.HasFlag(RenderType_t.DataTable)) { columns = new string[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; i++) { columns[i] = dr.GetName(i); } // Next i } // End if (format.HasFlag(RenderType_t.DataTable)) while (dr.Read()) { if (format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WriteStartObject(); } else { jsonWriter.WriteStartArray(); } for (int i = 0; i <= dr.FieldCount - 1; i++) { object obj = dr.GetValue(i); if (obj == System.DBNull.Value) { obj = null; } if (columns != null && format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WritePropertyName(columns[i]); } jsonWriter.WriteValue(obj); } // Next i if (format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WriteEndObject(); } else { jsonWriter.WriteEndArray(); } } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); if (!format.HasFlag(RenderType_t.Data_Only) && !format.HasFlag(RenderType_t.DataTable)) { jsonWriter.WriteEndObject(); } // End if (!format.HasFlag(RenderType_t.Data_Only)) } while (dr.NextResult()); jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); jsonWriter.Flush(); output.Flush(); } // jsonWriter } // output } // dr } // End Using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // con } // End Sub WriteArray
public async Task <object> selectWithFullDetail(string curri_id_data) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } List <Personnel_educational> result = new List <Personnel_educational>(); string selpersonnel = string.Format("select {0}, {1}.{2}," + "{3} = {4} " + ", {5}, {6}, {7}, {8}, {9} " + "from {1}, {10} " + "where {1}.{2} in(1,2) " + "and exists (select * from {11} " + "where {1}.{0} = {12} and {13} = '{14}') " + "and {1}.{2} = {10}.{15} ", User_list.FieldName.USER_ID, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, User_list.FieldName.EMAIL, User_list.FieldName.TEL, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME, User_curriculum.FieldName.TABLE_NAME, User_curriculum.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, curri_id_data, User_type.FieldName.USER_TYPE_ID ); string seleducation = string.Format("select * from {0} " + "where " + "exists (select * from {1} " + "where {2} = {3} and {4} = '{5}') " + "and exists (select * from {6} " + "where {2} = {7} and {8} in(1, 2)) ", Educational_teacher_staff.FieldName.TABLE_NAME, User_curriculum.FieldName.TABLE_NAME, Educational_teacher_staff.FieldName.PERSONNEL_ID, User_curriculum.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, curri_id_data, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, User_list.FieldName.USER_TYPE_ID ); d.iCommand.CommandText = string.Format("BEGIN {0} {1} END", selpersonnel, seleducation); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { if (data.Columns.Contains(Educational_teacher_staff.FieldName.EDUCATION_ID)) {//Educational table data int personnelid = Convert.ToInt32(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.PERSONNEL_ID].Ordinal]); result.First(p => p.user_id == personnelid).history.Add(new Educational_teacher_staff { college = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.COLLEGE].Ordinal].ToString(), degree = Convert.ToChar(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.DEGREE].Ordinal]), grad_year = Convert.ToInt32(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal]), pre_major = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.PRE_MAJOR].Ordinal].ToString(), major = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.MAJOR].Ordinal].ToString(), personnel_id = personnelid }); } else { result.Add(new Personnel_educational { //Main user table data user_id = Convert.ToInt32(item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal]), email = item.ItemArray[data.Columns[User_list.FieldName.EMAIL].Ordinal].ToString(), file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()), tel = item.ItemArray[data.Columns[User_list.FieldName.TEL].Ordinal].ToString(), user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(), fullname = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString() }); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); res.Close(); } catch (Exception ex) { //Handle error from sql execution return(ex.Message); } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return(result); }
} // End Sub MultipleLargeDataSets public static void MultipleLargeDataSets(System.IO.Stream strm, string strSQL) { Newtonsoft.Json.JsonSerializer serializer = new Newtonsoft.Json.JsonSerializer(); using (System.IO.StreamWriter output = new System.IO.StreamWriter(strm)) { using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("Tables"); jsonWriter.WriteStartArray(); using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL , System.Data.CommandBehavior.CloseConnection | System.Data.CommandBehavior.SequentialAccess )) { do { jsonWriter.WriteStartObject(); // tbl = new Table(); jsonWriter.WritePropertyName("Columns"); jsonWriter.WriteStartArray(); for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("ColumnName"); jsonWriter.WriteValue(dr.GetName(i)); jsonWriter.WritePropertyName("FieldType"); jsonWriter.WriteValue(dr.GetFieldType(i).AssemblyQualifiedName); jsonWriter.WriteEndObject(); } // Next i jsonWriter.WriteEndArray(); jsonWriter.WritePropertyName("Rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { while (dr.Read()) { object[] thisRow = new object[dr.FieldCount]; jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteValue(dr.GetValue(i)); } // Next i jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow); } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl); } while (dr.NextResult()); } // End Using dr jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); jsonWriter.Flush(); output.Flush(); output.BaseStream.Flush(); output.Close(); // context.Response.Output.Flush(); // context.Reponse.OutputStream.Flush(); // context.Response.Flush(); } // End Using jsonWriter } // End using output } // End Sub MultipleLargeDataSets