public static void ValidConnStringTest()
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(DataTestClass.SQL2008_Northwind_NamedPipes);
builder.ConnectTimeout = 5;
// Unmodified server string
OpenGoodConnection(builder.ConnectionString);
// Just using server name
builder.DataSource = "np:" + GetHostFromDataSource(builder.DataSource);
OpenGoodConnection(builder.ConnectionString);
}
public SplitPacketTest()
{
string actualHost;
int actualPort;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr);
GetTcpInfoFromDataSource(builder.DataSource, out actualHost, out actualPort);
Task.Factory.StartNew(() => { SetupProxy(actualHost, actualPort); });
Thread.Sleep(2000);
Assert.True(Port != -1, "Proxy local port not defined!");
builder.DataSource = "tcp:127.0.0.1," + Port;
BaseConnString = builder.ConnectionString;
}
public static void InvalidConnStringTest()
{
const string invalidConnStringError = "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 25 - Connection string is not valid)";
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(DataTestClass.SQL2008_Northwind_NamedPipes);
builder.ConnectTimeout = 2;
string host = GetHostFromDataSource(builder.DataSource);
// Using forwad slashes
builder.DataSource = "np://" + host + "/pipe/sql/query";
OpenBadConnection(builder.ConnectionString, invalidConnStringError);
// Without pipe token
builder.DataSource = @"np:\\" + host + @"\sql\query";
OpenBadConnection(builder.ConnectionString, invalidConnStringError);
// Without a pipe name
builder.DataSource = @"np:\\" + host + @"\pipe";
OpenBadConnection(builder.ConnectionString, invalidConnStringError);
// Nothing after server
builder.DataSource = @"np:\\" + host;
OpenBadConnection(builder.ConnectionString, invalidConnStringError);
// No leading slashes
builder.DataSource = @"np:" + host + @"\pipe\sql\query";
OpenBadConnection(builder.ConnectionString, invalidConnStringError);
// No server name
builder.DataSource = @"np:\\\pipe\sql\query";
OpenBadConnection(builder.ConnectionString, invalidConnStringError);
// Nothing but slashes
builder.DataSource = @"np:\\\\\";
OpenBadConnection(builder.ConnectionString, invalidConnStringError);
// Empty string
builder.DataSource = "np:";
OpenBadConnection(builder.ConnectionString, invalidConnStringError);
}
public static void TestMain()
{
_operationCanceledErrorMessage = SystemDataResourceManager.Instance.SQL_OperationCancelled;
_severeErrorMessage = SystemDataResourceManager.Instance.SQL_SevereError;
// pure random
_randPool = new RandomizerPool();
SqlConnectionStringBuilder regularConnectionString = new SqlConnectionStringBuilder();
regularConnectionString.ConnectionString = DataTestClass.SQL2008_Master;
regularConnectionString.MultipleActiveResultSets = false;
List<string> connStrings = new List<string>();
connStrings.Add(regularConnectionString.ToString());
connStrings.Add(regularConnectionString.ToString());
regularConnectionString.MultipleActiveResultSets = true;
connStrings.Add(regularConnectionString.ToString());
_connectionStrings = connStrings.ToArray();
_katmaiTypes = SqlRandomTypeInfoCollection.CreateSql2008Collection();
_endEvent = new ManualResetEvent(false);
if (_randPool.ReproMode)
{
_runningThreads = 1;
TestThread();
}
else
{
for (int tcount = 0; tcount < ThreadCountDefault; tcount++)
{
Thread t = new Thread(TestThread);
t.Start();
}
}
}
public static void ConnectToSQLWithInstanceNameTest()
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr);
bool proceed = true;
string dataSourceStr = builder.DataSource.Replace("tcp:", "");
string[] serverNamePartsByBackSlash = dataSourceStr.Split('\\');
if (!dataSourceStr.Contains(",") && serverNamePartsByBackSlash.Length == 2)
{
string hostname = serverNamePartsByBackSlash[0];
proceed = !String.IsNullOrWhiteSpace(hostname) && IsBrowserAlive(hostname);
}
if(proceed)
{
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
connection.Close();
}
}
}
public static async Task <ReturnedSaveFuncInfo> ReStoreDbAsync(string connectionString, string pathf, bool autoBackup, ENSource Source)
{
var ret = new ReturnedSaveFuncInfo();
var cn = new SqlConnection();
try
{
GC.Collect();
SqlConnection.ClearAllPools();
if (autoBackup)
{
var dir = Application.StartupPath + "\\AradBackUp";
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
var file = Path.GetFileName(System.Reflection.Assembly.GetEntryAssembly()?.Location)?.Replace(".exe", "__");
var d = Calendar.MiladiToShamsi(DateTime.Now).Replace("/", "_");
d += "__" + DateTime.Now.Hour + " - " + DateTime.Now.Minute;
file += d;
var filepath = dir + "\\" + file + ".Bak";
ret.AddReturnedValue(await BackupDbAsync(connectionString, Source, EnBackUpType.Manual, filepath));
}
if (pathf.EndsWith(".NPZ") || pathf.EndsWith(".npz"))
{
var zp = new Zip();
pathf = zp.ExtractTempDIR(pathf);
}
else if (pathf.EndsWith(".NPZ2") || pathf.EndsWith(".npz2"))
{
try
{
var zp = new CompressFile();
var res = await zp.ExtractTempDIR(pathf);
ret.AddReturnedValue(res);
if (res.HasError)
{
return(ret);
}
pathf = res.value;
}
catch
{
var zp = new Zip();
pathf = zp.ExtractTempDIR(pathf);
}
}
var csb = new SqlConnectionStringBuilder(connectionString);
var serverConnectionString =
connectionString.Replace("Initial Catalog=" + csb.InitialCatalog, "Initial Catalog=");
cn = new SqlConnection(serverConnectionString)
{
ConnectionString = serverConnectionString
};
var cmd = new SqlCommand("RESTORE DATABASE [dbName] FROM DISK = N'bkPath' WITH FILE = 1, MOVE N'novin' TO N'dbMDFPath', MOVE N'novin_log' TO N'dbLDFPath', NOUNLOAD, REPLACE, STATS = 5", cn);
var dbInfo = new CLSServerDBs(connectionString);
var backUpInfo = new DataBaseBackUpInfo(pathf, cn.ConnectionString);
cmd.CommandText = cmd.CommandText.Replace("dbName", csb.InitialCatalog);
cmd.CommandText = cmd.CommandText.Replace("bkPath", pathf);
cmd.CommandText = cmd.CommandText.Replace("novin", backUpInfo.LogicalName);
cmd.CommandText = cmd.CommandText.Replace("novin_log", backUpInfo.LogicalName + "_log.ldf");
cmd.CommandText = cmd.CommandText.Replace("dbMDFPath", dbInfo.FileName);
cmd.CommandText =
cmd.CommandText.Replace("dbLDFPath", dbInfo.FileName.ToLower().Replace(".mdf", "_log.Ldf"));
//تایم اوت بالا بردن مجاز است
cmd.CommandTimeout = 5 * 60 * 1000;//5 minutes
cn.Open();
var temp = cmd.CommandText;
cmd.CommandText = "ALTER DATABASE [" + csb.InitialCatalog + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
cmd.ExecuteNonQuery();
cmd.CommandText = temp;
cmd.ExecuteNonQuery();
// cmd.CommandText = NovinPardaz.Properties.Resources.DatabaseSettings;
cmd.CommandText = cmd.CommandText.Replace("dbName", csb.InitialCatalog);
cmd.ExecuteNonQuery();
cmd.CommandText = "ALTER DATABASE [" + csb.InitialCatalog + "] SET Multi_USER";
cmd.ExecuteNonQuery();
cn.Close();
SqlConnection.ClearAllPools();
Application.DoEvents();
}
catch (SqlException ex)
{
WebErrorLog.ErrorInstence.StartErrorLog(ex);
try { cn.Close(); } catch { }
ret.AddReturnedValue(ReturnedState.Error, $"حساب کاربری SQL دسترسی به مسیر نصب برنامه ندارد\r\n{ex.Message }//{ex?.InnerException?.Message ?? ""}");
}
catch (Exception ex)
{
WebErrorLog.ErrorInstence.StartErrorLog(ex);
try { cn.Close(); } catch { }
ret.AddReturnedValue(ex);
}
Task.Run(() => SetMultiUser(connectionString));
return(ret);
}
public static int Generate(string whereClause, string filename, bool isMyRandotrips)
{
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = Consts.DB_SERVER;
builder.UserID = Consts.DB_USER;
builder.Password = Consts.DB_PASSWORD;
builder.InitialCatalog = Consts.DB_NAME;
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
StringBuilder ssb = new StringBuilder();
ssb.Append("SELECT point_type,intent_set,what_3_words,text,photos,short_hash_id,latitude,longitude,final_bearing,power,z_score,nearest_place,country,datetime FROM ");
// 0 1 2 3 4 5 6 7 8 9 10 11 12
#if RELEASE_PROD
ssb.Append("reports");
#else
ssb.Append("reports");
//ssb.Append("reports_dev");
#endif
ssb.Append($" WHERE VISITED = '1' {whereClause} ORDER BY DATETIME ASC;");
Console.WriteLine("SQL:" + ssb.ToString());
List <FlyTo> flytos = new List <FlyTo>();
List <Point> points = new List <Point>();
using (SqlCommand sqlCommand = new SqlCommand(ssb.ToString(), connection))
{
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
var flyto = new FlyTo();
var point = new Point();
// latitude
if (!reader.IsDBNull(6))
{
flyto.latitude = point.latitude = reader.GetDouble(6);
}
// longitude
if (!reader.IsDBNull(7))
{
flyto.longitude = point.longitude = reader.GetDouble(7);
}
// bearing
if (!reader.IsDBNull(8))
{
flyto.final_bearing = reader.GetDouble(8);
}
if (flyto.final_bearing < 0)
{
flyto.final_bearing = (flyto.final_bearing + 360) % 360;
}
if (!reader.IsDBNull(0))
{
point.point_type = reader.GetString(0);
}
if (!reader.IsDBNull(1))
{
point.intent_set = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
point.what_3_words = reader.GetString(2);
}
if (!reader.IsDBNull(3))
{
point.report = reader.GetString(3);
}
if (!reader.IsDBNull(4))
{
point.photos = reader.GetString(4);
}
if (!reader.IsDBNull(5))
{
point.short_hash_id = reader.GetString(5);
}
if (!reader.IsDBNull(9))
{
point.power = reader.GetDouble(9);
}
if (!reader.IsDBNull(10))
{
point.z_score = reader.GetDouble(10);
}
if (!reader.IsDBNull(11))
{
point.nearest_place = reader.GetString(11);
}
if (!reader.IsDBNull(12))
{
point.country = reader.GetString(12);
}
if (!reader.IsDBNull(13))
{
point.datetime = reader.GetDateTime(13);
}
flytos.Add(flyto);
points.Add(point);
}
}
}
var flyTosAppender = new StringBuilder();
int pointNo = 1;
foreach (FlyTo flyto in flytos)
{
var var3 = (flyto.final_bearing + 360) % 360;
var var4 = (flyto.final_bearing + 360) % 360;
var var5 = (flyto.final_bearing + 360 + 90) % 360;
var var6 = (flyto.final_bearing + 360 + 180) % 360;
var var7 = (flyto.final_bearing + 360 + 270) % 360;
var var8 = (flyto.final_bearing + 360) % 360;
flyTosAppender.Append(string.Format(FLYTO_TEMPLATE, flyto.latitude, flyto.longitude, $"point{pointNo++}", var3, var4, var5, var6, var7, var8));
}
var pointsAppender = new StringBuilder();
pointNo = 1; // reset
foreach (Point point in points)
{
// Format content to show inside the balloon popup
var balloonString = new StringBuilder();
balloonString.Append("<![CDATA[");
if (!string.IsNullOrEmpty(point.nearest_place))
{
balloonString.Append($"in {point.nearest_place}, {point.country}<br>");
}
balloonString.Append($"@{point.latitude.ToString("G6")},{point.longitude.ToString("G6")} ({point.what_3_words})<br>");
balloonString.Append($"Power: {point.power.ToString("G3")}<br>");
balloonString.Append($"z-score: {point.z_score.ToString("G3")}<br>");
if (!string.IsNullOrEmpty(point.intent_set))
{
balloonString.Append($"Intent: {point.intent_set}<br>");
}
if (!string.IsNullOrEmpty(point.report))
{
balloonString.Append($"Report: {point.report}<br>");
}
if (!string.IsNullOrEmpty(point.photos))
{
var photos = point.photos.Split(",");
int i = 0;
foreach (string url in photos)
{
balloonString.Append($"<a href=\"{url}\">photo {++i}</a> ");
}
balloonString.Append("<br>");
}
balloonString.Append($"{point.short_hash_id}<br>");
balloonString.Append("]]>");
var dateTimeStr = "";
if (isMyRandotrips)
{
// TODO: one day figure out how to show My Randotrips in user's local timezone
dateTimeStr = $"{point.point_type} {point.datetime.ToString("yyyy-MM-dd HH':'mm")} UTC";
}
else
{
dateTimeStr = $"{point.point_type} {point.datetime.ToString("yyyy-MM-dd HH':'mm")} UTC";
}
var formatted = string.Format(POINT_TEMPLATE,
$"point{pointNo++}",
dateTimeStr,
balloonString.ToString(),
point.longitude,
point.latitude
);
pointsAppender.Append(formatted);
}
var output = string.Format(MAIN_TEMPLATE, filename.Replace(".kml", "").Replace("randotrips_", ""), flyTosAppender, pointsAppender);
System.IO.File.WriteAllText($"wwwroot/flythrus/{filename}", output);
return(pointNo);
}
}
catch (Exception e)
{
// My error handling is getting lazy
Console.Write(e);
return(-1);
}
}
public static void CreateDatabaseObjects(string connectionString, string schema, bool createDatabase = false)
{
if (createDatabase)
{
var builder = new SqlConnectionStringBuilder(connectionString);
var databaseName = builder.InitialCatalog;
builder.InitialCatalog = "master";
builder.AttachDBFilename = string.Empty;
using (var connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText =
string.Format(
CultureInfo.InvariantCulture,
@"IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'{0}') CREATE DATABASE [{0}];",
databaseName);
command.ExecuteNonQuery();
}
}
}
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText =
string.Format(
CultureInfo.InvariantCulture,
@"
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'{0}')
EXECUTE sp_executesql N'CREATE SCHEMA [{0}] AUTHORIZATION [dbo]';
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[Commands]') AND type in (N'U'))
CREATE TABLE [{0}].[Commands](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Body] [nvarchar](max) NOT NULL,
[DeliveryDate] [datetime] NULL,
[CorrelationId] [nvarchar](max) NULL,
[IsDeadLetter] [bit] NOT NULL,
[TraceInfo][nvarchar](max) NULL
CONSTRAINT [PK_{0}.Commands] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[Events]') AND type in (N'U'))
CREATE TABLE [{0}].[Events](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Body] [nvarchar](max) NOT NULL,
[DeliveryDate] [datetime] NULL,
[CorrelationId] [nvarchar](max) NULL,
[IsDeadLetter] [bit] NOT NULL,
[TraceInfo][nvarchar](max) NULL
CONSTRAINT [PK_{0}.Events] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
",
schema);
command.ExecuteNonQuery();
}
}
}
public virtual ActionResult Index(InstallModel model)
{
if (DataSettingsHelper.DatabaseIsInstalled())
{
return(RedirectToRoute("HomePage"));
}
//设置页面超时时间为5分钟
this.Server.ScriptTimeout = 300;
if (model.DatabaseConnectionString != null)
{
model.DatabaseConnectionString = model.DatabaseConnectionString.Trim();
}
foreach (var lang in _locService.GetAvailableLanguages())
{
model.AvailableLanguages.Add(new SelectListItem
{
Value = Url.Action("ChangeLanguage", "Install", new { language = lang.Code }),
Text = lang.Name,
Selected = _locService.GetCurrentLanguage().Code == lang.Code,
});
}
model.DisableSampleDataOption = _config.DisableSampleDataDuringInstallation;
//SQL Server
if (model.DataProvider.Equals("sqlserver", StringComparison.InvariantCultureIgnoreCase))
{
if (model.SqlConnectionInfo.Equals("sqlconnectioninfo_raw", StringComparison.InvariantCultureIgnoreCase))
{
if (string.IsNullOrEmpty(model.DatabaseConnectionString))
{
ModelState.AddModelError("", _locService.GetResource("ConnectionStringRequired"));
}
try
{
new SqlConnectionStringBuilder(model.DatabaseConnectionString);
}
catch
{
ModelState.AddModelError("", _locService.GetResource("ConnectionStringWrongFormat"));
}
}
else
{
if (string.IsNullOrEmpty(model.SqlServerName))
{
ModelState.AddModelError("", _locService.GetResource("SqlServerNameRequired"));
}
if (string.IsNullOrEmpty(model.SqlDatabaseName))
{
ModelState.AddModelError("", _locService.GetResource("DatabaseNameRequired"));
}
if (model.SqlAuthenticationType.Equals("sqlauthentication", StringComparison.InvariantCultureIgnoreCase))
{
if (string.IsNullOrEmpty(model.SqlServerUsername))
{
ModelState.AddModelError("", _locService.GetResource("SqlServerUsernameRequired"));
}
if (string.IsNullOrEmpty(model.SqlServerPassword))
{
ModelState.AddModelError("", _locService.GetResource("SqlServerPasswordRequired"));
}
}
}
}
//验证是否有特定文件夹与文件操作权限
var dirsToCheck = FilePermissionHelper.GetDirectoriesWrite();
foreach (string dir in dirsToCheck)
{
if (!FilePermissionHelper.CheckPermissions(dir, false, true, true, false))
{
ModelState.AddModelError("", string.Format(_locService.GetResource("ConfigureDirectoryPermissions"), WindowsIdentity.GetCurrent().Name, dir));
}
}
var filesToCheck = FilePermissionHelper.GetFilesWrite();
foreach (string file in filesToCheck)
{
if (!FilePermissionHelper.CheckPermissions(file, false, true, true, true))
{
ModelState.AddModelError("", string.Format(_locService.GetResource("ConfigureFilePermissions"), WindowsIdentity.GetCurrent().Name, file));
}
}
if (ModelState.IsValid)
{
var webHelper = EngineContext.Current.Resolve <IWebHelper>();
var settingsManager = new DataSettingsManager();
try
{
string connectionString;
if (model.DataProvider.Equals("sqlserver", StringComparison.InvariantCultureIgnoreCase))
{
//SQL Server
if (model.SqlConnectionInfo.Equals("sqlconnectioninfo_raw", StringComparison.InvariantCultureIgnoreCase))
{
var sqlCsb = new SqlConnectionStringBuilder(model.DatabaseConnectionString);
if (this.UseMars)
{
sqlCsb.MultipleActiveResultSets = true;
}
connectionString = sqlCsb.ToString();
}
else
{
connectionString = CreateConnectionString(model.SqlAuthenticationType == "windowsauthentication",
model.SqlServerName, model.SqlDatabaseName,
model.SqlServerUsername, model.SqlServerPassword);
}
if (model.SqlServerCreateDatabase)
{
if (!SqlServerDatabaseExists(connectionString))
{
//create database
var collation = model.UseCustomCollation ? model.Collation : "";
var errorCreatingDatabase = CreateDatabase(connectionString, collation);
if (!String.IsNullOrEmpty(errorCreatingDatabase))
{
throw new Exception(errorCreatingDatabase);
}
}
}
else
{
if (!SqlServerDatabaseExists(connectionString))
{
throw new Exception(_locService.GetResource("DatabaseNotExists"));
}
}
}
else
{
//SQL CE
string databaseFileName = "Ransurotto.Db.sdf";
string databasePath = @"|DataDirectory|\" + databaseFileName;
connectionString = "Data Source=" + databasePath + ";Persist Security Info=False";
string databaseFullPath = CommonHelper.MapPath("~/App_Data/") + databaseFileName;
if (System.IO.File.Exists(databaseFullPath))
{
System.IO.File.Delete(databaseFullPath);
}
}
//保存数据库连接设定
var dataProvider = model.DataProvider;
var settings = new DataSettings
{
DataProvider = dataProvider,
DataConnectionString = connectionString
};
settingsManager.SaveSettings(settings);
//初始化数据提供商
var dataProviderInstance = EngineContext.Current.Resolve <BaseDataProviderManager>().LoadDataProvider();
dataProviderInstance.InitDatabase();
//添加数据
var installationService = EngineContext.Current.Resolve <IInstallationService>();
installationService.InstallData(model.AdminEmail, model.AdminPassword, model.InstallSampleData);
//TODO 安装插件
//安装权限
var permissionProviders = new List <Type>();
permissionProviders.Add(typeof(StandardPermissionProvider));
foreach (var providerType in permissionProviders)
{
dynamic provider = Activator.CreateInstance(providerType);
EngineContext.Current.Resolve <IPermissionService>().InstallPermissions(provider);
}
//清空缓存
DataSettingsHelper.ResetCache();
//重启应用程序
webHelper.RestartAppDomain();
//跳转至主页
return(RedirectToRoute("HomePage"));
}
catch (Exception ex)
{
DataSettingsHelper.ResetCache();
var cacheManager = EngineContext.Current.ContainerManager.Resolve <ICacheManager>("ransurotto_cache_static");
cacheManager.Clear();
settingsManager.SaveSettings(new DataSettings
{
DataProvider = null,
DataConnectionString = null
});
ModelState.AddModelError("", string.Format(_locService.GetResource("SetupFailed"), ex.Message));
}
}
return(View(model));
}
/// <summary>
/// Builds a connection string for Microsoft SQL Server databases using the parameters given.
/// </summary>
/// <param name="serverName">The server's name or IP address.</param>
/// <param name="databaseName">The database name.</param>
/// <param name="user">If not null, the user to use for the connection.</param>
/// <param name="password">If user is not null, the password to use to authenticate this user.</param>
/// <param name="library">The SQL library to use.</param>
/// <param name="persistSecurityInfo">True to persist security info.</param>
/// <param name="multipleActiveResultSets">True to activate support for multiple simultaneous results sets.</param>
/// <returns>The connection string built using the parameters given.</returns>
public static string BuildConnectionString(
string serverName, string databaseName,
string user = null, string password = null,
SQLNetworkLibrary library = SQLNetworkLibrary.TcpIP,
bool persistSecurityInfo = false,
bool multipleActiveResultSets = true )
{
serverName = serverName.Validated( "Server's name or IP address" );
databaseName = databaseName.Validated( "Database's name" );
// The basic is with integrated security...
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.DataSource = serverName;
sb.InitialCatalog = databaseName;
sb.PersistSecurityInfo = persistSecurityInfo;
sb.NetworkLibrary = library.LibraryIdentifier();
// Using explicit (user / password) security...
if( user != null ) {
user = user.Validated( "User's login" );
sb.IntegratedSecurity = false;
sb.UserID = user;
if( !string.IsNullOrEmpty( password ) ) sb.Password = password;
}
else sb.IntegratedSecurity = true; // Or using integrated security mode
// Allowing multiple result sets if requested
if( multipleActiveResultSets ) sb.MultipleActiveResultSets = true;
// Returning...
return sb.ToString();
}
private void LoadData(string discipline)
{
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "IRINAMESH";
builder.InitialCatalog = "bigData2020";
builder.IntegratedSecurity = true;
List <string[]> data = new List <string[]>();
List <string[]> coord = new List <string[]>();
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
StringBuilder sb = new StringBuilder();
sb.Append("select * ");
sb.Append("from (select top 5000 Учебное_Заведение, Поступающие.Город, count(Поступающие.ID) as [Число_поступающих], avg(Балл) as [Средний балл], Широта, Долгота ");
sb.Append("from Оценки ");
sb.Append("inner join Поступающие on Оценки.ID = Поступающие.ID ");
sb.Append("inner join Города on Города.Город = Поступающие.Город ");
sb.Append("where Оценки.Предмет = '");
sb.Append(discipline + "' ");
sb.Append("group by Учебное_Заведение, Поступающие.Город, Широта, Долгота ");
sb.Append("order by Город, Учебное_Заведение desc) as Таблица ");
sb.Append("order by Таблица.[Средний балл] desc;");
String sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
data.Add(new string[6]);
data[data.Count - 1][0] = reader[0].ToString(); // школа
data[data.Count - 1][1] = reader[1].ToString(); // город
data[data.Count - 1][2] = reader[2].ToString(); // число поступающих
data[data.Count - 1][3] = reader[3].ToString(); // балл
data[data.Count - 1][4] = reader[4].ToString(); // широта
data[data.Count - 1][5] = reader[5].ToString(); // долгота
}
reader.Close();
}
}
}
FillDataGrid(data);
}
catch (SqlException e)
{
string filename = "";
string filepath = @"..\..\..\..\CsvFiles\forScatters";
MessageBox.Show(e.ToString());
switch (discipline)
{
case "Физика":
filename = @"\phys.csv";
break;
case "Русский язык":
filename = @"\rus.csv";
break;
case "Математика":
filename = @"\math.csv";
break;
case "Информатика и ИКТ":
filename = @"\ikt.csv";
break;
case "Обществознание":
filename = @"\social.csv";
break;
case "Химия":
filename = @"\chemistry.csv";
break;
case "Творческий конкурс":
filename = @"\creation.csv";
break;
case "География":
filename = @"\geo.csv";
break;
}
filepath += filename;
using (var sr = new StreamReader(filepath))
{
int i = 0;
string line;
while ((line = sr.ReadLine()) != null)
{
//dataGridView1.Columns[i].HeaderCell.Style.Font = new System.Drawing.Font("Microsoft Sans Serif", 18);
try
{
var array = line.Split(';');
dataGridView1.Rows.Add();
dataGridView1.Rows[i].Cells[0].Value = array[0];
dataGridView1.Rows[i].Cells[1].Value = array[1];
dataGridView1.Rows[i].Cells[2].Value = DistanceCount(array[4], array[5]);
dataGridView1.Rows[i].Cells[3].Value = Convert.ToInt32(array[2]);
dataGridView1.Rows[i].Cells[4].Value = array[3];
i++;
}
catch (Exception)
{
Console.WriteLine("Некорректный файл");
}
}
}
}
}
public SqlServerConnectionStringBuilder(string connectionString)
{
instance = new SqlConnectionStringBuilder(connectionString);
}
private static void PrepareConnectionStrings(string originalString, out string tcpString, out string tcpMarsString, out string npString, out string npMarsString)
{
SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(originalString);
DataSourceBuilder sourceBuilder = new DataSourceBuilder(connBuilder.DataSource);
sourceBuilder.Protocol = null;
// TCP
connBuilder.DataSource = sourceBuilder.ToString();
connBuilder.MultipleActiveResultSets = false;
tcpString = connBuilder.ConnectionString;
// TCP + MARS
connBuilder.MultipleActiveResultSets = true;
tcpMarsString = connBuilder.ConnectionString;
// Named Pipes
sourceBuilder.Port = null;
connBuilder.DataSource = "np:" + sourceBuilder.ToString();
connBuilder.MultipleActiveResultSets = false;
npString = connBuilder.ConnectionString;
// Named Pipes + MARS
connBuilder.MultipleActiveResultSets = true;
npMarsString = connBuilder.ConnectionString;
}
private static SqlErrorCollection CreateCollection()
{
var builder = new SqlConnectionStringBuilder()
{
DataSource = badServer,
ConnectTimeout = 1,
Pooling = false
};
using (var connection = new SqlConnection(builder.ConnectionString))
{
try
{
connection.Open();
}
catch (SqlException ex)
{
Assert.NotNull(ex.Errors);
Assert.Equal(1, ex.Errors.Count);
return ex.Errors;
}
}
throw new InvalidOperationException("SqlException.Errors should have been returned.");
}
public Tuple <string, int> StaffLuckyDraw(int eventID)
{
Models.User user = new Models.User();
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "luckydrawapplication20200108092548dbserver.database.windows.net";
builder.UserID = "sqladmin";
builder.Password = "******";
builder.InitialCatalog = "luckywheeldb";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT TOP 1 * FROM users WHERE users.EventID = " + eventID + " AND users.StaffWon = 0 ORDER BY RAND()");
String sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
using (SqlDataReader rd = command.ExecuteReader())
{
while (rd.Read())
{
user.PurchaserID = Convert.ToInt32(rd["PurchaserID"].ToString());
user.Name = rd["Name"].ToString();
user.ICNumber = rd["ICNumber"].ToString();
user.EmailAddress = rd["EmailAddress"].ToString();
user.ContactNumber = rd["ContactNumber"].ToString();
user.EventID = Convert.ToInt32(rd["EventID"].ToString());
user.ProjectID = Convert.ToInt32(rd["ProjectID"].ToString());
user.Unit = rd["Unit"].ToString();
user.SalesConsultant = rd["SalesConsultant"].ToString();
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
int orderNo = 0;
int prizeAmount = 0;
int won = 0;
if (user.SalesConsultant != null || user.SalesConsultant != "")
{
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "luckydrawapplication20200108092548dbserver.database.windows.net";
builder.UserID = "sqladmin";
builder.Password = "******";
builder.InitialCatalog = "luckywheeldb";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT TOP 1 * FROM agent WHERE won = 0 ORDER BY RAND()");
String sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
using (SqlDataReader rd = command.ExecuteReader())
{
while (rd.Read())
{
orderNo = Convert.ToInt32(rd["orderNo"]);
prizeAmount = Convert.ToInt32(rd["prizeAmount"]);
won = Convert.ToInt32(rd["won"]);
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
UpdateDatabaseAfterStaffWon(user, orderNo, prizeAmount);
}
return(new Tuple <String, int>(user.SalesConsultant, prizeAmount));
}
public static string GetDatabaseNameFromConnectionString(this string connectionString)
{
var connectionStringObject = new SqlConnectionStringBuilder(connectionString);
return(connectionStringObject.InitialCatalog);
}
public static List <Models.User> GetWinnerList(int eventID)
{
List <Models.User> UserList = new List <Models.User>();
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "luckydrawapplication20200108092548dbserver.database.windows.net";
builder.UserID = "sqladmin";
builder.Password = "******";
builder.InitialCatalog = "luckywheeldb";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT users.*, project.ProjectName, project.PrizeCategory, event.EventLocation FROM users INNER JOIN project on project.ProjectID = users.ProjectID INNER JOIN event ON event.EventID = users.EventID WHERE PrizeWon > 0 AND users.EventID = " + eventID);
String sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
using (SqlDataReader rd = command.ExecuteReader())
{
while (rd.Read())
{
Models.User user = new Models.User();
user.PurchaserID = Convert.ToInt32(rd["PurchaserID"].ToString());
user.Name = rd["Name"].ToString();
user.ICNumber = rd["ICNumber"].ToString();
user.EmailAddress = rd["EmailAddress"].ToString();
user.ContactNumber = rd["ContactNumber"].ToString();
user.EventID = Convert.ToInt32(rd["EventID"].ToString());
user.ProjectID = Convert.ToInt32(rd["ProjectID"].ToString());
user.ProjectName = rd["ProjectName"].ToString();
user.SalesLocation = rd["EventLocation"].ToString();
user.Unit = rd["Unit"].ToString();
user.SalesConsultant = rd["SalesConsultant"].ToString();
if (Convert.ToInt32(rd["PrizeWon"]) > 0)
{
string[] prizes = rd["PrizeCategory"].ToString().Split(',');
user.PrizeWon = Convert.ToInt32(prizes[Convert.ToInt32(rd["PrizeWon"]) - 1]);
}
else
{
user.PrizeWon = 0;
}
user.DateTime = rd["DateTime"].ToString();
UserList.Add(user);
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
return(UserList);
}
public SqlConfiguration(SqlConnectionStringBuilder builder)
{
Verify.IsNotNull(nameof(builder), builder);
ConnectionString = builder.ToString();
}
protected void RaporOlustur(string Format)
{
EFDal ed = new EFDal();
report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
string RaporAdi = Request["RaporAdi"].ToString() + ".rpt";
//string RaporAd = Server.MapPath("..\\..\\Rapor\\" + RaporAdi);
string RaporAd = Server.MapPath("Rapor\\" + RaporAdi);
report.Load(RaporAd);
if (RaporAdi == "SertifikaKapak.rpt")//turkak
{
//100476479
report.SetParameterValue("SayfaSayisi", Request["SayfaSayisi"].ToString());
report.SetParameterValue("@IstID", Request["IstId"].ToString());
report.SetParameterValue("OnayTarihi", Request["OnayTarihi"].ToString().Replace(" 00:00:00", ""));
report.SetParameterValue("CihazAdi", Request["CihazAdi"].ToString());
//report.SetParameterValue("QRCodeImagePath", Session["QRCodeImagePath"].ToString());
//report.SetParameterValue("QRCodeImagePath", QRCodeOlustur());
//report.SetParameterValue("Url", "Url");
Session["DokumanSaklanacakIstId"] = Request["SayfaSayisi"].ToString();
}
if (RaporAdi == "SertifikaKapakTSE.rpt")//tse
{
//100476479
report.SetParameterValue("SayfaSayisi", Request["SayfaSayisi"].ToString());
report.SetParameterValue("@IstID", Request["IstId"].ToString());
report.SetParameterValue("OnayTarihi", Request["OnayTarihi"].ToString().Replace(" 00:00:00", ""));
report.SetParameterValue("CihazAdi", Request["CihazAdi"].ToString());
Session["DokumanSaklanacakIstId"] = Request["SayfaSayisi"].ToString();
//report.SetParameterValue("QRCodeImagePath", QRCodeOlustur());
//report.SetParameterValue("Url", "Url");
}
if (RaporAdi == "SertifikaKapakEImza.rpt")//turkak
{
//100476479
QRCodeOlustur();
report.SetParameterValue("SayfaSayisi", Request["SayfaSayisi"].ToString());
report.SetParameterValue("@IstID", Request["IstId"].ToString());
report.SetParameterValue("OnayTarihi", Request["OnayTarihi"].ToString().Replace(" 00:00:00", ""));
report.SetParameterValue("CihazAdi", Request["CihazAdi"].ToString());
//report.SetParameterValue("QRCodeImagePath", Session["QRCodeImagePath"].ToString());
//report.SetParameterValue("QRCodeImagePath", QRCodeOlustur());
report.SetParameterValue("Url", Session["qrCodeURL"].ToString());
Session["DokumanSaklanacakIstId"] = Request["SayfaSayisi"].ToString();
}
if (RaporAdi == "SertifikaKapakTSEEImza.rpt")//tse
{
//100476479
QRCodeOlustur();
report.SetParameterValue("SayfaSayisi", Request["SayfaSayisi"].ToString());
report.SetParameterValue("@IstID", Request["IstId"].ToString());
report.SetParameterValue("OnayTarihi", Request["OnayTarihi"].ToString().Replace(" 00:00:00", ""));
report.SetParameterValue("CihazAdi", Request["CihazAdi"].ToString());
Session["DokumanSaklanacakIstId"] = Request["SayfaSayisi"].ToString();
//report.SetParameterValue("QRCodeImagePath", QRCodeOlustur());
report.SetParameterValue("Url", Session["qrCodeURL"].ToString());
}
if (RaporAdi == "CihazKayit.rpt")
{
//report.SetParameterValue("Sifre", "bakalim");
string IstekNo = Request["IstekNo"].ToString();
int BolgeKodu = ed.kal_BolgeKoduDon(Context.User.Identity.Name);
int Yil = int.Parse(Request["Yil"].ToString());
int FirmaId = int.Parse(Request["FirmaId"].ToString());
report.RecordSelectionFormula = "{vCihazKabul.SILINDI}=false and {vCihazKabul.ISTEKNO}=" + IstekNo + " and {vCihazKabul.BOLGEKODU}=" + BolgeKodu.ToString() + " and {vCihazKabul.YIL}=" + Yil.ToString() + " and {vCihazKabul.FIRMAID}=" + FirmaId.ToString();
//report.RecordSelectionFormula = "{vCihazKabul.YIL}=9 and {vCihazKabul.ISTEKNO}=549 and {vCihazKabul.BOLGEKODU}=2";
}
if (RaporAdi == "GorevOluru.rpt")
{
string GorevId = Request["GorevId"].ToString();
//report.RecordSelectionFormula = "{GorevPersonel.GorevID}=" + GorevId ;
report.SetParameterValue("AracPlakasi", Session["RaporIcinAracPlakasi"].ToString());
report.SetParameterValue("GorevId", GorevId);
Session.Remove("RaporIcinAracPlakasi");
}
if (RaporAdi == "TeklifYeni.rpt")
{
lblUyari.Visible = false;
int BolgeKodu = ed.kal_BolgeKoduDon(Context.User.Identity.Name);
string TeklifNo = Request["TeklifNo"].ToString();
string Telefon = ed.TeklifNodanFirmaninTelefonunuDon(TeklifNo, BolgeKodu);
if (Telefon == "Neg")//Muhtemelen firma kaydı firma tablosuna yapilmamis
{
lblUyari.Text = "<b><u>UYARI:</u></b><br/>Muhtemel olarak firma tablosunda firma kaydı bulunamadı. Ya da firmaya ait telefon bilgisi girilmemiş.Lütfen kontrol ediniz..";
lblUyari.Visible = true;
return;
}
else
{
report.RecordSelectionFormula = "{teklif.TEKLIFNO}='" + TeklifNo + "' and {teklif.BOLGEKODU}=" + BolgeKodu.ToString() + " and {birim.BOLGEKODU}=" + BolgeKodu.ToString() + " and {birim.NEICIN}='teklif'";
//report.SetParameterValue("KurumTipi", "Kamu");
//report.SetParameterValue("Telefon", "4545");
}
}
if (RaporAdi == "TeklifYeniIndirimli.rpt")
{
lblUyari.Visible = false;
int BolgeKodu = ed.kal_BolgeKoduDon(Context.User.Identity.Name);
string TeklifNo = Request["TeklifNo"].ToString();
string Telefon = ed.TeklifNodanFirmaninTelefonunuDon(TeklifNo, BolgeKodu);
decimal IndirimOrani = 0;
if (Telefon == "Neg")//Muhtemelen firma kaydı firma tablosuna yapilmamis
{
lblUyari.Text = "<b><u>UYARI:</u></b><br/>Muhtemel olarak firma tablosunda firma kaydı bulunamadı. Ya da firmaya ait telefon bilgisi girilmemiş.Lütfen kontrol ediniz..";
lblUyari.Visible = true;
return;
}
else
{
report.RecordSelectionFormula = "{teklif.TEKLIFNO}='" + TeklifNo + "' and {teklif.BOLGEKODU}=" + BolgeKodu.ToString() + " and {birim.BOLGEKODU}=" + BolgeKodu.ToString() + " and {birim.NEICIN}='teklif'";
using (Service1Client client = new Service1Client())
{
IndirimOrani = client.TeklifNodanIndirimOraniniDon(BolgeKodu, TeklifNo);
}
report.SetParameterValue("IndirimOrani", IndirimOrani);
//report.SetParameterValue("Telefon", "4545");
}
}
if (RaporAdi == "TeklifYeniOnYazisiz.rpt")
{
int BolgeKodu = ed.kal_BolgeKoduDon(Context.User.Identity.Name);
string TeklifNo = Request["TeklifNo"].ToString();
string Telefon = ed.TeklifNodanFirmaninTelefonunuDon(TeklifNo, BolgeKodu);
report.RecordSelectionFormula = "{teklif.TEKLIFNO}='" + TeklifNo + "' and {teklif.BOLGEKODU}=" + BolgeKodu.ToString() + " and {birim.BOLGEKODU}=" + BolgeKodu.ToString() + " and {birim.NEICIN}='teklif'";
report.SetParameterValue("KurumTipi", "Kamu");
report.SetParameterValue("Telefon", Telefon);
}
if (RaporAdi == "FaturaOnYaziDetayli.rpt")
{
report.SetParameterValue("IstIdler", Session["FaturaOnYaziIcinVirgulleAyrilmisIstIdler"].ToString());
report.SetParameterValue("PaketNolari", Session["FaturaOnYaziIcinVirgulleAyrilmisPaketNolar"].ToString());
report.SetParameterValue("PaketBedelleri", decimal.Parse(Session["PaketBedelleri"].ToString()));
report.SetParameterValue("PaketKapsamindaOlmayanlariGizle", "H");
if (Session["PaketKapsamindaOlmayanlariGizle"] == "E")
{
report.SetParameterValue("PaketKapsamindaOlmayanlariGizle", "E");
Session["PaketKapsamindaOlmayanlariGizle"] = null;
}
}
if (RaporAdi == "FaturaOnYazi.rpt")
{
report.SetParameterValue("IstIdler", Session["FaturaOnYaziIcinVirgulleAyrilmisIstIdler"].ToString());
report.SetParameterValue("MasrafIncelemeUcreti", Session["DetaysizMasrafIncelemeUcreti"].ToString());
report.SetParameterValue("PostaHavale", Session["DetaysizPostaBedeli"].ToString());
/*
* DataSet ds=new DataSet();
* ds=ed.Sorgula("SELECT top 1 dbo.firma.MUSTNO, dbo.firma.FIRMAADI, dbo.firma.ADRESI, dbo.firma.ADRESII, dbo.firma.IL_ILCE, dbo.firma.TELEFON, dbo.firma.FAX, dbo.firma.ILGILI, "+
* " dbo.firma.EMAIL, dbo.firma.NOTLAR, dbo.firma.FATADRES, dbo.firma.VERGIDAIRESI, dbo.firma.VERGINO, dbo.firma.BOLGEKODU, dbo.firma.SILINDI, "+
* " dbo.istek.FIRMAID, dbo.istek.YIL, dbo.istek.ISTEKNO, dbo.istek.ALTISTNO, dbo.istek.CIHAZADI, dbo.istek.IMALATCI, dbo.istek.MODEL, dbo.istek.SERINO, "+
* " dbo.istek.OLCUMARALIGI, dbo.istek.ILGLAB, dbo.istek.SORUMLU, dbo.istek.FATBEDEL, dbo.istek.IPTAL, dbo.istek.BOLGEKODU AS isBolgeKodu, dbo.istek.SILINDI AS isSilindi, "+
* " dbo.istek.FATURASIRA, dbo.istek.CIHAZKAPSAMAYRINTITABLOSU, dbo.FaturaOnYazi.ID, dbo.FaturaOnYazi.ISTID, dbo.FaturaOnYazi.BOLGEKODU AS FoyBolgeKodu, "+
* " dbo.FaturaOnYazi.Silindi AS foySilindi, dbo.FaturaOnYazi.FATURANO, dbo.FaturaOnYazi.GUNLUKSIRA,dbo.FaturaOnYazi.EkBolgemi "+
* " FROM dbo.istek INNER JOIN "+
* " dbo.firma ON dbo.istek.FIRMAID = dbo.firma.FIRMAID INNER JOIN "+
* " dbo.FaturaOnYazi ON dbo.istek.ISTID = dbo.FaturaOnYazi.ISTID "+
* " where FaturaOnYazi.ISTID in ("+Session["FaturaOnYaziIcinVirgulleAyrilmisIstIdler"].ToString() +")");
*
* report.SetDataSource(ds);
*/
}
if (RaporAdi == "FaturaOnYaziYeni.rpt")
{
int BolgeKodu = ed.kal_BolgeKoduDon(Context.User.Identity.Name);
report.SetParameterValue("BolgeKodu", Request["BolgeKodu"].ToString());
report.SetParameterValue("FaturaNo", Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
using (Service1Client proxy = new Service1Client())
{
decimal MasrafIncelemeUcreti = proxy.FaturaNoIcinMasrafIncelemeBedeliToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
decimal EkBolgeUcretiToplami = proxy.FaturaNoIcinIcinEkBolgeToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
decimal PostaBedeli = proxy.FaturaNoIcinPostaBedeliToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
report.SetParameterValue("MasrafIncelemeToplami", MasrafIncelemeUcreti);
report.SetParameterValue("PostaBedeli", PostaBedeli);
report.SetParameterValue("EkBolgeToplami", EkBolgeUcretiToplami);
/*
* //Burada rapurun daha az yer tutar,sıkışık görünemsi için EkBolgeler subreportunu kapatabiliriz eğer EkBolgeUcretiToplami == 0 ise
* if (EkBolgeUcretiToplami == 0)
* {
* report.SetParameterValue("IstegeAitEkBolgelerSubReportuGorunsunmu", false);
* }
*/
Session["FaturaIcerigindenRaporaGidenFaturaNo"] = null;
}
}
//if (RaporAdi == "FaturaOnYaziYeniProtokolBilgili.rpt")
//{
// int BolgeKodu = ed.kal_BolgeKoduDon(Context.User.Identity.Name);
// using (Service1Client proxy = new Service1Client())
// {
// decimal MasrafIncelemeUcreti = proxy.FaturaNoIcinMasrafIncelemeBedeliToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
// decimal EkBolgeUcretiToplami = proxy.FaturaNoIcinIcinEkBolgeToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
// decimal PostaBedeli = proxy.FaturaNoIcinPostaBedeliToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
// decimal IndirimOrani = decimal.Parse(Request["IndirimOrani"].ToString());
// report.SetParameterValue("YurtDisindanmi", false);
// report.SetParameterValue("BolgeKodu", Request["BolgeKodu"].ToString());
// report.SetParameterValue("FaturaNo", Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
// report.SetParameterValue("EkBolgeToplami", EkBolgeUcretiToplami);
// report.SetParameterValue("MasrafIncelemeToplami", MasrafIncelemeUcreti);
// report.SetParameterValue("PostaBedeli", PostaBedeli);
// report.SetParameterValue("IndirimOrani", IndirimOrani);
// /*
// //Burada rapurun daha az yer tutar,sıkışık görünemsi için EkBolgeler subreportunu kapatabiliriz eğer EkBolgeUcretiToplami == 0 ise
// if (EkBolgeUcretiToplami == 0)
// {
// report.SetParameterValue("IstegeAitEkBolgelerSubReportuGorunsunmu", false);
// }
// */
// }
//}
if (RaporAdi == "FaturaOnYaziYeniProtokolBilgili.rpt")
{
int BolgeKodu = ed.kal_BolgeKoduDon(Context.User.Identity.Name);
using (Service1Client proxy = new Service1Client())
{
//decimal MasrafIncelemeUcreti = proxy.FaturaNoIcinMasrafIncelemeBedeliToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
decimal MasrafIncelemeUcreti = ed.FaturaNoIcinMasrafIncelemeBedeliToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
decimal EkBolgeUcretiToplami = proxy.FaturaNoIcinIcinEkBolgeToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
decimal PostaBedeli = ed.FaturaNoIcinIcinPostaBedeliToplamlari(BolgeKodu, Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
decimal IndirimOrani = decimal.Parse(Request["IndirimOrani"].ToString());
report.SetParameterValue("@BolgeKodu", Request["BolgeKodu"].ToString());
report.SetParameterValue("@FaturaNo", Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString());
report.SetParameterValue("MasrafIncelemeToplami", MasrafIncelemeUcreti);
report.SetParameterValue("PostaBedeli", PostaBedeli);
report.SetParameterValue("EkBolgeToplami", EkBolgeUcretiToplami);
report.SetParameterValue("IndirimOrani", IndirimOrani);
report.SetParameterValue("@BolgeKodu", Request["BolgeKodu"].ToString(), "MasrafveInceleme");
report.SetParameterValue("@FaturaNo", Session["FaturaIcerigindenRaporaGidenFaturaNo"].ToString(), "MasrafveInceleme");
}
}
if (RaporAdi == "FirmaEtiketi.rpt")
{
report.SetParameterValue("BolgeKodu", Request["BolgeKodu"].ToString());
report.SetParameterValue("FirmaId", Request["FirmaId"].ToString());
}
if (RaporAdi == "EgitimSertifikasi.rpt")
{
int EgtBolgeKodu = 0;
report.SetParameterValue("@EgitimId", Request["EgitimId"].ToString());
using (Service1Client proxy = new Service1Client())
{
//report.SetParameterValue("EgitimiVerenler", proxy.EgitimiVerenlerinIsimleriniVirgulleAyrilmisOlarakDon(int.Parse(Request["EgitimId"].ToString())));
report.SetParameterValue("EgitimiVerenler", ed.kal_BelirliEgitimiVerenListesiDon2(int.Parse(Request["EgitimId"].ToString())));
report.SetParameterValue("EgitimAdi", proxy.EgitimIddenEgitimAdiDon(int.Parse(Request["EgitimId"].ToString())));
using (Service1Client proxyy = new Service1Client())
{
EgtBolgeKodu = proxyy.BolgeKoduDon(Context.User.Identity.Name);
}
if (EgtBolgeKodu == 1)//Gebze
{
report.SetParameterValue("ImzaAd", "Ali Can Kuyrukluyıldız");
report.SetParameterValue("ImzaStatu", "Gebze Kalibrasyon Müdürü");
}
if (EgtBolgeKodu == 2)//Ankara
{
report.SetParameterValue("ImzaAd", "Abdulmuttalip Aktoğ");
report.SetParameterValue("ImzaStatu", "Kalibrasyon Grup Başkanı");
}
if (EgtBolgeKodu == 3)//Bursa
{
report.SetParameterValue("ImzaAd", "Ömer EYYUPOĞLU");
report.SetParameterValue("ImzaStatu", "Bursa İl Koordinatörü");
}
}
}
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["KalData"].ToString();
CrystalDecisions.Shared.TableLogOnInfo tliCurrent;
foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in report.Database.Tables)
{
tliCurrent = tbl.LogOnInfo;
tliCurrent.ConnectionInfo.ServerName = sb.DataSource;
tliCurrent.ConnectionInfo.UserID = sb.UserID;
tliCurrent.ConnectionInfo.Password = sb.Password;
tliCurrent.ConnectionInfo.DatabaseName = sb.InitialCatalog;
tbl.ApplyLogOnInfo(tliCurrent);
}
if (Format == "PDF")
{
if (Session["eImzaliOlacak"] == null)
{
this.ExportReportPdfEImzaYok();
}
else
{
this.ExportReportPdf();
}
}
if (Format == "Word")
{
this.ExportReportWord();
}
CrystalReportViewer1.ReportSource = report;
//Rapor üretildikten hemen sonra faturalandırılan istekler tablosuna yaz
if (RaporAdi == "FaturaOnYaziYeniProtokolBilgili.rpt")
{
using (Service1Client proxy = new Service1Client())
{
Session["FaturaIcerigindenRaporaGidenFaturaNo"] = null;
string[] IstIdler = (string[])Session["FaturalandirildiOlarakIsaretlenecekIstIdler"];
foreach (var item in IstIdler)
{
try
{
proxy.FaturalandirilanIsteklereEkle(int.Parse(item));
}
catch
{
//RadNotification1.Text = " <font color='red'> "+item +" istekId li cihaz faturası yazılanlar tablosuna eklenemedi! Bu, cihazın birden fazla faturalandırılmasına yol acabilir. Lütfen istekId sini not alip sistem yöneticisine bildiriniz.</font>"; ;
// RadNotification1.Show();
}
}
}
}
}
static void Main(string[] args)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
{
};
SqlConnection connection = new SqlConnection(builder.ToString());
connection.Open();
using (connection)
{
try
{
SqlCommand cmdDB = new SqlCommand("CREATE DATABASE MinionsDB", connection);
cmdDB.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
builder[""] = "";
connection = new SqlConnection(builder.ToString());
connection.Open();
using (connection)
{
try
{
string sqlCreateTableCountries =
"CREATE TABLE Countries(Id INT PRIMARY KEY IDENTITY,Name VARCHAR(50))";
string sqlCreateTableTowns =
"CREATE TABLE Towns(Id INT PRIMARY KEY IDENTITY,Name VARCHAR(50),CountryId INT FOREIGN KEY REFERENCES Countries(Id) NOT NULL)";
string sqlCreateTableMinions =
"CREATE TABLE Minions(Id INT PRIMARY KEY IDENTITY,Name VARCHAR(50),Age INT,TownId INT FOREIGN KEY REFERENCES Towns(Id))";
string sqlCreateTableEvilnessFactors =
"CREATE TABLE EvilnessFactors(Id INT PRIMARY KEY ,Name VARCHAR(10) UNIQUE NOT NULL)";
string sqlCreateTableVillains =
"CREATE TABLE Villains(Id INT PRIMARY KEY IDENTITY,Name VARCHAR(50),EvilnessFactorId INT FOREIGN KEY REFERENCES EvilnessFactors(Id))";
string sqlCreateTableMinionsVillains =
"CREATE TABLE MinionsVillains(MinionId INT FOREIGN KEY REFERENCES Minions(Id),VillainId INT FOREIGN KEY REFERENCES Villains(Id))";
ExecuteCommand(sqlCreateTableCountries, connection);
ExecuteCommand(sqlCreateTableTowns, connection);
ExecuteCommand(sqlCreateTableMinions, connection);
ExecuteCommand(sqlCreateTableEvilnessFactors, connection);
ExecuteCommand(sqlCreateTableVillains, connection);
ExecuteCommand(sqlCreateTableMinionsVillains, connection);
string insertCountriesSQL =
"INSERT INTO Countries VALUES ('Bulgaria'), ('United Kingdom'), ('United States of America'), ('France')";
string insertTownsSQL =
"INSERT INTO Towns (Name, CountryId) VALUES ('Sofia',1), ('Burgas',1), ('Varna', 1), ('London', 2),('Liverpool', 2),('Ocean City', 3),('Paris', 4)";
string insertMinionsSQL =
"INSERT INTO Minions (Name, Age, TownId) VALUES ('bob',10,1),('kevin',12,2),('stuart',9,3), ('rob',22,3), ('michael',5,2),('pep',3,2)";
string insertEvilnessFactorsSQL =
"INSERT INTO EvilnessFactors VALUES (1, 'Super Good'), (2, 'Good'), (3, 'Bad'), (4, 'Evil'), (5, 'Super Evil')";
string insertVillainsSQL =
"INSERT INTO Villains (Name, EvilnessFactorId) VALUES ('Gru', 2),('Victor', 4),('Simon Cat', 3),('Pusheen', 1),('Mammal', 5)";
string insertMinionsVillainsSQL =
"INSERT INTO MinionsVillains VALUES (1, 2), (3, 1), (1, 3), (3, 3), (4, 1), (2, 2), (1, 1), (3, 4), (1, 4), (1, 5), (5, 1)";
ExecuteCommand(insertCountriesSQL, connection);
ExecuteCommand(insertTownsSQL, connection);
ExecuteCommand(insertMinionsSQL, connection);
ExecuteCommand(insertEvilnessFactorsSQL, connection);
ExecuteCommand(insertVillainsSQL, connection);
ExecuteCommand(insertMinionsVillainsSQL, connection);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
public DatabaseGateway(string connectionString, string databaseName)
{
_connectionString = connectionString;
_databaseName = databaseName;
_connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
}
public ActionResult QueueEmails(MassEmailer m)
{
m.Body = GetBody(m.Body);
if (UsesGrammarly(m))
{
return Json(new { error = GrammarlyNotAllowed });
}
if (TooLarge(m))
{
return Json(new { error = TooLargeError });
}
if (!m.Subject.HasValue() || !m.Body.HasValue())
{
return Json(new { id = 0, error = "Both subject and body need some text." });
}
if (!User.IsInRole("Admin") && m.Body.Contains("{createaccount}", ignoreCase: true))
{
return Json(new { id = 0, error = "Only Admin can use {createaccount}." });
}
if (Util.SessionTimedOut())
{
Session["massemailer"] = m;
return Content("timeout");
}
DbUtil.LogActivity("Emailing people");
if (m.EmailFroms().Count(ef => ef.Value == m.FromAddress) == 0)
{
return Json(new { id = 0, error = "No email address to send from." });
}
m.FromName = m.EmailFroms().First(ef => ef.Value == m.FromAddress).Text;
int id;
try
{
var eq = m.CreateQueue();
if (eq == null)
{
throw new Exception("No Emails to send (tag does not exist)");
}
id = eq.Id;
// If there are additional recipients, add them to the queue
if (m.AdditionalRecipients != null)
{
foreach (var pid in m.AdditionalRecipients)
{
// Protect against duplicate PeopleIDs ending up in the queue
var q3 = from eqt in CurrentDatabase.EmailQueueTos
where eqt.Id == eq.Id
where eqt.PeopleId == pid
select eqt;
if (q3.Any())
{
continue;
}
CurrentDatabase.EmailQueueTos.InsertOnSubmit(new EmailQueueTo
{
Id = eq.Id,
PeopleId = pid,
OrgId = eq.SendFromOrgId,
Guid = Guid.NewGuid(),
});
}
CurrentDatabase.SubmitChanges();
}
if (m.RecipientIds != null)
{
foreach (var pid in m.RecipientIds)
{
// Protect against duplicate PeopleIDs ending up in the queue
var q3 = from eqt in CurrentDatabase.EmailQueueTos
where eqt.Id == eq.Id
where eqt.PeopleId == pid
select eqt;
if (q3.Any())
{
continue;
}
CurrentDatabase.EmailQueueTos.InsertOnSubmit(new EmailQueueTo
{
Id = eq.Id,
PeopleId = pid,
OrgId = eq.SendFromOrgId,
Guid = Guid.NewGuid(),
});
}
CurrentDatabase.SubmitChanges();
}
if (eq.SendWhen.HasValue)
{
return Json(new { id = 0, content = "Emails queued to be sent." });
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
return Json(new { id = 0, error = ex.Message });
}
var host = CurrentDatabase.Host;
var currorgid = CurrentDatabase.CurrentSessionOrgId;
// save these from HttpContext to set again inside thread local storage
var userEmail = Util.UserEmail;
var isInRoleEmailTest = User.IsInRole("EmailTest");
var isMyDataUser = User.IsInRole("Access") == false;
try
{
ValidateEmailReplacementCodes(CurrentDatabase, m.Body, new MailAddress(m.FromAddress));
}
catch (Exception ex)
{
return Json(new { error = ex.Message });
}
var onlyProspects = m.OnlyProspects;
HostingEnvironment.QueueBackgroundWorkItem(ct =>
{
try
{
var db = CurrentDatabase.Copy();
var cul = CurrentDatabase.Setting("Culture", "en-US");
Thread.CurrentThread.CurrentUICulture = new CultureInfo(cul);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(cul);
// set these again inside thread local storage
db.SetCurrentOrgId(currorgid);
Util.UserEmail = userEmail;
Util.IsInRoleEmailTest = isInRoleEmailTest;
Util.IsMyDataUser = isMyDataUser;
db.SendPeopleEmail(id, onlyProspects);
}
catch (Exception ex)
{
var ex2 = new Exception($"Emailing error for queueid {id} on {host}\n{ex.Message}", ex);
var cb = new SqlConnectionStringBuilder(Util.ConnectionString) { InitialCatalog = "ELMAH" };
var errorLog = new SqlErrorLog(cb.ConnectionString) { ApplicationName = "BVCMS" };
errorLog.Log(new Error(ex2));
var db = CMSDataContext.Create(host);
var equeue = db.EmailQueues.Single(ee => ee.Id == id);
equeue.Error = ex.Message.Truncate(200);
db.SubmitChanges();
}
});
return Json(new { id = id });
}
/// <summary>
/// creates/tests and returns a valid database connection String
/// </summary>
/// <param name="dbInformation">database information</param>
/// <returns>connectionstring</returns>
public static string GetConnection(DBInformation dbInformation, DBType dbType = DBType.MSSQL)
{
// Initialize the EntityConnectionStringBuilder.
// EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
// Specify the provider name, server and database.
string providerName = "System.Data.SqlClient";
// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
// Set the properties for the data source.
sqlBuilder.DataSource = dbInformation.Host;
//Achtung! Hier wird _Zentral hinter den Datenbankname angehängt!!
sqlBuilder.InitialCatalog = dbInformation.DatabaseName;// + (pDBServer == DBS.Zentral ? "_" + pDBServer.ToString() : "");
sqlBuilder.IntegratedSecurity = dbInformation.WinAuthent;
if (!dbInformation.WinAuthent)
{
sqlBuilder.Password = dbInformation.Password;
sqlBuilder.UserID = dbInformation.Username;
}
sqlBuilder.MultipleActiveResultSets = true; // für Replikation!
sqlBuilder.Enlist = dbInformation.Enlist; //wegen transactionscope!
sqlBuilder.MultipleActiveResultSets = true; // für Replikation!
// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();
//Set the provider name.
//entityBuilder.Provider = providerName;
//// Set the provider-specific connection string.
//entityBuilder.ProviderConnectionString = providerString;
////metadata=res://*/DB.MDB_PAD.csdl|res://*/DB.MDB_PAD.ssdl|res://*/DB.MDB_PAD.msl;
//// Set the Metadata location.
//switch (pDBConData.DB)
//{
// // case DB.PAD:
// //metadata= res://*/DB.MDB_PNDS.csdl|res://*/DB.MDB_PNDS.ssdl|res://*/DB.MDB_PNDS.msl;provider=System.Data.SqlClient;provider connection string="Data Source=SOFTDEV03\SQLEXPRESS2008;Initial Catalog=PNDS;Integrated Security=True;MultipleActiveResultSets=True"
// // entityBuilder.Metadata = @"res://*/DB." + pDBServer.ToString() + ".MDB_PAD_" + pDBServer.ToString() + ".csdl|res://*/DB." + pDBServer.ToString() + ".MDB_PAD_" + pDBServer.ToString() + ".ssdl|res://*/DB." + pDBServer.ToString() + ".MDB_PAD_" + pDBServer.ToString() + ".msl";
// // break;
// //metadata= res://*/DB.Lokal.MDB_PAD_Lokal.csdl|res://*/DB.Lokal.MDB_PAD_Lokal.ssdl|res://*/DB.Lokal.MDB_PAD_Lokal.msl;
// case DB.PNDS:
// entityBuilder.Metadata = @"res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_PNDS_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".csdl|res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_PNDS_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".ssdl|res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_PNDS_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".msl";
// break;
// //case DB.PPM:
// // entityBuilder.Metadata = @"res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_" + DB.PPM + "_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".csdl|res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_" + DB.PPM + "_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".ssdl|res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_" + DB.PPM + "_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".msl";
// // break;
// case DB.ZAP:
// entityBuilder.Metadata = @"res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_ZAP_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".csdl|res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_ZAP_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".ssdl|res://*/DB." + pDBConData.DBServer.ToString() + ".MDB_ZAP_" + pDBConData.DBServer.ToString() + pDBConData.Suffix + ".msl";
// break;
//}
//metadata=res://*/DB.Zentral.MDM_PNDS_Zentral.csdl|res://*/DB.Zentral.MDM_PNDS_Zentral.ssdl|res://*/DB.Zentral.MDM_PNDS_Zentral.msl;provider=System.Data.SqlClient;provider connection string="Data Source=TEST;Initial Catalog=PNDS;Persist Security Info=True;User ID=pecusoft;Password=sesam1997oeffne;MultipleActiveResultSets=True;Application Name=EntityFramework"
// Debug.WriteLine(entityBuilder.ToString());
// String test = entityBuilder.ToString();
// String test2 = "metadata=res://*/DB.MDB_PAD.csdl|res://*/DB.MDB_PAD.ssdl|res://*/DB.MDB_PAD.msl;provider=System.Data.SqlClient;provider connection string=\"Data Source=SOFTDEV03\\SQLEXPRESS2008;Initial Catalog=PAD;Integrated Security=True;MultipleActiveResultSets=True\"";
using (SqlConnection conn = new SqlConnection(providerString))
{
conn.Open();
// Debug.WriteLine("Just testing the connection.");
conn.Close();
// CPManager.Instance.DBZ_ERR = DBZ_ERR.OK;
// CPManager.Instance.VMMain.DBZERR = false;
}
return(providerString);
}
public static bool GetMSSQLRecords(string[] dbparemeters, string workingFolder, string projectXMLFile, string projectSqLiteFile)
{
string table;
var fqProjectFilePath = Path.Combine(workingFolder, projectXMLFile);
var fqProjectDBFilePath = Path.Combine(workingFolder, projectSqLiteFile);
if (File.Exists(fqProjectFilePath))
{
table = ReadXML.ReadProjectDBTableName(fqProjectFilePath);
}
else
{
throw new FileNotFoundException("Project file not found!");
}
SqlConnectionStringBuilder dbConString = new SqlConnectionStringBuilder
{
DataSource = dbparemeters[0],
InitialCatalog = dbparemeters[1],
UserID = dbparemeters[2],
Password = dbparemeters[3]
};
using (SqlConnection connection = new SqlConnection(dbConString.ConnectionString))
{
try
{
connection.Open();
string command = "SELECT * from " + table + ";";
using (SqlCommand cmd = new SqlCommand(command, connection))
{
SqlDataReader drItem = cmd.ExecuteReader();
//make a List to send to the SQL Lite add method
List <string> list = new List <string>();
//populate the List
while (drItem.Read())
{
//populate the List
string xPos = drItem["xPos"].ToString();
string yPos = drItem["yPos"].ToString();
string zPos = drItem["zPos"].ToString();
string length = drItem["length"].ToString();
string width = drItem["width"].ToString();
string height = drItem["height"].ToString();
string weight = drItem["weight"].ToString();
string name = drItem["name"].ToString();
list.Add(xPos);
list.Add(yPos);
list.Add(zPos);
list.Add(length);
list.Add(width);
list.Add(height);
list.Add(weight);
list.Add(name);
if (File.Exists(fqProjectDBFilePath))
{
//finally add the new list to the project database
WriteSQLite.AddSQLiteData(FilePathDefaults.ScratchFolder, projectSqLiteFile, list);
}
else
{
throw new FileNotFoundException("Project database to write new SQL data into not found!");
}
} //end of while
return(true);
} //end of using SqlCommand
} //end of try
catch (SqlException)
{
return(false);
}
}
}
public void ProcessRequest(HttpContext context)
{
string CID = Convert.ToString(context.Request.QueryString["StyleId"]);
string OCODE = "8989";
string ConnectionString = (_Content.Connection as EntityConnection).StoreConnection.ConnectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.ConnectTimeout = 2500;
SqlConnection Conn = new SqlConnection(builder.ConnectionString);
System.Data.Common.DbDataReader sqlReader;
Conn.Open();
SqlCommand cmd = new SqlCommand("GET_LC_Image", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@IPID", SqlDbType.NVarChar).Value = CID;
cmd.Parameters.Add("@OCODE", SqlDbType.NVarChar).Value = OCODE;
cmd.CommandTimeout = 0;
sqlReader = (System.Data.Common.DbDataReader)cmd.ExecuteReader();
try
{
if (sqlReader.Read())
{
byte[] imageByte = sqlReader["Style_Photo"] as byte[] ?? null;
//byte[] imageByte = (byte[])sqlReader["EMP_PHOTO"];
System.Drawing.Image b;
Bitmap bitMap = null;
if (imageByte != null && imageByte.Length > 0)
{
using (MemoryStream ms = new MemoryStream())
{
ms.Write(imageByte, 0, imageByte.Length);
b = System.Drawing.Image.FromStream(ms);
bitMap = new System.Drawing.Bitmap(b, b.Width, b.Height);
using (Graphics g = Graphics.FromImage(bitMap))
{
g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
g.SmoothingMode = SmoothingMode.AntiAlias;
g.CompositingQuality = System.Drawing.Drawing2D.CompositingQuality.HighQuality;
g.DrawImage(bitMap, 0, 0, b.Width, b.Height);
g.Dispose();
b.Dispose();
ms.Dispose();
context.Response.ContentType = "image/jpeg";
bitMap.Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
bitMap.Dispose();
}
}
}
else
{
string imagePath = "../resources/no_image_found.png";
context.Response.ContentType = "image/jpeg";
context.Response.Write(imagePath);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.Close();
}
}
static void Main(string[] args)
{
Console.WriteLine("https://medium.com/dapper-net");
Console.WriteLine();
var dataSource = @"(LocalDB)\MSSQLLocalDB";
if (args.Count() >= 1)
{
dataSource = args[0];
}
Console.WriteLine($"Using Data Source: '{dataSource}'");
// Create connection string
var dataFolder = Directory.GetParent(Environment.CurrentDirectory).GetDirectories("Dapper.Samples.Data").Single();
var builder = new SqlConnectionStringBuilder()
{
DataSource = dataSource,
AttachDBFilename = $@"{dataFolder.FullName}\DapperSample.mdf",
IntegratedSecurity = true,
ConnectTimeout = 10,
ApplicationName = "Dapper.Samples.Basics"
};
// Check for connectivity to SQL Server
try {
var dummy = new SqlConnection(builder.ConnectionString);
dummy.Open();
} catch (SqlException) {
Console.WriteLine($"ERROR: Cannot open connection to {dataSource}");
return;
}
// Wrap common code in an Action shell
Action <string, Action <SqlConnection> > ExecuteSample = (message, action) =>
{
Console.WriteLine(message);
using (var conn = new SqlConnection(builder.ConnectionString))
{
action(conn);
}
Console.WriteLine();
};
// Return a dynamic type list
ExecuteSample("1. Return a list of dynamic objects", conn =>
{
var queryResult = conn.Query("SELECT [Id], [FirstName], [LastName] FROM dbo.[Users]");
Console.WriteLine("{0} is a {1}", nameof(queryResult), queryResult.GetType());
queryResult.ToList().ForEach(u => Console.WriteLine($"{u}"));
});
// Return a strongly typed list
ExecuteSample("2. Return a list of strongly typed objects", conn =>
{
var queryResult = conn.Query <User>("SELECT [Id], [FirstName], [LastName] FROM dbo.[Users]");
Console.WriteLine("{0} is a {1}", nameof(queryResult), queryResult.GetType());
queryResult.ToList().ForEach(u => Console.WriteLine($"{u}"));
});
// Execute sample: return affected rows
ExecuteSample("3. Execute a statement and return affected rows. Any resultset will be discared.", conn =>
{
int affectedRows = conn.Execute("UPDATE dbo.[Users] SET [FirstName] = 'John' WHERE [Id] = 3");
Console.WriteLine("'UPDATE' Affected Rows: {0}", affectedRows);
});
// Return an untyped scalar
ExecuteSample("4. Return an object scalar", conn =>
{
object firstName = conn.ExecuteScalar("SELECT [FirstName] FROM dbo.[Users] WHERE [Id] = 1");
Console.WriteLine("{0} ({1}): {2}", nameof(firstName), firstName.GetType(), firstName);
});
// Return an typed scalar
ExecuteSample("5. Return a typed object", conn =>
{
var firstName = conn.ExecuteScalar <string>("SELECT [FirstName] FROM dbo.[Users] WHERE [Id] = 1");
Console.WriteLine("{0} ({1}): {2}", nameof(firstName), firstName.GetType(), firstName);
});
// Parameters sample
ExecuteSample("6. Create and set parameter values using anonymous objects.", conn =>
{
var queryResult = conn.Query <User>(
"SELECT [Id], [FirstName], [LastName] FROM dbo.[Users] WHERE Id = @Id OR EmailAddress = @Email",
new {
@Id = 1,
@Email = new DbString()
{
Value = "*****@*****.**",
IsAnsi = false,
IsFixedLength = false,
Length = 100
}
});
queryResult.ToList().ForEach(u => Console.WriteLine($"{u}"));
});
ExecuteSample("6b. Use an IEnumerable as parameter.", conn =>
{
string[] lastNames = new string[] { "Black", "Green", "White", "Brown" };
var queryResult = conn.Query <User>(
"SELECT [Id], [FirstName], [LastName] FROM dbo.[Users] WHERE LastName in @LastName",
new { @LastName = lastNames }
);
queryResult.ToList().ForEach(u => Console.WriteLine($"{u}"));
});
ExecuteSample("7. Create and set parameter values and properties (type and size).", conn =>
{
DynamicParameters dp = new DynamicParameters();
dp.Add("FirstName", "Davide", DbType.String, ParameterDirection.Input, 100);
dp.Add("LastName", "Mauri");
var queryResult = conn.Query <User>("SELECT [Id], [FirstName], [LastName] FROM dbo.[Users] WHERE FirstName = @FirstName AND LastName = @LastName", dp);
queryResult.ToList().ForEach(u => Console.WriteLine($"{u}"));
});
// Stored procedure execution sample
ExecuteSample("8. Execute a stored procedure that returns user info.", conn =>
{
var queryResult = conn.Query <User>("dbo.ProcedureBasic", new { @email = "*****@*****.**" }, commandType: CommandType.StoredProcedure);
queryResult.ToList().ForEach(u => Console.WriteLine($"{u}"));
});
// Parameters
ExecuteSample("9. Execute a stored procedure that has input and output params and a return value.", conn =>
{
DynamicParameters dp = new DynamicParameters();
dp.Add("email", "*****@*****.**");
dp.Add("firstName", null, DbType.String, ParameterDirection.Output, 100); // Manually specify parameter details
dp.Add("lastName", "", direction: ParameterDirection.Output); // Infer type and size from given value
dp.Add("result", null, DbType.Int32, ParameterDirection.ReturnValue);
conn.Execute("dbo.ProcedureWithOutputAndReturnValue", dp, commandType: CommandType.StoredProcedure);
Console.WriteLine("User: {0}, {1}, {2}", dp.Get <int>("result"), dp.Get <string>("firstName"), dp.Get <string>("lastName"));
});
// Return a DataReader
ExecuteSample("10. Get a DataReader, if you really prefer.", conn => {
var dataReader = conn.ExecuteReader("SELECT [Id], [FirstName], [LastName] FROM dbo.[Users]");
while (dataReader.Read())
{
Console.WriteLine("User: {0}, {1}, {2}", dataReader["Id"], dataReader["FirstName"], dataReader["LastName"]);
}
});
}
public void SqlConnectionStringBuilder_Test()
{
var csb = new SqlConnectionStringBuilder("Server=localhost; Database=Sdisco; Trusted_Connection=True;");
csb["Database"].ShouldBe("Sdisco");
}
private static string Process(string srcConString, string targetConString, ref DacDeployOptions options)
{
var srcCon = new SqlConnectionStringBuilder(srcConString);
var decFilePath = GetDacFileName(srcConString) + ".bacpac";
Export(srcConString, decFilePath);
//Export(targerConnectionString, @"C:\Temp\Target_dacFile.dacpac");
var TargetCon = new SqlConnectionStringBuilder(targetConString);
var TargetdacServices = new DacServices(TargetCon.ConnectionString);
TargetdacServices.Message += ((s, e) => { Console.WriteLine(e?.Message.ToString()); });
TargetdacServices.ProgressChanged += ((s, e) => { Console.WriteLine("Status:{0}, Message:{1}", e?.Status, e?.Message.ToString()); });
if (options == null)
{
options = new DacDeployOptions()
{
CommentOutSetVarDeclarations = true,
DoNotAlterChangeDataCaptureObjects = true,
DropDmlTriggersNotInSource = true,
PopulateFilesOnFileGroups = true,
RegisterDataTierApplication = true,
};
}
using (DacPackage dacpac = DacPackage.Load(decFilePath, DacSchemaModelStorageType.Memory))
{
// Script then deploy, to support debugging of the generated plan
// string script = dacServices.GenerateDeployScript(dacpac, dbName, options);
var dt = DateTime.Now.ToString("yyyy.MM.dd.HHmmss");
//TargetdacServices.Register(TargetCon.InitialCatalog, DacSchemaModelStorageType.File, "DacService", Version.Parse(dt));
//CreateXmlReport(srcConString, TargetCon, TargetdacServices, dacpac);
//CreateSqlChangeScript(srcConString, TargetCon, TargetdacServices, dacpac);
//CreateXmlDiffReport(srcConString, TargetCon, TargetdacServices);
var pubOptions = new PublishOptions()
{
DeployOptions = options,
GenerateDeploymentReport = true,
GenerateDeploymentScript = true,
DatabaseScriptPath = GetDacFileName(srcConString),
MasterDbScriptPath = GetDacFileName(srcConString) + "Master"
};
var rs = TargetdacServices.Script(dacpac, TargetCon.InitialCatalog, pubOptions);
string scriptPath = GetDacFileName(srcConString) + "_" + srcCon.DataSource + ".sql";
string reportPath = GetDacFileName(srcConString) + "_" + srcCon.DataSource + ".xml";
System.IO.File.WriteAllText(scriptPath, rs.DatabaseScript);
System.IO.File.WriteAllText(reportPath, rs.DeploymentReport);
return("Done.");
}
}
public void CreateSqlServer(
string resourceGroupName,
string location,
string loginName,
string password,
string serverName,
string dbName,
string tableName
)
{
string version12 = "12.0";
Dictionary <string, string> tags = new Dictionary <string, string>();
var server = sqlManagementClient.Servers.CreateOrUpdate(resourceGroupName, serverName, new Server()
{
AdministratorLogin = loginName,
AdministratorLoginPassword = password,
Version = version12,
Tags = tags,
Location = location
});
ThrowIfTrue(
server == null,
"sqlManagementClient.Servers.CreateOrUpdate returned null."
);
var database = sqlManagementClient.Databases.CreateOrUpdate(resourceGroupName, server.Name, dbName, new Database()
{
Location = server.Location
});
ThrowIfTrue(
database == null,
"sqlManagementClient.Databases.CreateOrUpdate returned null."
);
// Create server firewall rule
sqlManagementClient.FirewallRules.CreateOrUpdate(resourceGroupName, server.Name, "sqltestrule", new FirewallRule()
{
StartIpAddress = "0.0.0.0",
EndIpAddress = "255.255.255.255"
});
// Create test table with columns
// This is not needed in playback because in playback, there is no actual database to execute against
if (HttpMockServer.GetCurrentMode() != HttpRecorderMode.Playback)
{
var builder = new SqlConnectionStringBuilder()
{
DataSource = string.Format(server.FullyQualifiedDomainName, server.Name),
UserID = loginName,
Password = password,
InitialCatalog = dbName
};
using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
{
conn.Open();
SqlCommand command = new SqlCommand($"create table {tableName} (column1 int, column2 nvarchar(max))", conn);
command.ExecuteNonQuery();
}
}
}
private void retrieveStudentData()
{
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = "server";
csb.InitialCatalog = "database";
csb.IntegratedSecurity = true;
string connString = csb.ToString();
int searchID = Convert.ToInt32(TRANSACTION_ID);
string dbCON = System.IO.File.ReadAllText(@"C:\ProgramData\STI Front Line\System Files\Local\DBConfig.txt");
SqlConnection sqlCon = new SqlConnection(@"Data Source=" + dbCON + "; Initial Catalog=STILocalDB; Integrated Security=true;");
string queryString = "select top 1 TRANSACTION_ID, SCHOOL_LEVEL, STUDENT_ID, COURSE, YEAR_OR_GRADE, LRN_OR_ESC, STUDENT_NAME, BIRTH_DATE, BIRTH_PLACE, CITIZENSHIP, CIVIL_STATUS, GENDER, CURRENT_ADDRESS, PERMANENT_ADDRESS, LANDLINE, MOBILE, EMAIL, PREVIOUS_SCHOOL_LEVEL, GRADUATION, LAST_SCHOOL_ATTENDED, PREVIOUS_LEVEL, PREVIOUS_SCHOOL_YEAR_ATTENDED, PREVIOUS_YEAR_OR_GRADE, TERM, FATHER_NAME, FATHER_CONTACT_NO, FATHER_OCCUPATION, FATHER_EMAIL, MOTHER_NAME, MOTHER_CONTACT_NO, MOTHER_OCCUPATION, MOTHER_EMAIL, GUARDIAN_NAME, GUARDIAN_CONTACT_NO, GUARDIAN_OCCUPATION, GUARDIAN_EMAIL, TIME_AND_DATE_ENROLLED, SCHOOL_YEAR from tblSTIEnrollees Where TRANSACTION_ID=" + searchID;
using (SqlConnection connection = new SqlConnection(@"Data Source=" + dbCON + "; Initial Catalog=STILocalDB; Integrated Security=true;"))
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = queryString;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
transaction_idValue = reader["TRANSACTION_ID"].ToString();
school_levelValue = reader["SCHOOL_LEVEL"].ToString();
student_idValue = reader["STUDENT_ID"].ToString();
courseValue = reader["COURSE"].ToString();
year_or_gradeValue = reader["YEAR_OR_GRADE"].ToString();
lrn_or_escValue = reader["LRN_OR_ESC"].ToString();
student_nameValue = reader["STUDENT_NAME"].ToString();
birth_dateValue = reader["BIRTH_DATE"].ToString();
birth_placeValue = reader["BIRTH_PLACE"].ToString();
citizenshipValue = reader["CITIZENSHIP"].ToString();
civil_statusValue = reader["CIVIL_STATUS"].ToString();
genderValue = reader["GENDER"].ToString();
current_addressValue = reader["CURRENT_ADDRESS"].ToString();
permanent_addressValue = reader["PERMANENT_ADDRESS"].ToString();
landlineValue = reader["LANDLINE"].ToString();
mobileValue = reader["MOBILE"].ToString();
emailValue = reader["EMAIL"].ToString();
previous_school_levelValue = reader["PREVIOUS_SCHOOL_LEVEL"].ToString();
graduationValue = reader["GRADUATION"].ToString();
last_school_attendedValue = reader["LAST_SCHOOL_ATTENDED"].ToString();
previous_levelValue = reader["PREVIOUS_LEVEL"].ToString();
previous_school_year_attendedValue = reader["PREVIOUS_SCHOOL_YEAR_ATTENDED"].ToString();
previous_year_or_gradeValue = reader["PREVIOUS_YEAR_OR_GRADE"].ToString();
termValue = reader["TERM"].ToString();
father_nameValue = reader["FATHER_NAME"].ToString();
father_contact_noValue = reader["FATHER_CONTACT_NO"].ToString();
father_occupationValue = reader["FATHER_OCCUPATION"].ToString();
father_emailValue = reader["FATHER_EMAIL"].ToString();
mother_nameValue = reader["MOTHER_NAME"].ToString();
mother_contact_noValue = reader["MOTHER_CONTACT_NO"].ToString();
mother_occupationValue = reader["MOTHER_OCCUPATION"].ToString();
mother_emailValue = reader["MOTHER_EMAIL"].ToString();
guardian_nameValue = reader["GUARDIAN_NAME"].ToString();
guardian_contact_noValue = reader["GUARDIAN_CONTACT_NO"].ToString();
guardian_occupationValue = reader["GUARDIAN_OCCUPATION"].ToString();
guardian_emailValue = reader["GUARDIAN_EMAIL"].ToString();
time_and_date_enrolledValue = reader["TIME_AND_DATE_ENROLLED"].ToString();
school_yearValue = reader["SCHOOL_YEAR"].ToString();
}
}
}
}
public static CreateDatabaseResult CreateDataBase(string dbName, string serverConnectionString)
{
var ret = new CreateDatabaseResult();
if (string.IsNullOrEmpty(dbName))
{
ret.Status = EnCreateDataBase.DatabaseNameEmpty;
ret.Result.AddReturnedValue(ReturnedState.Error, $"نام بانک اطلاعاتی خالی است.");
return(ret);
}
if (string.IsNullOrEmpty(serverConnectionString))
{
ret.Status = EnCreateDataBase.ServerConnectionStringError;
ret.Result.AddReturnedValue(ReturnedState.Error, $"رشته اتصال به سرور خالی است.");
return(ret);
}
try
{
var cbs = new SqlConnectionStringBuilder(serverConnectionString)
{
InitialCatalog = "master"
};
if (IsExistDatabase(dbName, cbs.ConnectionString))
{
ret.Result.AddReturnedValue(ReturnedState.Error, $"بانک اطلاعاتی با نام {dbName} \r\nدر سرویس {serverConnectionString}\r\n در حال حاضر وجود دارد.");
ret.Status = EnCreateDataBase.DatabaseExists;
return(ret);
}
var cn = new SqlConnection(cbs.ConnectionString);
var cmd = new SqlCommand("", cn)
{
CommandType = CommandType.Text
};
var dbPath = Application.StartupPath + "\\DataBase";
if (Settings.SetDistanationFolder(cn.ConnectionString))
{
cmd.CommandText = "CREATE DATABASE [DBName] ON PRIMARY ( NAME = N'DBName'" +
",FILENAME = N'DBPath\\DBName.mdf', SIZE = 10240KB , FILEGROWTH = 10240KB )" +
"LOG ON ( NAME = N'DBName_log',FILENAME = N'DBPath\\DBName_log.ldf'" +
",SIZE=10240KB,FILEGROWTH=10%)";
cmd.CommandText = cmd.CommandText.Replace("DBName", dbName);
cmd.CommandText = cmd.CommandText.Replace("DBPath", dbPath);
}
else
{
cmd.CommandText = "CREATE DATABASE [DBName]";
cmd.CommandText = cmd.CommandText.Replace("DBName", dbName);
}
var constr = cn.ConnectionString.Replace("master", dbName);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
ret.ConnectionString = constr;
ret.Status = EnCreateDataBase.Success;
}
catch (Exception ex)
{
if (ex.Message.ToLower().Contains("because it already exists".ToLower()))
{
ret.Status = EnCreateDataBase.DatabaseExists;
}
else
{
WebErrorLog.ErrorInstence.StartErrorLog(ex);
}
ret.Result.AddReturnedValue(ex);
}
return(ret);
}
static void datacheck()
{
string sourcePath = @"C:\DATA FILES\Raw Data Files\";
string[] allfiles = new string[10];
int i = 0;
string fileName = "";
//code to check if files were updated sccessfully.
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "tgbldev.database.windows.net";
builder.UserID = "dbadmin";
builder.Password = "******";
builder.InitialCatalog = "tgbldevdata";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
Console.WriteLine("\nQuery data example:");
Console.WriteLine("=========================================\n");
Console.WriteLine("FileName");
connection.Open();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT dcp.Filename as FNAME ");
sb.Append("FROM [dbo].[DataCheckPoint] dcp");
String sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
allfiles[i] = reader.GetString(0);
i = i + 1;
}
}
}
}
if (System.IO.Directory.Exists(sourcePath))
{
string path = @"C:\DATA FILES\Raw Data Files\";
string[] files = new string[100];
DirectoryInfo directory = new DirectoryInfo(path);
FileInfo[] all_files = directory.GetFiles("*.txt");
int filecount = 0;
foreach (FileInfo f in all_files)
{
string filenameWithoutPath = Path.GetFileName(f.FullName);
files[filecount] = filenameWithoutPath.GetUntilOrEmpty();
for (int t = 0; t < i; t++)
{
if (files[filecount] == allfiles[t])
{
System.IO.File.Delete(sourcePath + fileName);
}
}
filecount++;
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}
public Tuple <int, int> CreateNewUser(Models.User user, int eventCode)
{
int last_inserted_id = 0;
response_message = "";
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "luckydrawapplication20200108092548dbserver.database.windows.net";
builder.UserID = "sqladmin";
builder.Password = "******";
builder.InitialCatalog = "luckywheeldb";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO users(Name, ICNumber, EmailAddress, ContactNumber, EventID, ProjectID, Unit, SalesConsultant, PrizeWon, StaffWon) VALUES ('" + user.Name.ToUpper() + "', '" + user.ICNumber + "', '" + user.EmailAddress.ToLower() + "', '" + user.ContactNumber + "', " + eventCode + ", " + user.ProjectID + ", '" + user.Unit.ToUpper() + "', '" + user.SalesConsultant.ToUpper() + "', 0, 0); SELECT SCOPE_IDENTITY() AS id;");
String sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
using (SqlDataReader rd = command.ExecuteReader())
{
while (rd.Read())
{
last_inserted_id = Convert.ToInt32(rd["id"]);
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "luckydrawapplication20200108092548dbserver.database.windows.net";
builder.UserID = "sqladmin";
builder.Password = "******";
builder.InitialCatalog = "luckywheeldb";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE project SET NoOfProject= NoOfProject + 1 WHERE ProjectID = " + user.ProjectID);
String sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
SqlDataReader rd = command.ExecuteReader();
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
return(CheckForLuckyDraw(user, last_inserted_id));
}
public DB()
{
_Builder = new SqlConnectionStringBuilder();
_Conn = new SqlConnection();
_cmd = new SqlCommand();
}
private void btnSignIn_Click(object sender, RoutedEventArgs e)
{
try
{
//добавить проверку на макс длинну
if (Validator.ValidTextBoxes(Password.Password, Login.Text))
{
if (Login.Text == "admin")
{
SqlConnection cn = Connection.GetConnectionAdmin(Password.Password);
SqlConnectionStringBuilder sqlbuilder = new SqlConnectionStringBuilder(cn.ConnectionString);
Model.Admin admin = new Model.Admin(sqlbuilder.UserID, sqlbuilder.Password);
MainWindow mainWnd = new MainWindow(admin);
mainWnd.Show();
this.Close();
}
else
{
using (SqlConnection cn = Connection.GetConnectionUser())
{
cn.Open();
SqlCommand cmd = new SqlCommand("Authorisation", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter password = new SqlParameter();
password.ParameterName = "@password";
password.Value = Password.Password;
SqlParameter login = new SqlParameter();
login.ParameterName = "@login";
login.Value = Login.Text;
cmd.Parameters.Add(login);
cmd.Parameters.Add(password);
SqlParameter rc = new SqlParameter();
rc.ParameterName = "@rc";
rc.SqlDbType = SqlDbType.Bit;
rc.Direction = ParameterDirection.Output;
cmd.Parameters.Add(rc);
cmd.ExecuteNonQuery();
if ((bool)cmd.Parameters["@rc"].Value)
{
cmd = new SqlCommand("UserInfo", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter login2 = new SqlParameter();
login2.ParameterName = "@login";
login2.Value = Login.Text;
SqlParameter password2 = new SqlParameter();
password2.ParameterName = "@password";
password2.Value = Password.Password;
cmd.Parameters.Add(login2);
cmd.Parameters.Add(password2);
SqlParameter id = new SqlParameter();
id.ParameterName = "@id";
id.SqlDbType = SqlDbType.UniqueIdentifier;
id.Direction = ParameterDirection.Output;
cmd.Parameters.Add(id);
SqlParameter email = new SqlParameter();
email.ParameterName = "@email";
email.SqlDbType = SqlDbType.NVarChar;
email.Size = 50;
email.Direction = ParameterDirection.Output;
cmd.Parameters.Add(email);
cmd.ExecuteNonQuery();
Model.User user = new Model.User(cmd.Parameters["@id"].Value, Login.Text, Password.Password, cmd.Parameters["@email"].Value);
MainWindow mainWnd = new MainWindow(user);
mainWnd.Show();
this.Close();
}
else
{
MessageBox.Show("Ошибка авторизации! Неверный логин или пароль!");
}
cn.Close();
}
}
}
else
{
MessageBox.Show("Введите все данные!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();//連線建立字串物件
sb.DataSource = "USER-PC";
sb.InitialCatalog = "Northwind";
sb.IntegratedSecurity = true;
sb.UserID = "sa";
sb.Password = "******";
SqlConnection conn = new SqlConnection(sb.ConnectionString);
string sql = "SELECT * FROM Categories";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
Table tb = new Table();
TableRow tr = new TableRow();
TableCell tc1 = new TableCell();
TableCell tc2 = new TableCell();
TableCell tc3 = new TableCell();
TableCell tc4 = new TableCell();
tb.BorderWidth = 1;
tc1.BorderWidth = 1;
tc2.BorderWidth = 1;
tc3.BorderWidth = 1;
tc4.BorderWidth = 1;
SqlDataReader sdr = cmd.ExecuteReader();//返回下達sql語法中每個單元格中的資料
while (sdr.Read())
{
//將找到的資料填入列
//填入單元格
//單元格填入資料列
//資料列填入資料表
tc1.Text = sdr["CategoryID"].ToString();
tc2.Text = sdr["CategoryName"].ToString();
tc3.Text = sdr[2].ToString();
tc4.Text = sdr[3].ToString();
tr.Cells.Add(tc1);
tr.Cells.Add(tc2);
tr.Cells.Add(tc3);
tr.Cells.Add(tc4);
tb.Rows.Add(tr);
//在找到一筆資料後再產生一空白列準備存放下筆資料
tr = new TableRow();
tc1 = new TableCell();
tc2 = new TableCell();
tc3 = new TableCell();
tc4 = new TableCell();
tb.BorderWidth = 1;
tc1.BorderWidth = 1;
tc2.BorderWidth = 1;
tc3.BorderWidth = 1;
tc4.BorderWidth = 1;
}
//將 PlaceHolder 控制元件用作儲存動態新增到網頁的伺服器控制元件的容器。
//PlaceHolder 控制元件不產生任何可見輸出並且只能用作網頁上其他控制元件的容器。
PlaceHolder1.Controls.Add(tb);
conn.Close();
}
static void Main(string[] args)
{
try
{
Console.WriteLine("Connect to SQL Server and demo Create, Read, Update and Delete operations.");
//...0010.Build connection string
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "localhost"; // update me
builder.UserID = "sa"; // update me
builder.Password = "******"; // update me
builder.InitialCatalog = "master";
//...0020.Connect to SQL
Console.Write("Connecting to SQL Server ... ");
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
Console.WriteLine("Done.");
//...0030.Create a sample database
Console.Write("Dropping and creating database 'SampleDB' ... ");
String sql = "DROP DATABASE IF EXISTS [SampleDB]; CREATE DATABASE [SampleDB]";
using (SqlCommand command = new SqlCommand(sql, connection)) //...0031. SqlCommand 클래스
{
command.ExecuteNonQuery(); //...0032. SqlCommand.ExecuteNonQuery 메서드
Console.WriteLine("Done.");
}
//...0040.Create a Table and insert some sample data
Console.Write("Creating sample table with data, press any key to continue...");
Console.ReadKey(true);
StringBuilder sb = new StringBuilder(); //...0041.StringBuilder
sb.Append("USE SampleDB; ");
sb.Append("CREATE TABLE Employees ( ");
sb.Append(" Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ");
sb.Append(" Name NVARCHAR(50), ");
sb.Append(" Location NVARCHAR(50) ");
sb.Append("); ");
sb.Append("INSERT INTO Employees (Name, Location) VALUES ");
sb.Append("(N'Jared', N'Australia'), "); //...0042.접두사N : nchar, nvarchar.
sb.Append("(N'Nikita', N'India'), ");
sb.Append("(N'Tom', N'Germany'); ");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery(); //...0043.SqlCommand.ExecuteNonQuery 메서드
Console.WriteLine("Done.");
}
//...0050.INSERT demo
Console.Write("Inserting a new row into table, press any key to continue...");
Console.ReadKey(true);
sb.Clear();
sb.Append("INSERT Employees (Name, Location) ");
sb.Append("VALUES (@name, @location);");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@name", "Jake"); //...0051.SqlCommand.Parameters 속성
command.Parameters.AddWithValue("@location", "United States");
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) inserted");
}
//...0060.UPDATE demo
String userToUpdate = "Nikita";
Console.Write("Updating 'Location' for user '" + userToUpdate + "', press any key to continue...");
Console.ReadKey(true);
sb.Clear();
sb.Append("UPDATE Employees SET Location = N'United States' WHERE Name = @name");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@name", userToUpdate);
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) updated");
}
//...0070.DELETE demo
String userToDelete = "Jared";
Console.Write("Deleting user '" + userToDelete + "', press any key to continue...");
Console.ReadKey(true);
sb.Clear();
sb.Append("DELETE FROM Employees WHERE Name = @name;");
sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@name", userToDelete);
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) deleted");
}
//...0080.SELECT demo
Console.WriteLine("Reading data from table, press any key to continue...");
Console.ReadKey(true);
sql = "SELECT Id, Name, Location FROM Employees;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader()) //...0081.SqlDataReader 클래스
{
while (reader.Read()) //...0082.SqlDataReader.Read 메서드
{
Console.WriteLine("{0} {1} {2}",
reader.GetInt32(0), //...0083.SqlDataReader.GetInt32(Int32) 메서드
reader.GetString(1), reader.GetString(2)); //...0084.SqlDataReader.GetString(Int32) 메서드
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("All done. Press any key to finish...");
Console.ReadKey(true);
}
public void Init(string connectionString)
{
builder = new SqlConnectionStringBuilder(connectionString);
}
public static string GetDB0EntityString(string configstring)
{
string[] DataConnectionInfo = configstring.Split(',');
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = DataConnectionInfo[0];
builder.UserID = DataConnectionInfo[1];
builder.Password = DataConnectionInfo[2];
builder.InitialCatalog = DatabaseName;
builder.IntegratedSecurity = false;
builder.PersistSecurityInfo = false;
EntityConnectionStringBuilder entBuilder = new EntityConnectionStringBuilder();
entBuilder.Provider = "System.Data.SqlClient";
entBuilder.ProviderConnectionString = builder.ConnectionString;
entBuilder.Metadata = String.Format("res://{0}/{1}.csdl|res://{0}/{1}.ssdl|res://{0}/{1}.msl", "Proc.BusinessLogic", "DB0." + DatabaseName);
return entBuilder.ConnectionString;
}
/// <summary>
/// Converts a connection string for a format which is appropriate to kill another connection with (i.e. non-pooled, no transactions)
/// </summary>
/// <param name="connectionString">Base connection string to convert</param>
/// <returns>The converted connection string</returns>
private static string CreateKillByTSqlConnectionString(string connectionString)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
// Avoid tampering with the connection pool
builder.Pooling = false;
return builder.ConnectionString;
}
public static string GetDB0ConnectionString(string configstring)
{
string[] DataConnectionInfo = configstring.Split(',');
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = DataConnectionInfo[0];
builder.UserID = DataConnectionInfo[1];
builder.Password = DataConnectionInfo[2];
builder.InitialCatalog = DatabaseName;
builder.MultipleActiveResultSets = true;
builder.IntegratedSecurity = false;
return builder.ConnectionString;
}
public bool InitConnection(string ConnectionString)
{
_connection = new SqlConnection();
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
_connection.ConnectionString = ConnectionString;
_strServerName = builder.DataSource;
_strDataBaseName = builder.InitialCatalog;
_isWinAuth = builder.IntegratedSecurity;
_strPassword = builder.Password;
_strUserName = builder.UserID;
return true;
}
public SqlConnectionStringBuilder GetConnectionStringBuilder(string ConnectionString)
{
SqlConnectionStringBuilder result = new SqlConnectionStringBuilder(ConnectionString);
return result;
}
//public Connection()
//{
//_msdasc = new MSDASC.DataLinksClass();
//}
/// <summary>
/// Строитель коннекта
/// </summary>
/// <returns></returns>
public bool BuildConnection()
{
_connection = new SqlConnection();
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = _strServerName;
builder.InitialCatalog = _strDataBaseName;
builder.IntegratedSecurity = _isWinAuth;
builder.Password = _strPassword;
builder.UserID = _strUserName;
_connection.ConnectionString = builder.ConnectionString;
return true;
}
static TcpDefaultForAzureTest()
{
AzureExtensions = new string[]
{
".database.windows.net",
".database.cloudapi.de",
".database.usgovcloudapi.net",
".database.chinacloudapi.cn"
};
builder = new SqlConnectionStringBuilder();
builder.ConnectTimeout = 1;
}
