static void Main(string[] args)
{
// App.config stores configuration data
// System.Data.SqlClient provides classes
// for accessing a SQL Server DB
// connectionString defines the DB name, and
// other parameters for connecting to the DB
// Configurationmanager provides access to
// config data in App.config
string provider = ConfigurationManager.AppSettings["provider"];
string connectionString = ConfigurationManager.AppSettings["connectionString"];
Console.WriteLine(connectionString);
// DbProviderFactories generates an
// instance of a DbProviderFactory
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
// The DBConnection represents the DB connection
using (DbConnection connection =
factory.CreateConnection())
{
// Check if a connection was made
if (connection == null)
{
Console.WriteLine("Connection Error");
Console.ReadLine();
return;
}
// The DB data needed to open the correct DB
connection.ConnectionString = connectionString;
// Open the DB connection
connection.Open();
// Allows you to pass queries to the DB
DbCommand command = factory.CreateCommand();
if (command == null)
{
Console.WriteLine("Command Error");
Console.ReadLine();
return;
}
// Set the DB connection for commands
command.Connection = connection;
// The query you want to issue
command.CommandText = "Select * From Products";
// DbDataReader reads the row results
// from the query
using (DbDataReader dataReader = command.ExecuteReader())
{
// Advance to the next results
while (dataReader.Read())
{
// Output results using row names
Console.WriteLine($"{dataReader["PID"]} " +
$"{dataReader["FULLNAME"]}");
}
}
Console.ReadLine();
}
}
public DbDataReader GetDBResults(ref String errMsg, string StoredProcedure, params object[] ProcedureParameters)
{
int i = 0;
string SQLStatement = "";
string DataProviderName = "System.Data.SqlClient";
DbProviderFactory dpf = DbProviderFactories.GetFactory(DataProviderName);
DbConnection Conn = dpf.CreateConnection();
Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MainDbConnection"].ToString();
try
{
Conn.Open();
}
catch (System.InvalidOperationException ex)
{
errMsg = ex.Message.ToString();
DbDataReader rs = null;
return(rs);
}
catch (System.Data.SqlClient.SqlException ex)
{
errMsg = ex.Message.ToString();
DbDataReader rs = null;
return(rs);
}
catch (System.Exception ex)
{
errMsg = ex.Message.ToString();
DbDataReader rs = null;
return(rs);
}
DbCommand Command = dpf.CreateCommand();
Command.Connection = Conn;
Command.CommandTimeout = 120;
//SqlTransaction transaction = new SqlTransaction()
//transaction.Connection=Conn;
//transaction.Commit
foreach (object o in ProcedureParameters)
{
if (i % 2 == 0)
{
SQLStatement = SQLStatement + (string)o + "=";
}
else
{
if (o.GetType() == typeof(string))
{
SQLStatement = SQLStatement + " '" + (string)o + "',";
}
else if (o.GetType() == typeof(DateTime))
{
SQLStatement = SQLStatement + " '" + o.ToString() + "',";
}
else if (o.GetType() == typeof(Int32))
{
SQLStatement = SQLStatement + o.ToString() + ",";
}
else if (o.GetType() == typeof(Double))
{
SQLStatement = SQLStatement + o.ToString() + ",";
}
else if (o.GetType() == typeof(Decimal))
{
SQLStatement = SQLStatement + o.ToString() + ",";
}
else if (o.GetType() == typeof(Int32))
{
SQLStatement = SQLStatement + o.ToString() + ",";
}
else if (o.GetType() == typeof(Boolean))
{
if ((bool)o == true)
{
SQLStatement = SQLStatement + " 1" + ",";
}
else
{
SQLStatement = SQLStatement + " 0" + ",";
}
}
else
{
SQLStatement = SQLStatement + " '" + (string)o + "',";
}
}
i = i + 1;
}
if (SQLStatement.Length > 1)
{
SQLStatement = SQLStatement.Substring(0, SQLStatement.Length - 1);
}
StoredProcedure = "EXEC " + StoredProcedure + " " + SQLStatement;
Command.CommandType = CommandType.Text;
Command.CommandText = StoredProcedure;
DbDataReader ResultSet = null;
try
{
ResultSet = Command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (System.Data.SqlClient.SqlException ex)
{
errMsg = ex.Message.ToString();
DbDataReader rs = null;
return(rs);
}
return(ResultSet);
}
protected override bool LoadObjects()
{
DbConnection cnn = null;
DbCommand cmd = null;
DbDataReader rdr = null;
try
{
DbProviderFactory factory =
DbProviderFactories.GetFactory(
connection.Provider.InvariantName);
cnn = factory.CreateConnection();
cnn.ConnectionString = connection.ConnectionString;
cnn.Open();
database = cnn.Database;
cmd = factory.CreateCommand();
cmd.Connection = cnn;
cmd.CommandText =
"SELECT C.TABLE_SCHEMA, C.TABLE_NAME, T.TABLE_TYPE, " +
"C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH " +
"FROM INFORMATION_SCHEMA.COLUMNS C INNER JOIN " +
"INFORMATION_SCHEMA.TABLES T ON " +
"T.TABLE_SCHEMA = C.TABLE_SCHEMA AND " +
"T.TABLE_NAME = C.TABLE_NAME ";
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
/*
* Need to make keys lowercase for case-insensitive
* matching but retain case for object properties.
*/
string schemaName = rdr.GetString(0);
string schemaNameNC = schemaName.ToLower();
schemaNameNC = schemaNameNC.Replace(' ', '~');
string tableName = rdr.GetString(1);
string tableNameNC = tableName.ToLower();
tableNameNC = tableNameNC.Replace(' ', '~');
bool isView = (rdr.GetString(2).EndsWith("VIEW"));
string columnName = rdr.GetString(3);
string columnType = rdr.GetString(4);
long columnSize = (rdr.IsDBNull(5) ? -1 : rdr.GetInt64(5));
string columnTypeName = GetColumnTypeName(
columnType, columnSize);
if (!schemata.ContainsKey(schemaNameNC))
{
schemata[schemaNameNC] = new Schema(schemaName);
}
if (!schemata[schemaNameNC].Tables.ContainsKey(tableNameNC))
{
schemata[schemaNameNC].Tables[tableNameNC] =
new Table(tableName, schemaName, isView);
}
schemata[schemaNameNC].Tables[tableNameNC].Columns.Add(
new TableColumn(columnName, columnTypeName));
}
return(true);
}
catch
{
return(false);
}
finally
{
if (rdr != null)
{
rdr.Dispose();
}
if (cnn != null)
{
cnn.Dispose();
}
}
}
/// <summary>
/// Builds the select query
/// </summary>
/// <returns>Returns a string containing the query, or a DbCommand containing a command with parameters</returns>
private object BuildQuery(bool buildCommand)
{
if (buildCommand && _dbProviderFactory == null)
{
throw new Exception("Cannot build a command when the Db Factory hasn't been specified. Call SetDbProviderFactory first.");
}
DbCommand command = null;
if (buildCommand)
{
command = _dbProviderFactory.CreateCommand();
}
string Query = "SELECT ";
// Output Distinct
if (_distinct)
{
Query += "DISTINCT ";
}
// Output Top clause
if (!(_topClause.Quantity == 100 & _topClause.Unit == TopUnit.Percent))
{
Query += "TOP " + _topClause.Quantity;
if (_topClause.Unit == TopUnit.Percent)
{
Query += " PERCENT";
}
Query += " ";
}
//newLine
Query += _newLine;
// Output column names
if (_selectedColumns.Count == 0)
{
Query += "*";
}
else
{
foreach (string ColumnName in _selectedColumns)
{
Query += ColumnName + ',';
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output table names
if (_selectedTables.Count > 0)
{
//newLine
Query += _newLine;
Query += " FROM ";
foreach (string TableName in _selectedTables)
{
Query += TableName + ',';
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
//newLine
Query += _newLine;
// Output joins
if (_joins.Count > 0)
{
foreach (JoinClause Clause in _joins)
{
string JoinString = "";
switch (Clause.JoinType)
{
case JoinType.InnerJoin: JoinString = "INNER JOIN"; break;
case JoinType.OuterJoin: JoinString = "OUTER JOIN"; break;
case JoinType.LeftJoin: JoinString = "LEFT JOIN"; break;
case JoinType.RightJoin: JoinString = "RIGHT JOIN"; break;
}
JoinString += " " + Clause.ToTable + " ON ";
JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable + '.' + Clause.FromColumn, Clause.ComparisonOperator, new SqlLiteral(Clause.ToTable + '.' + Clause.ToColumn));
Query += JoinString + ' ';
}
}
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
if (buildCommand)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement(true, ref command);
}
else
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
}
// Output GroupBy statement
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (string Column in _groupByColumns)
{
Query += Column + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
// Output having statement
if (_havingStatement.ClauseLevels > 0)
{
// Check if a Group By Clause was set
if (_groupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
if (buildCommand)
{
Query += " HAVING " + _havingStatement.BuildWhereStatement(true, ref command);
}
else
{
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
}
// Output OrderBy statement
if (_orderByStatement.Count > 0)
{
Query += " ORDER BY ";
foreach (OrderByClause Clause in _orderByStatement)
{
string OrderByClause = "";
switch (Clause.SortOrder)
{
case Sorting.Ascending:
OrderByClause = Clause.FieldName + " ASC"; break;
case Sorting.Descending:
OrderByClause = Clause.FieldName + " DESC"; break;
}
Query += OrderByClause + ',';
}
Query = Query.TrimEnd(','); // Trim de last AND inserted by foreach loop
Query += ' ';
}
if (buildCommand)
{
// Return the build command
command.CommandText = Query;
return(command);
}
else
{
// Return the built query
return(Query);
}
}
public override DbCommand CreateCommand()
{
DbCommand core = m_inner.CreateCommand();
return(new GlazeCommand(m_databaseProvider, core));
}
public override void OnIndexEntryCreated(string entryKey, Document document)
{
var cmd = _providerFactory.CreateCommand();
var pkParam = cmd.CreateParameter();
pkParam.ParameterName = GetParameterName("entryKey");
pkParam.Value = entryKey;
cmd.Parameters.Add(pkParam);
var sb = new StringBuilder("INSERT INTO ")
.Append(destination.TableName)
.Append(" (")
.Append(destination.PrimaryKeyColumnName)
.Append(", ");
foreach (var mapping in destination.ColumnsMapping)
{
sb.Append(mapping.Value).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(") \r\nVALUES (")
.Append(pkParam.ParameterName)
.Append(", ");
foreach (var mapping in destination.ColumnsMapping)
{
var parameter = cmd.CreateParameter();
parameter.ParameterName = GetParameterName(mapping.Key);
var field = document.GetFieldable(mapping.Key);
var numericfield = document.GetFieldable(String.Concat(mapping.Key, "_Range"));
if (numericfield != null)
{
field = numericfield;
}
if (field == null || field.StringValue == Constants.NullValue)
{
parameter.Value = DBNull.Value;
}
else if (field.StringValue == Constants.EmptyString)
{
parameter.Value = "";
}
else if (field.StringValue.Equals("False", StringComparison.InvariantCultureIgnoreCase))
{
parameter.Value = false;
}
else if (field.StringValue.Equals("True", StringComparison.InvariantCultureIgnoreCase))
{
parameter.Value = true;
}
else if (field is NumericField)
{
var numField = (NumericField)field;
parameter.Value = numField.NumericValue;
}
else
{
var stringValue = field.StringValue;
if (datePattern.IsMatch(stringValue))
{
try
{
parameter.Value = DateTools.StringToDate(stringValue);
}
catch
{
parameter.Value = stringValue;
}
}
else
{
DateTime time;
if (DateTime.TryParseExact(stringValue, Default.DateTimeFormatsToRead, CultureInfo.InvariantCulture,
DateTimeStyles.None, out time))
{
parameter.Value = time;
}
else
{
parameter.Value = stringValue;
}
}
}
cmd.Parameters.Add(parameter);
sb.Append(parameter.ParameterName).Append(", ");
}
sb.Length = sb.Length - 2;
sb.Append(")");
cmd.CommandText = sb.ToString();
commands.Enqueue(cmd);
}
/// <summary>
/// Executes Update statements in the database.
/// </summary>
/// <param name="sql">Sql statement.</param>
/// <returns>Number of rows affected.</returns>
public static int Update(string sql)
{
using (DbConnection connection = dbProviderFactory.CreateConnection())
{
connection.ConnectionString = ConStr;//Properties.Settings.Default.ConnectionString;
using (DbCommand command = dbProviderFactory.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
connection.Open();
int result = command.ExecuteNonQuery();
connection.Close();
return(result);
}
}
}
/// <summary>
/// create the command.
/// </summary>
/// <returns>The <see cref="DbCommand"/>.</returns>
public override DbCommand CreateCommand()
{
return(new ProfiledDbCommand(_tail.CreateCommand(), null, _profiler));
}
static void Main(string[] args)
{
Console.WriteLine("Web API Call and Storing JSON into SQL Database.");
Console.WriteLine("Calling : https://jsonplaceholder.typicode.com/posts");
// Establish Database Connection.
string provider = ConfigurationManager.AppSettings["provider"];
string connectionString = ConfigurationManager.AppSettings["connectionString"];
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
using (DbConnection connection = factory.CreateConnection())
{
if (connection == null)
{
Console.WriteLine("Connection Error");
Console.ReadLine();
return;
}
connection.ConnectionString = connectionString;
connection.Open();
DbCommand command = factory.CreateCommand();
if (command == null)
{
Console.WriteLine("Command Error");
Console.ReadLine();
return;
}
command.Connection = connection;
// Calling Api
string strurltest = String.Format("https://jsonplaceholder.typicode.com/posts");
WebRequest requestObjGet = WebRequest.Create(strurltest);
requestObjGet.Method = "GET";
HttpWebResponse responseObjGet = null;
responseObjGet = (HttpWebResponse)requestObjGet.GetResponse();
string apistrresult = null;
using (Stream stream = responseObjGet.GetResponseStream())
{
StreamReader sr = new StreamReader(stream);
apistrresult = sr.ReadToEnd();
// Parsing the JSON payload
var jsondata = JsonConvert.DeserializeObject <dynamic>(apistrresult);
Console.WriteLine("Total Json obj's: " + jsondata.Count);
// Storing that data in a SQL database
for (int i = 0; i < jsondata.Count; i++)
{
command.CommandText = "INSERT INTO apiData(userId, title, body) VALUES ('"
+ jsondata[i].userId + "', '" + jsondata[i].title + "', '" + jsondata[i].body + "')";
command.ExecuteNonQuery();
}
Console.WriteLine("Data Stored in SQL Database.");
//Console.WriteLine(jsondata[1].userId);
//Console.WriteLine(jsondata[1].id);
//Console.WriteLine(jsondata[1].title);
//Console.WriteLine(jsondata[1].body);
sr.Close();
}
connection.Close();
Console.ReadKey();
}
}
public override DbCommand CreateCommand()
{
return(new WrappingCommand <TBase>(_baseProviderFactory.CreateCommand()));
}
static void Main(string[] args)
{
#region get config info
string dataProvider = ConfigurationManager.AppSettings["provider"];
WriteLine("\tprovider: {0}", dataProvider);
// alternate see p. 833
var cnStringBuilder = new SqlConnectionStringBuilder
{
InitialCatalog = "Northwind",
DataSource = @"(localdb)\mssqllocaldb",
ConnectTimeout = 30,
IntegratedSecurity = true
};
WriteLine($"\tBuilt Connection String: " +
$"{cnStringBuilder.ConnectionString}\n");
#endregion
// get factory
DbProviderFactory factory =
DbProviderFactories.GetFactory(dataProvider);
using (DbConnection connection = factory.CreateConnection())
{
if (connection == null)
{
WriteLine("There was an issue creating the connection");
return;
}
else
{
WriteLine("-> Connection created");
}
connection.ConnectionString = cnStringBuilder.ConnectionString;
connection.Open();
DbCommand myCommand = factory.CreateCommand();
if (myCommand == null)
{
WriteLine("There is an issue creating the command");
return;
}
else
{
WriteLine($"Your command object is a " +
$"{myCommand.GetType().Name}");
}
myCommand.Connection = connection;
myCommand.CommandText = "Select * from Shippers";
using (DbDataReader dataReader = myCommand.ExecuteReader())
{
while (dataReader.Read())
{
WriteLine($"-> shipper # {dataReader["ShipperId"]} " +
$"name is a {dataReader[1]} phone: {dataReader[2]}");
}
}
}
}
static void Main(string[] args)
{
string provider = ConfigurationManager.AppSettings["provider"];
string connectionString = ConfigurationManager.AppSettings["connectionString"];
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
using (DbConnection connection = factory.CreateConnection())
{
if (connection == null)
{
Console.WriteLine("Connection Error");
Console.ReadLine();
return;
}
connection.ConnectionString = connectionString;
connection.Open();
DbCommand command = factory.CreateCommand();
if (command == null)
{
Console.WriteLine("Command Error");
Console.ReadLine();
return;
}
command.Connection = connection;
command.CommandText = "Select * From Products";
using (DbDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Console.WriteLine($"{dataReader["ProdId"]} " + $"{dataReader["Product"]}");
}
}
Console.ReadLine();
command.Connection = connection;
command.CommandText = "INSERT INTO [dbo].[Products] ([ProdId], [Product], [Price], [Code]) VALUES (55, N'Pisici ', CAST(3.6800 AS Money), N'PISI ')";
command.ExecuteNonQuery();
Console.ReadLine();
command.Connection = connection;
command.CommandText = "Select * From Products";
using (DbDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Console.WriteLine($"{dataReader["ProdId"]} " + $"{dataReader["Product"]}");
}
}
Console.ReadLine();
command.Connection = connection;
command.CommandText = "Delete Products where ProdId = 55";
command.ExecuteNonQuery();
Console.ReadLine();
command.Connection = connection;
command.CommandText = "Select * From Products";
using (DbDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Console.WriteLine($"{dataReader["ProdId"]} " + $"{dataReader["Product"]}");
}
}
Console.ReadLine();
command.Connection = connection;
command.CommandText = "Update Products set Product='hey', Price='2.050' where ProdId = 50";
command.ExecuteNonQuery();
Console.ReadLine();
command.Connection = connection;
command.CommandText = "Select * From Products";
using (DbDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Console.WriteLine($"{dataReader["ProdId"]} " + $"{dataReader["Product"]}" + $"{dataReader["Price"]}");
}
}
Console.ReadLine();
connection.Close();
}
}
static void Main()
{
string myconnection = ConfigurationManager.AppSettings["connection"];
string myprovider = ConfigurationManager.AppSettings["Provider"];
DbProviderFactory factory = DbProviderFactories.GetFactory(myprovider);
Console.WriteLine("Number? ");
int nr = Convert.ToInt32(Console.ReadLine());
//öppna förbindelse till databasen
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = myconnection;
Console.WriteLine("databasen öppnad...");
string sql = "select namn, rekord from Deltagare where nr = @nr";
using (DbCommand command1 = factory.CreateCommand())
{
command1.Connection = connection;
command1.CommandText = sql;
//command1.Parameters.AddWithValue("@nr", nr);
DbParameter par = factory.CreateParameter(); // för att koda provider oberoende kod
par.ParameterName = "@nr";
par.Value = nr;
command1.Parameters.Add(par);
connection.Open();
DbDataReader reader = command1.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
string name = reader["namn"].ToString();
double bestresult = Convert.ToDouble(reader["rekord"]);
Console.WriteLine("Person with nr " + nr + " is: ");
Console.WriteLine(name + " with a personal best of: " + bestresult);
reader.Close();
command1.CommandText = "select * from Resultat where deltagarnr = @nr";
reader = command1.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
//int resultmbr = Convert.ToInt32(reader[""])
double result = Convert.ToDouble(reader["resultat"]);
Console.WriteLine(result);
}
}
else
{
Console.WriteLine("inga tidigare resultat");
}
reader.Close();
connection.Close();
//////////////////////////////////////////////////////////////////////////////////
///// mata in resultat för personen
///
Console.WriteLine("New result? ");
double newresult = Convert.ToDouble(Console.ReadLine());
// Behöver också 'int nr'!!!
DbTransaction transaction;
command1.CommandText = "select max(resultatnr) from Resultat";
sql = "insert into Resultat (resultatnr, deltagarnr, resultat) values (@resnr, @deltnr, @result)";
//SqlCommand insertcommand = new SqlCommand(sql, connection);
DbCommand insertcommand = factory.CreateCommand(); // för att koda provider oberoende kod
insertcommand.Connection = connection;
insertcommand.CommandText = sql;
// @resnr e tom variabel
//insertcommand.Parameters.Add(new SqlParameter("@resnr", System.Data.SqlDbType.Int));
DbParameter resnrpar = factory.CreateParameter();
resnrpar.ParameterName = "@resnr";
insertcommand.Parameters.Add(resnrpar);
//insertcommand.Parameters.AddWithValue("@deltnr", nr);
DbParameter deltnrpar = factory.CreateParameter();
deltnrpar.ParameterName = "@deltnr";
deltnrpar.Value = nr;
insertcommand.Parameters.Add(deltnrpar);
//insertcommand.Parameters.AddWithValue("@result", newresult);
DbParameter resultpar = factory.CreateParameter();
resultpar.ParameterName = "@result";
resultpar.Value = newresult;
insertcommand.Parameters.Add(resultpar);
sql = "update Deltagare set rekord = @best where nr = @nr";
DbCommand updatecommand = factory.CreateCommand();
updatecommand.Connection = connection;
updatecommand.CommandText = sql;
DbParameter bestpar = factory.CreateParameter();
bestpar.ParameterName = "@best";
bestpar.Value = newresult;
updatecommand.Parameters.Add(bestpar);
DbParameter nrpar = factory.CreateParameter();
nrpar.ParameterName = "@nr";
nrpar.Value = nr;
updatecommand.Parameters.Add(nrpar);
connection.Open();
transaction = connection.BeginTransaction();
try
{
command1.Transaction = transaction;
insertcommand.Transaction = transaction;
updatecommand.Transaction = transaction;
//nästa lediga resultat nummer
int resnr = (int)command1.ExecuteScalar();
resnr++;
command1.CommandText = "select rekord from Deltagare where nr = @nr";
double best = Convert.ToDouble(command1.ExecuteScalar());
insertcommand.Parameters[0].Value = resnr;
insertcommand.ExecuteNonQuery();
//uppdatera rekord
if (newresult > best)
{
updatecommand.ExecuteNonQuery();
}
transaction.Commit(); // COMMIT
Console.WriteLine("Resultatet uppdaterat!");
if (newresult > best)
{
Console.WriteLine("Nytt rekord!");
}
}
catch (Exception e)
{
transaction.Rollback();
Console.WriteLine("FEL -- kunde inte spara");
Console.WriteLine(e.Message);
}
connection.Close();
}
else
{
Console.WriteLine("ingen sådan deltagare");
reader.Close();
connection.Close();
}
}
}
Console.ReadKey();
}
public IList <CWRRegisterDetails> GetAllCWRRegisterDetails()
{
List <CWRRegisterDetails> olist = null;
try
{
DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.SqlClient");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = ConnectionInfo.GetConnectionString;
DataTable table = new DataTable();
using (connection)
{
string queryString = "TS_GetAllCWREmpRegisterDetails";
// Create the DbCommand.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString;
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
// Create the DbDataAdapter.
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
// Fill the DataTable.
adapter.Fill(table);
olist = new List <CWRRegisterDetails>();
foreach (DataRow row in table.Rows)
{
CWRRegisterDetails oCWRRegisterDetails = CreateObject.GetObject <CWRRegisterDetails>();
oCWRRegisterDetails.CWRID = (row["Date"].ToString() ?? string.Empty);
oCWRRegisterDetails.first = NonBlankValueOf(row["1"].ToString());
oCWRRegisterDetails.second = NonBlankValueOf(row["2"].ToString());
oCWRRegisterDetails.third = NonBlankValueOf(row["3"].ToString());
oCWRRegisterDetails.fourth = NonBlankValueOf(row["4"].ToString());
oCWRRegisterDetails.fifth = NonBlankValueOf(row["5"].ToString());
oCWRRegisterDetails.sixth = NonBlankValueOf(row["6"].ToString());
oCWRRegisterDetails.seventh = NonBlankValueOf(row["7"].ToString());
oCWRRegisterDetails.eighth = NonBlankValueOf(row["8"].ToString());
oCWRRegisterDetails.ninth = NonBlankValueOf(row["9"].ToString());
oCWRRegisterDetails.tenth = NonBlankValueOf(row["10"].ToString());
oCWRRegisterDetails.eleventh = NonBlankValueOf(row["11"].ToString());
oCWRRegisterDetails.twelfth = NonBlankValueOf(row["12"].ToString());
oCWRRegisterDetails.thirteenth = NonBlankValueOf(row["13"].ToString());
oCWRRegisterDetails.fourteenth = NonBlankValueOf(row["14"].ToString());
oCWRRegisterDetails.fifteenth = NonBlankValueOf(row["15"].ToString());
oCWRRegisterDetails.sixteenth = NonBlankValueOf(row["16"].ToString());
oCWRRegisterDetails.seventeenth = NonBlankValueOf(row["17"].ToString());
oCWRRegisterDetails.eighteenth = NonBlankValueOf(row["18"].ToString());
oCWRRegisterDetails.nineteenth = NonBlankValueOf(row["19"].ToString());
oCWRRegisterDetails.twentieth = NonBlankValueOf(row["20"].ToString());
oCWRRegisterDetails.twentyfirst = NonBlankValueOf(row["21"].ToString());
oCWRRegisterDetails.twentysecond = NonBlankValueOf(row["22"].ToString());
oCWRRegisterDetails.twentythird = NonBlankValueOf(row["23"].ToString());
oCWRRegisterDetails.twentyfourth = NonBlankValueOf(row["24"].ToString());
oCWRRegisterDetails.twentyfifth = NonBlankValueOf(row["25"].ToString());
oCWRRegisterDetails.twentysixth = NonBlankValueOf(row["26"].ToString());
oCWRRegisterDetails.twentyseventh = NonBlankValueOf(row["27"].ToString());
oCWRRegisterDetails.twentyeighth = NonBlankValueOf(row["28"].ToString());
oCWRRegisterDetails.twentyninth = NonBlankValueOf(row["29"].ToString());
oCWRRegisterDetails.thirtieth = NonBlankValueOf(row["30"].ToString());
oCWRRegisterDetails.thirtyfirst = NonBlankValueOf(row["31"].ToString());
olist.Add(oCWRRegisterDetails);
}
return(olist);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public Page()
{
providerFactory = Utils.DB.CreateDBProviderFactory();
cn = Utils.DB.CreateConnection();
cmd = providerFactory.CreateCommand();
cmd.Connection = cn;
}
public bool DeletedById(int id)
{
_errorCode = 0;
_rowsAffected = 0;
try
{
var query = new StringBuilder();
query.Append("DELETE ");
query.Append("FROM [dbo].[tVehicle] ");
query.Append("WHERE");
query.Append("[vehicleID] = @id ");
query.Append("SELECT @intErrorCode=@@ERROR; ");
var commandText = query.ToString();
query.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentException("dbCommand" + "The db SelectById command for entity [tVehicle] can't be null");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentException("dbCommand" + " The db SelectById command for entity [tCompany] can't be null.");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
DBHelper.AddParameter(dbCommand, "@id", Types.Int, ParameterDirection.Input, id);
DBHelper.AddParameter(dbCommand, "@intErrorCode", Types.Int, ParameterDirection.Output, null);
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
throw new Exception("Deleting Error for entity [tCompany] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
LogHelper.Log(LogTarget.File, ExceptionHelper.ExceptionToString(ex), true);
throw new Exception("CustomersRepository::Insert:Error occured.", ex);
}
}
/// <summary>
/// Calls the specified procedure to find the result type.
/// </summary>
/// <param name="procedure">The procedure.</param>
public void ExecuteProcedure(DatabaseStoredProcedure procedure)
{
var executionName = procedure.Name;
if (!string.IsNullOrEmpty(procedure.Package))
{
executionName = procedure.Package + "." + procedure.Name;
}
//for Oracle, sprocs with REF CURSORs indicate it returns something.
if (_isOracle && !procedure.Arguments.Any(a => a.DatabaseDataType == "REF CURSOR"))
{
return;
}
using (var resultSet = new DataSet {
Locale = CultureInfo.InvariantCulture
})
{
using (DbConnection connection = _factory.CreateConnection())
{
connection.ConnectionString = _schema.ConnectionString;
using (var command = _factory.CreateCommand())
{
command.Connection = connection;
command.CommandText = executionName;
command.CommandTimeout = 5;
command.CommandType = CommandType.StoredProcedure;
AddParameters(procedure, command);
connection.Open();
using (DbTransaction tx = connection.BeginTransaction())
{
command.Transaction = tx;
using (DbDataAdapter adapter = _factory.CreateDataAdapter())
{
adapter.SelectCommand = command;
try
{
adapter.FillSchema(resultSet, SchemaType.Source);
}
catch (DbException exception)
{
//ignore any db exceptions
Debug.WriteLine(executionName + Environment.NewLine
+ exception.Message);
}
catch (Exception exception) //for exceptions that don't derive from DbException
{
//ignore any db exceptions
Debug.WriteLine(executionName + Environment.NewLine
+ exception.Message);
}
}
tx.Rollback();
}
}
}
UpdateProcedure(procedure, resultSet);
}
}
public bool DeletedById(int id)
{
_errorCode = 0;
_rowsAffected = 0;
try
{
var query = new StringBuilder();
query.Append("DELETE ");
query.Append("FROM [dbo].[tbl_IslemTakip] ");
query.Append("WHERE ");
query.Append("[IslemTakipID] = @id ");
query.Append("SELECT @intErrorCode=@@ERROR; ");
var commandText = query.ToString();
query.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException(
"dbCommand" + " The db SelectById command for entity [tbl_IslemTakip] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Parameters
DBHelper.AddParameter(dbCommand, "@id", CsType.Int, ParameterDirection.Input, id);
//Output Parameters
DBHelper.AddParameter(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
throw new Exception(
"Deleting Error for entity [tbl_IslemTakip] reported the Database ErrorCode: " +
_errorCode);
}
}
}
//Return the results of query/ies
return(true);
}
catch (Exception ex)
{
LogHelper.Log(LogTarget.File, ExceptionHelper.ExceptionToString(ex), true);
throw new Exception("IslemTakipRepository::Delete:Error occured.", ex);
}
}
public override void AddUsersToRoles(string [] usernames, string [] roleNames)
{
Hashtable h = new Hashtable();
foreach (string u in usernames)
{
if (u == null)
{
throw new ArgumentNullException("null element in usernames array");
}
if (h.ContainsKey(u))
{
throw new ArgumentException("duplicate element in usernames array");
}
if (u.Length == 0 || u.Length > 256 || u.IndexOf(',') != -1)
{
throw new ArgumentException("element in usernames array in illegal format");
}
h.Add(u, u);
}
h = new Hashtable();
foreach (string r in roleNames)
{
if (r == null)
{
throw new ArgumentNullException("null element in rolenames array");
}
if (h.ContainsKey(r))
{
throw new ArgumentException("duplicate element in rolenames array");
}
if (r.Length == 0 || r.Length > 256 || r.IndexOf(',') != -1)
{
throw new ArgumentException("element in rolenames array in illegal format");
}
h.Add(r, r);
}
using (DbConnection connection = CreateConnection()) {
/* add the user/role combination to dbo.aspnet_UsersInRoles */
DbCommand command = factory.CreateCommand();
command.CommandText = @"dbo.aspnet_UsersInRoles_AddUsersToRoles";
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
AddParameter(command, "@RoleNames", String.Join(",", roleNames));
AddParameter(command, "@UserNames", String.Join(",", usernames));
AddParameter(command, "@ApplicationName", ApplicationName);
AddParameter(command, "@CurrentTimeUtc", DateTime.UtcNow);
DbParameter dbpr = AddParameter(command, "@ReturnVal", ParameterDirection.ReturnValue, DbType.Int32, null);
command.ExecuteNonQuery();
int returnValue = (int)dbpr.Value;
if (returnValue == 0)
{
return;
}
else if (returnValue == 2)
{
throw new ProviderException("One or more of the specified user/role names was not found.");
}
else if (returnValue == 3)
{
throw new ProviderException("One or more of the specified user names is already associated with one or more of the specified role names.");
}
else
{
throw new ProviderException("Failed to create new user/role association.");
}
}
}
protected override int ExecuteDelete(IDictionary keys, IDictionary oldValues)
{
if (!CanDelete)
{
throw new NotSupportedException("Delete operation is not supported");
}
if (oldValues == null && ConflictDetection == ConflictOptions.CompareAllValues)
{
throw new InvalidOperationException("oldValues parameters should be specified when ConflictOptions is set to CompareAllValues");
}
InitConnection();
DbCommand command = factory.CreateCommand();
command.CommandText = DeleteCommand;
command.Connection = connection;
if (DeleteCommandType == SqlDataSourceCommandType.Text)
{
command.CommandType = CommandType.Text;
}
else
{
command.CommandType = CommandType.StoredProcedure;
}
IDictionary oldDataValues;
if (ConflictDetection == ConflictOptions.CompareAllValues)
{
oldDataValues = new Hashtable();
if (keys != null)
{
foreach (DictionaryEntry de in keys)
{
oldDataValues [de.Key] = de.Value;
}
}
if (oldValues != null)
{
foreach (DictionaryEntry de in oldValues)
{
oldDataValues [de.Key] = de.Value;
}
}
}
else
{
oldDataValues = keys;
}
InitializeParameters(command, DeleteParameters, null, oldDataValues, true);
SqlDataSourceCommandEventArgs args = new SqlDataSourceCommandEventArgs(command);
OnDeleting(args);
if (args.Cancel)
{
return(-1);
}
bool closed = connection.State == ConnectionState.Closed;
if (closed)
{
connection.Open();
}
Exception exception = null;
int result = -1;;
try {
result = command.ExecuteNonQuery();
} catch (Exception e) {
exception = e;
}
if (closed)
{
connection.Close();
}
OnDataSourceViewChanged(EventArgs.Empty);
SqlDataSourceStatusEventArgs deletedArgs =
new SqlDataSourceStatusEventArgs(command, result, exception);
OnDeleted(deletedArgs);
if (exception != null && !deletedArgs.ExceptionHandled)
{
throw exception;
}
return(result);
}
public bool DeleteById(int id)
{
_errorCode = 0;
_rowsAffected = 0;
try
{
var sb = new StringBuilder();
sb.Append("DELETE FROM [dbo].[tCountry]");
sb.Append("WHERE ");
sb.Append("[countryId] = @countryId;");
sb.Append("SELECT @intErrorCode = @@ERROR; ");
var commandText = sb.ToString();
sb.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Delete command for entity [Country] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@countryId", csType.Int, ParameterDirection.Input, id);
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", csType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Delete method for entity [countries] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("CountryRepository::Delete::Error occured.", ex);
}
//throw new NotImplementedException();
}
public Araba IdSelect(int id)
{
_rowsAffected = 0;
Araba Araba = null;
try
{
var query = new StringBuilder();
query.Append("SELECT ");
query.Append("[AracID],[AracMarka] ,[AracModel],[Plaka],[EhliyetYasi],[YasSiniri],[GunkukSinirKilometre],[AnlikKilometre],[Airbag],[BagajHacmi] ,[KoltukSayisi],[KiralamaBedeli],[Sirket],[Resim],[Durum]");
query.Append("FROM [dbo].[Kiralama] ");
query.Append("WHERE ");
query.Append("[AracID] = @id ");
var commandText = query.ToString();
query.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException(
"dbCommand" + " The db SelectById command for entity [Kiralama] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Parameters
DBHelper.AddParameter(dbCommand, "@id", id);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
using (var reader = dbCommand.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var entity = new Araba();
entity.AracID = reader.GetInt32(0);
entity.AracMarka = reader.GetString(1);
entity.AracModel = reader.GetString(2);
entity.Plaka = reader.GetString(3);
entity.EhliyetYasi = reader.GetInt32(4);
entity.YasSiniri = reader.GetInt32(5);
entity.GunkukSinirKilometre = reader.GetInt32(6);
entity.AnlikKilometre = reader.GetInt32(7);
entity.Airbag = reader.GetString(8);
entity.BagajHacmi = reader.GetInt32(9);
entity.KoltukSayisi = reader.GetInt32(10);
entity.KiralamaBedeli = reader.GetInt32(11);
entity.Sirket = reader.GetInt32(12);
entity.Resim = reader.GetString(13);
entity.Durum = reader.GetString(14);
Araba = entity;
break;
}
}
}
}
}
return(Araba);
}
catch (Exception ex)
{
throw new Exception("KullaniciRepository:ID ile Seçim Hatası", ex);
}
}
public bool Ekle(Odeme entity)
{
_rowsAffected = 0;
try
{
var query = new StringBuilder();
query.Append("INSERT [dbo.tblOdeme] ");
query.Append("( [OdemeMiktari], [Tarih], [Basarili],[MusteriID] ) ");
query.Append("VALUES ");
query.Append(
"( @OdemeMiktari, @Tarih, @Basarili,@MusteriId ) ");
var commandText = query.ToString();
query.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [tbl_Transactions] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Params
DBHelper.AddParameter(dbCommand, "@OdemeMiktari", entity.OdemeMiktari);
DBHelper.AddParameter(dbCommand, "@OdemeTarihi", entity.OdemeTarihi.Date);
DBHelper.AddParameter(dbCommand, "@OdemeBasarili", entity.OdemeBasarili);
DBHelper.AddParameter(dbCommand, "@MusteriID", entity.MusteriId);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query
_rowsAffected = dbCommand.ExecuteNonQuery();
}
}
//Return the results of query/ies
return(true);
}
catch (Exception ex)
{
throw new Exception("TransactionsRepository::Insert:Error occured.", ex);
}
}
public IDbCommand CreateCommand()
{
return(_factory.CreateCommand());
}
public static void TestMain()
{
string connectionString = DataTestUtility.TcpConnStr;
string tempTable = DataTestUtility.GetUniqueName("T", "[", "]");
string tempKey = DataTestUtility.GetUniqueName("K", "[", "]");
DbProviderFactory provider = SqlClientFactory.Instance;
try
{
using (DbConnection con = provider.CreateConnection())
{
con.ConnectionString = connectionString;
con.Open();
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = con;
DbTransaction tx;
#region <<Create temp table>>
cmd.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract into " + tempTable + " from authors where au_id='UNKNOWN-ID'";
cmd.ExecuteNonQuery();
cmd.CommandText = "alter table " + tempTable + " add constraint " + tempKey + " primary key (au_id)";
cmd.ExecuteNonQuery();
#endregion
tx = con.BeginTransaction();
cmd.Transaction = tx;
cmd.CommandText = "insert into " + tempTable + "(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values ('876-54-3210', 'Doe', 'Jane' , '882-8080', 'One Microsoft Way', 'Redmond', 'WA', '98052', 0)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + tempTable + "(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values ('876-54-3211', 'Doe', 'John' , '882-8181', NULL, NULL, NULL, NULL, 0)";
cmd.ExecuteNonQuery();
tx.Commit();
cmd.Transaction = null;
string parameterName = "@p1";
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = parameterName;
p1.Value = "876-54-3210";
cmd.Parameters.Add(p1);
cmd.CommandText = "select * from " + tempTable + " where au_id >= " + parameterName;
// Test GetValue + IsDBNull
using (DbDataReader rdr = cmd.ExecuteReader())
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.Read())
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNull(i))
{
actualResult.Append("(NULL)");
}
else
{
actualResult.Append(rdr.GetValue(i));
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
// Test GetFieldValue<T> + IsDBNull
using (DbDataReader rdr = cmd.ExecuteReader())
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.Read())
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNull(i))
{
actualResult.Append("(NULL)");
}
else
{
if (rdr.GetFieldType(i) == typeof(bool))
{
actualResult.Append(rdr.GetFieldValue <bool>(i));
}
else if (rdr.GetFieldType(i) == typeof(decimal))
{
actualResult.Append(rdr.GetFieldValue <decimal>(i));
}
else
{
actualResult.Append(rdr.GetFieldValue <string>(i));
}
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
// Test GetFieldValueAsync<T> + IsDBNullAsync
using (DbDataReader rdr = cmd.ExecuteReaderAsync().Result)
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.ReadAsync().Result)
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNullAsync(i).Result)
{
actualResult.Append("(NULL)");
}
else
{
if (rdr.GetFieldType(i) == typeof(bool))
{
actualResult.Append(rdr.GetFieldValueAsync <bool>(i).Result);
}
else if (rdr.GetFieldType(i) == typeof(decimal))
{
actualResult.Append(rdr.GetFieldValueAsync <decimal>(i).Result);
}
else
{
actualResult.Append(rdr.GetFieldValueAsync <string>(i).Result);
}
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
}
// GetStream
byte[] correctBytes = { 0x12, 0x34, 0x56, 0x78 };
string queryString;
string correctBytesAsString = "0x12345678";
queryString = string.Format("SELECT CAST({0} AS BINARY(20)), CAST({0} AS IMAGE), CAST({0} AS VARBINARY(20))", correctBytesAsString);
using (var command = provider.CreateCommand())
{
command.CommandText = queryString;
command.Connection = con;
using (var reader = command.ExecuteReader())
{
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
byte[] buffer = new byte[256];
Stream stream = reader.GetStream(i);
int bytesRead = stream.Read(buffer, 0, buffer.Length);
for (int j = 0; j < correctBytes.Length; j++)
{
Assert.True(correctBytes[j] == buffer[j], "ERROR: Bytes do not match");
}
}
}
}
// GetTextReader
string[] correctStrings = { "Hello World", "\uFF8A\uFF9B\uFF70\uFF9C\uFF70\uFF99\uFF84\uFF9E" };
string[] collations = { "Latin1_General_CI_AS", "Japanese_CI_AS" };
for (int j = 0; j < collations.Length; j++)
{
string substring = string.Format("(N'{0}' COLLATE {1})", correctStrings[j], collations[j]);
queryString = string.Format("SELECT CAST({0} AS CHAR(20)), CAST({0} AS NCHAR(20)), CAST({0} AS NTEXT), CAST({0} AS NVARCHAR(20)), CAST({0} AS TEXT), CAST({0} AS VARCHAR(20))", substring);
using (var command = provider.CreateCommand())
{
command.CommandText = queryString;
command.Connection = con;
using (var reader = command.ExecuteReader())
{
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
char[] buffer = new char[256];
TextReader textReader = reader.GetTextReader(i);
int charsRead = textReader.Read(buffer, 0, buffer.Length);
string stringRead = new string(buffer, 0, charsRead);
Assert.True(stringRead == (string)reader.GetValue(i), "ERROR: Strings to not match");
}
}
}
}
}
}
finally
{
using (DbConnection con = provider.CreateConnection())
{
con.ConnectionString = connectionString;
con.Open();
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = "drop table " + tempTable;
cmd.ExecuteNonQuery();
}
}
}
}
public override DbCommand CreateCommand()
{
return(_tail.CreateCommand());
}
async static Task Main(string[] args)
{
DateTime dt = DateTime.Now;
int kw = dt.GetKW();
DbProviderFactory factory = Microsoft.Data.SqlClient.SqlClientFactory.Instance;
//DbProviderFactory factory = Microsoft.Data.Sqlite.SqliteFactory.Instance;
string sql = "Server=(localdb)\\mssqllocaldb;Database=Northwnd;Trusted_Connection=true";
string lite = @"Data Source=c:\db\Northwind.sqlite";
var con = factory.CreateConnection();
con.ConnectionString = sql;
con.Open();
var cmd = factory.CreateCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM Employees";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
}
con.Close();
Console.WriteLine("Hello World!");
#region tupel
string txt = "sieben";
string txt2 = "7";
var tuple = new Tuple <int, string, decimal, bool>(100_000_000, "lala", 12.44m, true);
var wert = GetZeug();
(int value, string unit, var ex) = GetZeug();
Console.WriteLine(value);
#endregion
#region tryparse
//int txtAlsZahl;
if (int.TryParse(txt2, out int txtAlsZahl))
{
Console.WriteLine($"Zahl ist ok: {txtAlsZahl}");
}
#endregion
#region funktionen
void LokaleFunktion()
{
Console.WriteLine("Lokal");
}
int zahllll = txt.Length < 7 ? 18 : throw new ExecutionEngineException();
if (txt.Length > 7)
{
zahllll = 18;
}
else
{
zahllll = 99;
}
LokaleFunktion();
LokaleFunktion();
int w = 12;
//int w2 = Verdoppelt(w);
VerdoppeltRef(ref w);
EineFunktion(11);
EineFunktion(11, 112);
EineFunktion(11, 112, "lala", 1.22);
EineFunktion(11, dd: 8.99); //neu 7.2
#endregion
Console.WriteLine("####################### yield");
foreach (string item in GetTexte())
{
Console.WriteLine(item);
}
await foreach (string item in GetTexteNEW_ab8_0())
{
Console.WriteLine(item);
}
#nullable enable
/// <summary>
/// 创建命令
/// </summary>
/// <returns></returns>
public DbCommand CreateCommand()
{
return(dbFactory.CreateCommand());
}
/// <summary>
/// 执行返回数据表
/// </summary>
/// <param name="context">数据库上下文</param>
/// <param name="sqlString">执行语句</param>
/// <param name="commandType">CommandType</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">页的大小</param>
/// <returns>数据表</returns>
public static DataTable ExecuteDataTable(string dbName, string sqlString, CommandType commandType = CommandType.Text, int pageIndex = 1, int pageSize = 10)
{
DataTable table = new DataTable();
var tuple = providers.Single(p => p.Item1 == dbName);
DbProviderFactory providerFactory = tuple.Item3.GetFactory(null);
DbConnection connection = providerFactory.CreateConnection();
connection.ConnectionString = tuple.Item2;
DbCommand command = providerFactory.CreateCommand();
command.Connection = connection;
command.CommandType = commandType;
command.CommandText = sqlString;
command.CommandTimeout = 0;
//打开连接
connection.Open();
using (IDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
{
int fieldCount = dr.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
table.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
}
object[] values = new object[fieldCount];
int currentIndex = 0;
int startIndex = pageSize * (pageIndex - 1);
try
{
table.BeginLoadData();
while (dr.Read())
{
if (startIndex > currentIndex++)
{
continue;
}
if (pageSize > 0 && (currentIndex - startIndex) > pageSize)
{
break;
}
dr.GetValues(values);
table.LoadDataRow(values, true);
}
}
finally
{
table.EndLoadData();
dr.Close();
}
}
return(table);
}
public override SiteMapNode BuildSiteMap()
{
SiteMapNode rootNode;
// Since the class is exposed to multiple pages,
// use locking to make sure that the site map is not rebuilt by more than one
// page at the same time.
lock (this)
{
rootNode = HttpContext.Current.Cache["rootNode"] as SiteMapNode;
if (rootNode == null)
{
Clear();
// Get all the data (using provider-agnostic code).
DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);
// Use this factory to create a connection.
DbConnection con = provider.CreateConnection();
con.ConnectionString = connectionString;
// Create the command.
DbCommand cmd = provider.CreateCommand();
cmd.CommandText = storedProcedure;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
// Creat the DataAdapter.
DbDataAdapter adapter = provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
// Get the results in a DataSet
// (a DataReader won't work because we need back-and-forth
// navigation).
// (Error handling code would be appropriate here.)
DataSet ds = new DataSet();
adapter.Fill(ds, "SiteMap");
DataTable dtSiteMap = ds.Tables["SiteMap"];
// Now navigate the DataSet to create the SiteMap.
// We won't check for all the possible error conditions
// (like duplicate root nodes).
// Get the root node.
DataRow rowRoot = dtSiteMap.Select("ParentID IS NULL")[0];
rootNode = new SiteMapNode(this,
rowRoot["Url"].ToString(), rowRoot["Url"].ToString(),
rowRoot["Title"].ToString(), rowRoot["Description"].ToString());
string rootID = rowRoot["ID"].ToString();
AddNode(rootNode);
// Fill down the hierarchy.
AddChildren(rootNode, rootID, dtSiteMap);
HttpContext.Current.Cache.Insert("rootNode", rootNode,
null, DateTime.Now.AddSeconds(cacheTime), TimeSpan.Zero);
}
}
return(rootNode);
}
public virtual DbCommand CreateCommand()
{
return(_dbProvider.CreateCommand());
}
/// <summary>
/// Determines the correct provider to use and sets up the connection and command
/// objects for use in other methods
/// </summary>
/// <param name="connectString">The full connection string to the database</param>
/// <param name="providerlist">The enum value of providers from dbutilities.Providers</param>
public void CreateDBObjects(string connectString, Providers providerList)
{
//CreateDBObjects(connectString, providerList, null);
switch (providerList)
{
case Providers.SqlServer:
_factory = SqlClientFactory.Instance;
break;
case Providers.Oracle:
_factory = OracleClientFactory.Instance;
break;
case Providers.OleDB:
_factory = OleDbFactory.Instance;
break;
case Providers.ODBC:
_factory = OdbcFactory.Instance;
break;
}
_connection = _factory.CreateConnection();
_command = _factory.CreateCommand();
_connection.ConnectionString = connectString;
_command.Connection = connection;
}
