/// <summary>
/// 执行查询返回DataTable
/// </summary>
/// <param name="sql">Sql语句</param>
/// <returns>成功返回DataTable,失败则返回 null</returns>
static public System.Data.DataTable ExecuteQueryToDataTable(string sql, System.Data.Common.DbConnection Conn)
{
if (Conn is System.Data.SQLite.SQLiteConnection)
{
var cmd = Conn.CreateCommand() as System.Data.SQLite.SQLiteCommand;
cmd.CommandText = sql;
cmd.CommandTimeout = 180;
var da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
if (Conn is OracleConnection)
{
var cmd = Conn.CreateCommand() as OracleCommand;
cmd.CommandText = sql;
cmd.CommandTimeout = 180;
var da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
else
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.IDataReader reader = ExecuteQuery(sql, Conn);
dt.Load(reader);
return(dt);
}
}//ExecuteQueryToDataTable(string sql)
} //Execute(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <param name="param">参数</param>
/// <returns>返回受影响行数</returns>
public static int Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param)
{
if (Conn == null)
{
Dbhelper.ErrLog(
"Dbhelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):连接对象为空!");
return(0);
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandText = sql;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
try
{
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
Dbhelper.ErrLog(
"Dbhelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):" +
ex.Message + "/nsql=" + sql);
return(0);
}
} //Execute(string sql,System.Data.IDataParameter[] param)
public void RunNativeWithMapper()
{
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var customer = new Customer();
ObjectFactory.Map(reader, customer);
}
}
}
}
public static string GenerateAllTables(this System.Data.Common.DbConnection connection, GeneratorBehavior generatorBehavior = GeneratorBehavior.Default)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var conneciontName = connection.GetType().Name.ToLower();
var tables = new List <string>();
var sql = generatorBehavior.HasFlag(GeneratorBehavior.View) ? TableSchemaSqls[conneciontName].Split("where")[0] : TableSchemaSqls[conneciontName];
using (var command = connection.CreateCommand(sql))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
}
var sb = new StringBuilder();
sb.AppendLine("namespace Models { ");
tables.ForEach(table => sb.Append(connection.GenerateClass(
string.Format(QuerySqls[conneciontName], table), table, generatorBehavior: generatorBehavior
)));
sb.AppendLine("}");
return(sb.ToString());
}
internal IDbCommand SetupCommand(IDbConnection cnn, Action <IDbCommand, object> paramReader)
{
var cmd = cnn.CreateCommand();
var init = GetInit(cmd.GetType());
init?.Invoke(cmd);
if (Transaction != null)
{
cmd.Transaction = Transaction;
}
cmd.CommandText = CommandText;
if (CommandTimeout.HasValue)
{
cmd.CommandTimeout = CommandTimeout.Value;
}
else if (SqlMapper.Settings.CommandTimeout.HasValue)
{
cmd.CommandTimeout = SqlMapper.Settings.CommandTimeout.Value;
}
if (CommandType.HasValue)
{
cmd.CommandType = CommandType.Value;
}
paramReader?.Invoke(cmd, Parameters);
return(cmd);
}
public static byte[] GetFlagFromDb(string iso)
{
byte[] baResult = null;
using (System.Data.Common.DbConnection con = GetConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT SUBSTRING(b64, 23, 40000000000000000) AS b64 FROM flags WHERE flag = @iso";
System.Data.Common.DbParameter country = cmd.CreateParameter();
country.ParameterName = "@iso";
country.Value = iso;
country.DbType = System.Data.DbType.String;
cmd.Parameters.Add(country);
object obj = cmd.ExecuteScalar();
string b64 = System.Convert.ToString(obj);
baResult = System.Convert.FromBase64String(b64);
} // End Using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using con
return(baResult);
}
public virtual void InsertUpdateDataTable(string tableSchema, string tableName, System.Data.DataTable dt)
{
string strSQL = "SELECT * FROM ";
if (tableSchema != null)
{
strSQL += this.QuoteObjectIfNecessary(tableSchema) + ".";
}
strSQL += this.QuoteObjectIfNecessary(tableName) + " WHERE (1 = 2) ";
using (System.Data.Common.DbConnection connection = this.Connection)
{
using (System.Data.Common.DbDataAdapter daInsertUpdate = this.m_factory.CreateDataAdapter())
{
using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand())
{
cmdSelect.CommandText = strSQL;
System.Data.Common.DbCommandBuilder cb = this.m_factory.CreateCommandBuilder();
cb.DataAdapter = daInsertUpdate;
daInsertUpdate.SelectCommand = cmdSelect;
daInsertUpdate.InsertCommand = cb.GetInsertCommand();
daInsertUpdate.UpdateCommand = cb.GetUpdateCommand();
daInsertUpdate.DeleteCommand = cb.GetDeleteCommand();
daInsertUpdate.Update(dt);
} // End Using cmdSelect
} // End Using daInsertUpdate
} // End Using connection
} // End Sub InsertUpdateDataTable
} //Execute(string sql,System.Data.IDataParameter[] param)
/// <summary>
/// 执行一个事务
/// </summary>
/// <param name="sqls">Sql语句组</param>
/// <returns>成功时返回true</returns>
public static bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
{
System.Data.IDbTransaction myTrans;
if (Conn == null)
{
Dbhelper.ErrLog("Dbhelper.ExecuteTrans(string[] sqls):连接对象为空!");
return(false);
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
myTrans = Conn.BeginTransaction();
cmd.Transaction = myTrans;
try
{
foreach (string sql in sqls)
{
if (sql != null)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
Dbhelper.ErrLog("Dbhelper.ExecuteTrans(string[] sqls):" + ex.Message);
return(false);
}
return(true);
} //Execute(string sql)
// AnySqlWebAdmin.Code.Trash.PgIntegratedSecurityTest.TestNpgSql();
public static void TestNpgSql()
{
var a = new { Test = 5, Result = "Success" };
var b = new { Test = 3, Result = "foo" };
var c = new { Test1 = 3, Result = "foo" };
System.Type t = a.GetType();
System.Console.WriteLine(t);
if (object.ReferenceEquals(a.GetType(), b.GetType()))
{
System.Console.WriteLine("Two anony = equal");
}
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Database = "osm_test"; // must be set
csb.Host = "localhost";
// csb.Host = "127.0.0.1"; // doesn't work
// csb.Host = System.Environment.MachineName; // doesn't work
csb.Port = 5432;
csb.IntegratedSecurity = true;
csb.Username = System.Environment.UserName; // Works when user exists
// csb.Username = "******"; // works as root
bool ret = false;
string sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'; ";
using (System.Data.Common.DbConnection conn = Npgsql.NpgsqlFactory.Instance.CreateConnection())
{
conn.ConnectionString = csb.ConnectionString;
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
using (System.Data.Common.DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
int?foo = (int?)cmd.ExecuteScalar();
if (foo.HasValue)
{
ret = foo.Value != 0;
}
} // End Using cmd
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Using conn
System.Console.WriteLine(ret);
} // End Sub TestNpgSql
}//ExecuteQueryToDataTable(string sql)
/// <summary>
/// 执行查询返回DataReader
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">连接对象</param>
/// <returns>成功时返回Reader对象，失败时返回null</returns>
static public System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn)
{
System.Data.IDataReader reader = null;
if (Conn == null)
{
return(null);
}
try
{
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandText = sql;
reader = cmd.ExecuteReader();
return(reader);
}
catch (Exception ex)
{
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！ExecuteQuery");
System.Threading.Thread.Sleep(200);
return(ExecuteQuery(sql, Conn));
}
else
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "; \n sql =" + sql);
}
return(null);
}
}//ExecuteQuery(string sql)
} //ExecuteQueryToDataTable(string sql)
/// <summary>
/// 执行查询返回DataReader
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">连接对象</param>
/// <returns>成功时返回Reader对象，失败时返回null</returns>
public static System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param = null)
{
System.Data.IDataReader reader = null;
if (Conn == null)
{
return(null);
}
try
{
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandText = sql;
if (param != null)
{
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
}
reader = cmd.ExecuteReader();
return(reader);
}
catch (Exception ex)
{
Dbhelper.ErrLog("Dbhelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message);
return(null);
}
} //ExecuteQuery(string sql)
private void UpdateWrongValues(System.Data.Common.DbConnection connection, SqlServerValidationToolkitContext ctx)
{
string q = CompiledQuery;
var c = connection.CreateCommand();
c.CommandText = q;
var reader = c.ExecuteReader();
List <WrongValue> correctedWrongValues = Validation_WrongValue.ToList();
while (reader.Read())
{
//the query returns the id of the invalid value and the errortype-id
int invalidValueId = reader.GetInt32(0);
string errorTypeCode = reader.GetString(1);
WrongValue existingWrongValue = Validation_WrongValue
.SingleOrDefault(wv =>
wv.Errortype.CodeForValidationQueries == errorTypeCode
&&
wv.Id == invalidValueId
);
string value = GetValue(invalidValueId, connection);
if (existingWrongValue == null)
{
ErrorType errorType = ctx.Errortypes.Where(et => et.CodeForValidationQueries == errorTypeCode).SingleOrDefault();
if (errorType == null) //errorType should exist
{
throw new Exception("ErrorType not found for code '" + errorTypeCode + "'");
}
WrongValue wrongValue = new WrongValue()
{
Errortype = errorType,
Id = invalidValueId,
Value = value
};
Validation_WrongValue.Add(wrongValue);
}
else
{
existingWrongValue.Value = value;
correctedWrongValues.Remove(existingWrongValue);
}
}
_log.Info(string.Format("{0} wrong values are corrected", correctedWrongValues.Count));
foreach (var wvCorrected in correctedWrongValues)
{
wvCorrected.Is_Corrected = true;
}
}
/// <summary>
/// 更新 Apq_Tables, Apq_Columns 表内容
/// </summary>
/// <param name="DBName">目标数据库名</param>
/// <returns>影响行数</returns>
public int Refresh(string DBName)
{
#region 参数检测
if (string.IsNullOrEmpty(DBName))
{
throw new ArgumentNullException("DBName");
}
#endregion
System.Data.Common.DbCommand Command = Connection.CreateCommand();
Apq.Data.Common.DbCommandHelper CommandHelper = new Common.DbCommandHelper(Command);
CommandHelper.AddParameter("@Apq_Tables", Apq_Tables);
Command.CommandText = "SELECT OBJECT_ID( @Apq_Tables )";
Common.DbConnectionHelper.Open(Connection);
#region Apq_Tables
if (System.Convert.IsDBNull(Command.ExecuteScalar()))
{
Command.CommandText = string.Format(Sqls.Apq_TablesCreate,
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
Apq.Data.SqlClient.Common.EncodeString(DBName)
);
Command.ExecuteNonQuery();
}
Command.CommandText = string.Format(Sqls.Apq_TablesUpdate,
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
Apq.Data.SqlClient.Common.EncodeString(DBName)
);
Command.ExecuteNonQuery();
#endregion
#region Apq_Columns
CommandHelper.AddParameter("@Columns", Apq_Columns);
Command.CommandText = "SELECT OBJECT_ID( @Columns )";
if (System.Convert.IsDBNull(Command.ExecuteScalar()))
{
Command.CommandText = string.Format(Sqls.Apq_ColumnsCreate,
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
Apq.Data.SqlClient.Common.EncodeString(DBName)
);
Command.ExecuteNonQuery();
}
Command.CommandText = string.Format(Sqls.Apq_ColumnsUpdate,
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
Apq.Data.SqlClient.Common.EncodeString(DBName)
);
return(Command.ExecuteNonQuery());
#endregion
}
private async Task SubscribeQuestionAnswerAsync(Guid questionId, System.Data.Common.DbConnection connection)
{
var command = connection.CreateCommand();
command.CommandText = $"SELECT Id FROM Answers WHERE QuestionId = '{questionId.ToString()}';";
var scoreDependency = new SqlDependency((SqlCommand)command);
scoreDependency.OnChange += OnAnswerAdded;
SqlDependency.Start(configuration.GetConnectionString("SqlServer"));
_ = await command.ExecuteReaderAsync();
}
/// <summary>
/// Insert Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select Statement</param>
/// <param name="pDataRows">DataRows</param>
/// <returns>bool</returns>
public static bool InsertData(string pFileString, string pSelectSQL, System.Data.DataRow[] pDataRows)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
try
{
Conn.Open();
//cmd = builder.GetInsertCommand(true);
cmd = Conn.CreateCommand();
cmd.CommandTimeout = 1500;
//builder.GetInsertCommand((true);
foreach (DataRow R in pDataRows)
{
//string SQL = GetInsertSQL(pSelectSQL.Substring(pSelectSQL.LastIndexOf (" From ") + 6, pSelectSQL.Length - pSelectSQL.LastIndexOf(" From ") - 6),R);
string SQL = GetInsertSQL(R.Table.TableName, R);
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
}
result = true;
return(result);
}
public System.Data.Common.DbCommand GetCommand(System.Data.Common.DbConnection con)
{
if (this.m_cmd != null)
{
this.m_cmd.Connection = con;
return(this.m_cmd);
}
System.Data.Common.DbCommand cmd = con.CreateCommand();
cmd.CommandText = this.m_sql;
return(cmd);
}
/// <summary>
/// Returns the value of the column for the id
/// </summary>
private string GetValue(int invalidValueId, System.Data.Common.DbConnection connection)
{
string selectValueSqlFormat = "SELECT [{0}] FROM [{1}] WHERE [{2}]={3}";
string selectValueSql = string.Format(selectValueSqlFormat,
Column.Name,
Column.Source.Name,
Column.Source.IdColumnName,
invalidValueId);
var c = connection.CreateCommand();
c.CommandText = selectValueSql;
var result = c.ExecuteScalar();
return(result.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="pFileString"></param>
/// <param name="pTableName"></param>
/// <param name="pIsConnectionString"></param>
/// <returns></returns>
public override bool CheckDatabaseExistance(string pFileString, string pTableName, bool pIsConnectionString = false)
{
System.Data.Common.DbConnection Conn = null;
//System.Data.Common.DbDataAdapter Adapter = null;
System.Data.DataTable DataTable = new System.Data.DataTable();
bool result = false;
string connString = pFileString;
string[] restrictions = new string[] { null, null, pTableName };
if (DataSource != null)
{
IDbDriver driver = this;
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
try
{
Conn.Open();
Conn.ChangeDatabase("master");
System.Data.Common.DbCommand cmd = Conn.CreateCommand();
cmd.CommandText = "SELECT database_id FROM sys.databases WHERE Name = @name";
System.Data.Common.DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = "@name";
parameter.Value = driver.DbName;
cmd.Parameters.Add(parameter);
object ret = cmd.ExecuteScalar();
if (ret != null)
{
int databaseID = (int)ret;
result = databaseID > 0;
}
}
catch (Exception ex)
{
// do nothing
}
finally
{
Conn.Close();
}
}
return(result);
}
//public static System.Data.DataTable ExecuteStoredProcedure(this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade
// )
//{
// return ExecuteStoredProcedure(databaseFacade, "foo", ("abc", "def"), ("abc", 5));
//}
public static System.Data.DataTable ExecuteStoredProcedure(
this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade,
string procedureName,
params System.ValueTuple <string, object>[] parameters
)
{
System.Data.DataTable dt = new System.Data.DataTable();
// using (System.Data.Common.DbConnection con = databaseFacade.GetDbConnection())
System.Data.Common.DbConnection con = databaseFacade.GetDbConnection();
{
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = procedureName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; ++i)
{
System.Data.Common.DbParameter p = cmd.CreateParameter();
p.ParameterName = parameters[i].Item1;
p.Value = parameters[i].Item2;
p.DbType = GetDbType(p.Value);
if (p.Value == null)
{
p.Value = System.DBNull.Value;
} // End if (p.Value == null)
cmd.Parameters.Add(p);
} // Next i
} // End if (parameters != null)
// cmd.ExecuteNonQuery();
// cmd.ExecuteReader();
using (System.Data.Common.DbDataAdapter da = System.Data.Common.ProviderExtensions.CreateDataAdapter(con, cmd))
{
da.Fill(dt);
} // End Using da
} // End Using cmd
} // End Using con
return(dt);
} // End Function ExecuteStoredProcedure
private void GetView(MyBlogCore.SqlFactory factory, string viewPath)
{
string query = @"
SELECT Content, LastModified FROM Views WHERE Location = @Path;
UPDATE Views SET LastRequested = GetUtcDate() WHERE Location = @Path;
";
try
{
using (System.Data.Common.DbConnection cnn = factory.Connection)
{
using (System.Data.Common.DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
cmd.AddWithValue("@Path", viewPath);
if (cnn.State != System.Data.ConnectionState.Open)
{
cnn.Open();
}
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
_exists = reader.HasRows;
if (_exists)
{
reader.Read();
_viewContent = System.Text.Encoding.UTF8.GetBytes(reader["Content"].ToString());
_lastModified = System.Convert.ToDateTime(reader["LastModified"]);
} // End if (_exists)
} // End Using reader
} // End Using cmd
if (cnn.State != System.Data.ConnectionState.Closed)
{
cnn.Close();
}
} // End Using cnn
}
catch (System.Exception ex)
{
// if something went wrong, Exists will be false
}
}
Task <IDictionary <string, object> > IHealthCheckable.HealthCheckAsync()
{
IDictionary <string, object> returnValue = new Dictionary <string, object>();
System.Data.Common.DbConnection dbConnection = null;
try { dbConnection = _initiativeContext?.Database?.GetDbConnection(); }
catch (Exception err) { returnValue["dbError"] = err.Message; }
if (dbConnection != null)
{
try { returnValue["host"] = dbConnection.DataSource; } catch (Exception err) { returnValue["host"] = err.Message; }
try { returnValue["database"] = dbConnection.Database; } catch (Exception err) { returnValue["database"] = err.Message; }
try
{
_initiativeContext.Database.OpenConnection();
try { returnValue["serverVersion"] = dbConnection.ServerVersion; } catch (Exception err) { returnValue["serverVersion"] = err.Message; }
// The following should produce the same as serverVersion, but to be sure we'll run it again.
// Also, we'll time it and supply that result
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "SELECT @@VERSION";
var watch = new Stopwatch();
watch.Start();
var result = cmd.ExecuteScalar();
watch.Stop();
returnValue["version"] = result;
returnValue["pingMilliseconds"] = watch.Elapsed.TotalMilliseconds;
cmd.CommandText = "SELECT TOP (1) [MigrationId] FROM[CoeIdeas].[dbo].[__EFMigrationsHistory] ORDER BY MigrationId DESC";
returnValue["CurrentMigration"] = cmd.ExecuteScalar();
}
}
catch (Exception) { /* eat the exception */ }
finally
{
_initiativeContext.Database.CloseConnection();
}
}
return(Task.FromResult(returnValue));
}
public Response ValidationSchedule(string bz)
{
using (DZEntities dzEntities = new DZEntities())
{
Response response = new Response();
response.IsSuccess = true;
System.Data.EntityClient.EntityConnection entityConnection = (System.Data.EntityClient.EntityConnection)dzEntities.Connection;
entityConnection.Open();
System.Data.Common.DbConnection storeConnection = entityConnection.StoreConnection;
System.Data.Common.DbCommand cmd = storeConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "P_UN_VALIDATION";
OracleParameter[] sqlpara = new OracleParameter[3];
sqlpara[0] = new OracleParameter("p_bz", bz);
sqlpara[1] = new OracleParameter("p_ErrCode", OracleDbType.Varchar2, 30);
sqlpara[2] = new OracleParameter("p_ErrMsg", OracleDbType.Varchar2, 1000);
sqlpara[0].Direction = ParameterDirection.Input;
sqlpara[1].Direction = ParameterDirection.Output;
sqlpara[2].Direction = ParameterDirection.Output;
cmd.Parameters.Add(sqlpara[0]);
cmd.Parameters.Add(sqlpara[1]);
cmd.Parameters.Add(sqlpara[2]);
cmd.ExecuteNonQuery();
if (cmd.Parameters[1].Value.ToString() == "1")
{
response.IsSuccess = true;
response.MessageText = cmd.Parameters[2].Value.ToString();
}
else
{
response.IsSuccess = false;
response.MessageText = cmd.Parameters[2].Value.ToString();
}
cmd.Dispose();
return(response);
}
}
} //ExecuteQuery(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <returns>返回受影响行数</returns>
public static int Execute(string sql, System.Data.Common.DbConnection Conn)
{
if (Conn == null)
{
Dbhelper.ErrLog("Dbhelper.Execute(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
return(0);
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandText = sql;
try
{
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
Dbhelper.ErrLog("Dbhelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message +
"/nsql=" + sql);
return(0);
}
} //Execute(string sql)
private void test()
{
string query = @"SELECT * FROM Users WHERE SomeProp = {0}
AND SomeId IN (SELECT id FROM fn_myfunc({1})";
string someProp = "'5'";
int someId = 3;
var users = this.m_ctx.issues.FromSql(query, someProp, someId)
.Include(u => u.closed_on)
.AsNoTracking()
.ToList();
var books = this.m_ctx.issues.FromSql("EXEC GetAllBooks").ToList();
var authorId = new System.Data.SqlClient.SqlParameter("@AuthorId", 1);
var books2 = this.m_ctx.issues
.FromSql("EXEC GetBooksByAuthor @AuthorId", authorId)
.ToList();
// this.m_ctx.Database.ProviderName
// this.m_ctx.Database.createpa
// This is EF6...
// https://weblogs.asp.net/Dixin/EntityFramework.Functions
this.m_ctx.Database.GetDbConnection();
using (System.Data.Common.DbConnection con = this.m_ctx.Database.GetDbConnection())
{
using (var cmd = con.CreateCommand())
{
var p = cmd.CreateParameter();
}
}
}
}//Execute(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <returns>返回受影响行数</returns>
static public int ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbTransaction tran)
{
if (Conn == null)
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
return(0);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.Transaction = tran;
cmd.CommandText = sql;
try
{
var count = cmd.ExecuteNonQuery();
cmd.Dispose();
return(count);
}
catch (Exception ex)
{
cmd.Dispose();
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！ExecuteWithTrans");
System.Threading.Thread.Sleep(200);
return(Execute(sql, Conn));
}
else
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
return(0);
}
}
}//Execute(string sql)
public List <contacts> GetAllContacts()
{
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM contacts";
List <contacts> contactsList = new List <contacts>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
contacts contact = new contacts();
contact.name = reader.GetValue(0).ToString();
contact.birthdate = DateTime.Parse(reader.GetValue(1).ToString());
contact.address = reader.GetValue(2).ToString();
contactsList.Add(contact);
}
}
return(contactsList);
}
internal IDbCommand SetupCommand(IDbConnection cnn, Action<IDbCommand, object> paramReader)
{
var cmd = cnn.CreateCommand();
var init = GetInit(cmd.GetType());
init?.Invoke(cmd);
if (Transaction != null)
cmd.Transaction = Transaction;
cmd.CommandText = CommandText;
if (CommandTimeout.HasValue)
{
cmd.CommandTimeout = CommandTimeout.Value;
}
else if (SqlMapper.Settings.CommandTimeout.HasValue)
{
cmd.CommandTimeout = SqlMapper.Settings.CommandTimeout.Value;
}
if (CommandType.HasValue)
cmd.CommandType = CommandType.Value;
paramReader?.Invoke(cmd, Parameters);
return cmd;
}
public static void DataToXML()
{
string table_schema = "geoip";
string table_name = "geoip_blocks_temp";
table_schema = "public";
table_name = "t_sys_language_monthnames";
using (System.Data.DataTable dt = new System.Data.DataTable())
{
dt.TableName = "record";
using (System.Data.DataSet ds = new System.Data.DataSet(table_name))
{
ds.Tables.Add(dt);
// dt.Namespace = "foo";
using (System.Data.Common.DbConnection con = Npgsql.NpgsqlFactory.Instance.CreateConnection())
{
con.ConnectionString = GetCS();
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM " + table_schema + "." + table_name;
using (System.Data.Common.DbDataAdapter da = Npgsql.NpgsqlFactory.Instance.CreateDataAdapter())
{
da.SelectCommand = cmd;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
da.Fill(dt);
if (con.State != System.Data.ConnectionState.Open)
{
con.Close();
}
} // End Using da
} // End Using cmd
} // End Using con
string exportFilename = System.IO.Path.Combine(@"d:\", table_name + ".xml");
//using (System.IO.Stream fs = System.IO.File.OpenWrite(exportFilename))
//{
// using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8))
// {
// // System.IO.StringWriter sw = new System.IO.StringWriter();
// // dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
// dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
// } // End Using sw
//} // End Using fs
System.Xml.XmlWriterSettings xs = new System.Xml.XmlWriterSettings();
xs.Indent = true;
xs.IndentChars = " ";
xs.NewLineChars = System.Environment.NewLine;
xs.OmitXmlDeclaration = false;
// xs.Encoding = System.Text.Encoding.UTF8; // doesn't work with pgsql
xs.Encoding = new System.Text.UTF8Encoding(false);
// <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(exportFilename, xs))
{
dt.WriteXml(writer, System.Data.XmlWriteMode.IgnoreSchema);
} // End Using writer
System.Console.WriteLine(dt.Rows.Count);
} // End Using ds
} // End Using dt
} // End Sub DataToXML
} // End Sub DataToXML
public static void LargeDataToXML()
{
string table_schema = "geoip";
string table_name = "geoip_blocks_temp";
// table_schema = "public";
// table_name = "t_sys_language_monthnames";
System.Xml.XmlWriterSettings xs = new System.Xml.XmlWriterSettings();
xs.Indent = true;
xs.IndentChars = " ";
xs.NewLineChars = System.Environment.NewLine;
xs.OmitXmlDeclaration = false; // // <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
// xs.Encoding = System.Text.Encoding.UTF8; // doesn't work with pgsql
xs.Encoding = new System.Text.UTF8Encoding(false);
string exportFilename = System.IO.Path.Combine(@"d:\", table_name + ".xml");
// System.Text.StringBuilder builder = new System.Text.StringBuilder();
// using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(builder, xs))
using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(exportFilename, xs))
{
writer.WriteStartDocument();
writer.WriteStartElement(table_name);
writer.WriteAttributeString("xmlns", "xsi", null, System.Xml.Schema.XmlSchema.InstanceNamespace);
// writer.WriteAttributeString("xsi", "schemaLocation", null, System.Xml.Schema.XmlSchema.InstanceNamespace);
using (System.Data.Common.DbConnection con = Npgsql.NpgsqlFactory.Instance.CreateConnection())
{
con.ConnectionString = GetCS();
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM " + table_schema + "." + table_name;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (dr.HasRows)
{
int fc = dr.FieldCount;
string[] columnNames = new string[fc];
// System.Type[] columnTypes = new System.Type[fc];
for (int i = 0; i < dr.FieldCount; ++i)
{
columnNames[i] = dr.GetName(i);
// columnTypes[i] = dr.GetFieldType(i);
} // Next i
while (dr.Read())
{
// object[] thisRow = new object[dr.FieldCount];
writer.WriteStartElement("record");
for (int i = 0; i < fc; ++i)
{
writer.WriteStartElement(columnNames[i]);
object obj = dr.GetValue(i);
if (obj != System.DBNull.Value)
{
writer.WriteValue(obj);
}
else
{
writer.WriteAttributeString("xsi", "nil", System.Xml.Schema.XmlSchema.InstanceNamespace, "true");
}
writer.WriteEndElement();
} // Next i
writer.WriteEndElement();
} // Whend
} // End if (dr.HasRows)
} // End Using dr
if (con.State != System.Data.ConnectionState.Open)
{
con.Close();
}
} // End Using cmd
} // End Using con
writer.WriteEndElement();
} // ENd Using writer
} // End Sub LargeDataToXML
static void Main()
{
#if false
System.Windows.Forms.Application.EnableVisualStyles();
System.Windows.Forms.Application.SetCompatibleTextRenderingDefault(false);
System.Windows.Forms.Application.Run(new Form1());
#endif
// https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5
// Postgres 9.5 (released since 2016-01-07) offers an "upsert" command:
// INSERT ... ON CONFLICT DO NOTHING/UPDATE
// INSERT INTO countries (country) VALUES ('France'),('Japan') ON CONFLICT DO NOTHING;
// Wrong
// INSERT INTO TestTable(some_field) VALUES('37F582A1-7D28-4052-837B-1AB065FDA88A') WHERE 0 = (SELECT COUNT(*) FROM TestTable WHERE some_field = '37F582A1-7D28-4052-837B-1AB065FDA88A')
// Correct:
// INSERT INTO TestTable(some_field) SELECT '37F582A1-7D28-4052-837B-1AB065FDA88A' WHERE 0 = (SELECT COUNT(*) FROM TestTable WHERE some_field = '37F582A1-7D28-4052-837B-1AB065FDA88A')
// Better:
// INSERT INTO invoices (invoiceid, billed) SELECT '12345', 'TRUE' WHERE NOT EXISTS (SELECT 1 FROM invoices WHERE invoiceid = '12345')
string sql = @"
INSERT INTO TABLE (fields)
SELECT fields
WHERE 0 = (SELECT COUNT(*) FROM table WHERE PK = @pk) ";
sql = @"
UPDATE table
SET field1 = value1
,field2 = value2
WHERE PK = @pk";
// Get non-computed columns for table
sql = @"
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE (1=1)
AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T_Benutzer'
AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME,'IsComputed') = 0
";
// Get PK Info
sql = @"
SELECT
tc.TABLE_CATALOG
,tc.TABLE_SCHEMA
,tc.TABLE_NAME
,tc.CONSTRAINT_NAME
,kcu.COLUMN_NAME
,kcu.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
ON kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
AND kcu.TABLE_CATALOG = tc.TABLE_CATALOG
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
-- AND tc.TABLE_CATALOG = '...'
AND tc.TABLE_SCHEMA = 'dbo'
AND tc.TABLE_NAME = 'T_ZO_Inventory_Dwg'
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION
";
using (System.Data.Common.DbConnection con = GetConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SQL";
// dimMin
System.Data.Common.DbParameter width = cmd.CreateParameter();
width.ParameterName = "@dimMin";
width.Value = 123;
width.DbType = System.Data.DbType.Double;
cmd.Parameters.Add(width);
// cmd.ExecuteReader()
// using (System.Data.Common.DbDataAdapter da = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)cmd)) { da.Fill(dt); }
}
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
System.Console.WriteLine(System.Environment.NewLine);
System.Console.WriteLine(" --- Press any key to continue --- ");
System.Console.ReadKey();
}
public static void SaveAssembly(string assemblyName, string path)
{
string sql = @"
--DECLARE @__assemblyname nvarchar(260)
--SET @__assemblyname = 'Microsoft.SqlServer.Types'
SELECT
A.name
,AF.content
FROM sys.assembly_files AS AF
INNER JOIN sys.assemblies AS A
ON AF.assembly_id = A.assembly_id
WHERE AF.file_id = 1
AND A.name = @__assemblyname
;
";
AnySqlWebAdmin.SqlService service = new AnySqlWebAdmin.SqlService();
using (System.Data.Common.DbConnection con = service.Connection)
{
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
var p = cmd.CreateParameter();
p.ParameterName = "__assemblyname";
p.DbType = System.Data.DbType.String;
p.Value = assemblyName;
cmd.Parameters.Add(p);
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.IDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
reader.Read();
//SqlBytes bytes = reader.GetSqlBytes(0);
const int BUFFER_SIZE = 1024;
byte[] buffer = new byte[BUFFER_SIZE];
int col = reader.GetOrdinal("content");
int bytesRead = 0;
int offset = 0;
// write the byte stream out to disk
using (System.IO.FileStream bytestream = new System.IO.FileStream(path, System.IO.FileMode.CreateNew))
{
while ((bytesRead = (int)reader.GetBytes(col, offset, buffer, 0, BUFFER_SIZE)) > 0)
{
bytestream.Write(buffer, 0, bytesRead);
offset += bytesRead;
} // Whend
bytestream.Close();
} // End Using bytestream
reader.Close();
} // End Using reader
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
}
} // End Function SaveAssembly