public int? Create(ConnectionString entity) { using (SqlCeConnection connection = new SqlCeConnection(ConnectionString)) { connection.Open(); SqlCeCommand command = new SqlCeCommand("INSERT INTO ConnectionStrings(Type, Name, Connection) VALUES (@Type, @Name, @Connection)", connection); command.Parameters.AddWithValue("@Type", (int)entity.Type); command.Parameters.AddWithValue("@Name", entity.Name); command.Parameters.AddWithValue("@Connection", entity.Connection); command.ExecuteNonQuery(); SqlCeCommand getId = new SqlCeCommand("SELECT @@IDENTITY AS Id", connection); object id = getId.ExecuteScalar(); return id == null ? (int?)null : Convert.ToInt32(getId.ExecuteScalar()); } }
public static int InsertCapturePointsForTextConversion(int RecommendationId, List<CustomTreeNode> customNodesList) { int returnCode = -1; List<int> capturePointsIds = new List<int>(); SqlCeConnection conn = BackEndUtils.GetSqlConnection(); try { conn.Open(); for (int i = 0; i < customNodesList.Count; i++) { SqlCeCommand command = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandInsertCapturePointTextConv, conn); //@pointText, @pointUsedAttributes, @pointParentNode, @pointUsedAttribValues, @pointRecId command.Parameters.Add("@pointText", customNodesList[i].Text); command.Parameters.Add("@pointUsedAttributes", BackEndUtils.GetUsedAttributes(customNodesList[i].customizedAttributeCollection)); command.Parameters.Add("@pointParentNode", (customNodesList[i].Parent == null ? "" : customNodesList[i].Parent.Text)); command.Parameters.Add("@pointUsedAttribValues", BackEndUtils.GetUsedAttributesValues(customNodesList[i].customizedAttributeCollection)); command.Parameters.Add("@pointRecId", RecommendationId); command.Parameters.Add("@Level", customNodesList[i].Level); command.Parameters.Add("@ItemIndex", customNodesList[i].Index); command.Parameters.Add("@parentLevel", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Level); command.Parameters.Add("@parentIndex", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Index); returnCode = Convert.ToInt32(command.ExecuteNonQuery()); SqlCeCommand commandMaxId = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandMaxCapturePointIdTextConv, conn); capturePointsIds.Add(Convert.ToInt32(commandMaxId.ExecuteScalar())); } } finally { conn.Close(); } return returnCode; }
protected void Page_Load(object sender, EventArgs e) { SqlCeConnection conn = new SqlCeConnection(connString); // SqlConnection conn.Open(); SqlCeCommand command = new SqlCeCommand("SELECT * FROM student WHERE pbr=@pbr", conn); // ... WHERE pbr=" + pbr.ToString() NE - SQL Injection command.Parameters.AddWithValue("pbr", 10000); // ovako rjestiti i labose sa usernmom i passwordom SqlCeDataReader dr = command.ExecuteReader(); // za labose: dr.HasRows // HasRows ne radi na Sql Compact :( // workaround: // bool hasRow = dr.Read(); // if (hasRow) { // napravi nesto // } // ili: (tnx Svjetlana) // SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Scrollable); // if (dr.HasRows) // ima rezultata txtStudenti.Text = ""; while (dr.Read()) { txtStudenti.Text += dr[0] + " " + dr["ime"] + " " + dr["prezime"] + " "+ dr["pbr"] + "\n"; } dr.Close(); command = new SqlCeCommand("SELECT COUNT(*) FROM student", conn); int broj = (int)command.ExecuteScalar(); txtStudenti.Text += broj.ToString(); conn.Close(); }
public void login() { //checks fields are empty if (txt_user_name.Text != "" & txt_password.Text != "") { string queryText = @"SELECT Count(*) FROM users WHERE user_name = @user_name AND password = @password"; //makes connection to database, and writes a insert query using (SqlCeConnection cn = new SqlCeConnection(@"Data Source=C:\temp\Mydatabase.sdf ")) using (SqlCeCommand cmd = new SqlCeCommand(queryText, cn)) { cn.Open(); //opens connection, sets fields with names like user_name cmd.Parameters.AddWithValue("@user_name", txt_user_name.Text); cmd.Parameters.AddWithValue("@password", txt_password.Text); int result = (int)cmd.ExecuteScalar(); if (result > 0) { this.Hide(); Home frmchild = new Home(); frmchild.Show(); frmchild.WriteToText(txt_user_name.Text); } else { MessageBox.Show("User Not Found!"); } } } }
public static Guid GetApplicationId(string connectionString, string applicationName) { using (SqlCeConnection conn = new SqlCeConnection(connectionString)) { using (SqlCeCommand cmd = new SqlCeCommand("SELECT ApplicationId FROM [aspnet_Applications] " + "WHERE ApplicationName = @ApplicationName", conn)) { cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName; conn.Open(); var applicationId = cmd.ExecuteScalar(); if (applicationId == null) { cmd.Parameters.Clear(); cmd.CommandText = "INSERT INTO [aspnet_Applications] (ApplicationId, ApplicationName, LoweredApplicationName, Description) VALUES (@ApplicationId, @ApplicationName, @LoweredApplicationName, @Description)"; applicationId = Guid.NewGuid(); cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = applicationId; cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName; cmd.Parameters.Add("@LoweredApplicationName", SqlDbType.NVarChar, 256).Value = applicationName.ToLowerInvariant(); cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 256).Value = String.Empty; cmd.ExecuteNonQuery(); } return (Guid)applicationId; } } }
private void postavi_labele() { //kreiraj novu praznu konekciju SqlCeConnection conn = new SqlCeConnection(); //dohvati tekst za povezivanje na bazu iz web.config i postavi g ana konekciju string connStr = WebConfigurationManager.ConnectionStrings["studenti"].ConnectionString; conn.ConnectionString = connStr; //kreiraj novu naredbu i postavi SQL kao i konekciju SqlCeCommand cmd = new SqlCeCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT COUNT(*) FROM student"; cmd.CommandType = System.Data.CommandType.Text; //tip je SQL naredba (a ne tablica ili stor.proc) //otvori komunikaciju sa bazom conn.Open(); int brojStud = (int)cmd.ExecuteScalar(); //izvrši vrati jednu vrijednost Label1.Text = "U bazi imamo " + brojStud.ToString() + " studenata!"; cmd.CommandText = "SELECT * FROM student"; //sada ih vrati kao datareader SqlCeDataReader dr = cmd.ExecuteReader(); Label2.Text = "ID - Ime - Prezime" + "<br>"; // na sql Expressu ima i dr.HasRows da vidimo je li prazan if(dr.HasRows)) while (dr.Read()) { //čitaj red po red dok ne dođeš do kraja Label2.Text += dr["stud_id"].ToString() + " - " + dr["ime"] + " - " + dr["prezime"] + " - " + dr["faks"] + "<br>"; } conn.Close(); }
/// <summary> /// /// </summary> /// <param name="connectionString"></param> /// <param name="commandType"></param> /// <param name="commandText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public static object ExecuteScalar( string connectionString, CommandType commandType, string commandText, params SqlCeParameter[] commandParameters ) { object retVal; try { using (SqlCeConnection conn = SqlCeContextGuardian.Open(connectionString)) { using (SqlCeCommand cmd = new SqlCeCommand(commandText, conn)) { AttachParameters(cmd, commandParameters); Debug.WriteLine("---------------------------------SCALAR-------------------------------------"); Debug.WriteLine(commandText); Debug.WriteLine("----------------------------------------------------------------------------"); retVal = cmd.ExecuteScalar(); } } return retVal; } catch (Exception ee) { throw new SqlCeProviderException("Error running Scalar: \nSQL Statement:\n" + commandText + "\n\nException:\n" + ee.ToString()); } }
public static long? ExecuteNonQuery(string query) { try { SqlCeConnection conn = CaseStudyDB.GetConnection(); conn.Open(); SqlCeCommand cmd = new SqlCeCommand(query, conn); cmd.ExecuteScalar(); cmd = new SqlCeCommand("SELECT @@IDENTITY", conn); object queryReturn = cmd.ExecuteScalar(); long value; long.TryParse(queryReturn.ToString(),out value); conn.Close(); if(value != 0) { return value; } else { return null; } } catch (Exception ex) { MessageBox.Show(string.Format("Error exeuting query: {0}", ex.Message)); return null; } }
//changing current user's password private void button1_Click(object sender, EventArgs e) { //check if old password matches SqlCeCommand cm = new SqlCeCommand("SELECT COUNT(*) FROM Password WHERE UserName=@user AND Password=@pas ", Form1.con); cm.Parameters.AddWithValue("@user", label3.Text); cm.Parameters.AddWithValue("@pas", EncryptSHA512Managed(textBox1.Text)); if ((int)cm.ExecuteScalar() == 1) { try { //change password SqlCeCommand cmd = Form1.con.CreateCommand(); cmd.CommandText = "UPDATE Password SET Password = @pass WHERE Username = @username"; cmd.Parameters.AddWithValue("@username", label3.Text); cmd.Parameters.AddWithValue("@pass", EncryptSHA512Managed(textBox2.Text)); cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } MessageBox.Show("Password modifyed!"); textBox1.Text = ""; textBox1.Text = ""; } else { MessageBox.Show("Wrong Password Inserted!"); } }
public static int CountUnsynchronizedBackroudWorker(SqlCeConnection databaseConnection) { if (databaseConnection == null) throw new ArgumentNullException("databaseConnection"); SqlCeCommand com = new SqlCeCommand(("SELECT COUNT(*) FROM Scanari WHERE Sincronizat=0"), databaseConnection); return (Int32)com.ExecuteScalar(); }
private void btnLogin_Click(object sender, EventArgs e) { dbh.TestConnection(); dbh.OpenConnectionToDB(); bool exist = false; string username = txtUsername.Text; string password = txtPassword.Text; txtUsername.Text = ""; txtPassword.Text = ""; using (SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM [tblUsers] WHERE Username = @Username AND Password = @Password", dbh.GetCon())) { cmd.Parameters.AddWithValue("Username", username); cmd.Parameters.AddWithValue("Password", password); SqlCeDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader.GetString(1) == username && reader.GetString(2) == password) { userID = reader.GetInt32(0); exist = true; break; } } } if (exist) { bool admin; using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(*) from [tblUsers] WHERE Username = @Username AND IsAdmin = 1", dbh.GetCon())) { cmd.Parameters.AddWithValue("Username", username); admin = (int)cmd.ExecuteScalar() > 0; } dbh.CloseConnectionToDB(); if (admin) { frmAdmin.Show(); } else { frmPlayer = new frmPlayer(frmRanking, username, this, userID); frmPlayer.Show(); //frmPlayer.Show(); } } else { dbh.CloseConnectionToDB(); MessageHandler.ShowMessage("Wrong username and/or password."); } }
/// <summary> /// A SqlCeConnection extension method that executes the scalar operation. /// </summary> /// <param name="this">The @this to act on.</param> /// <param name="commandFactory">The command factory.</param> /// <returns>An object.</returns> public static T ExecuteScalarTo <T>(this SqlCeConnection @this, Action <SqlCeCommand> commandFactory) { using (SqlCeCommand command = @this.CreateCommand()) { commandFactory(command); return(command.ExecuteScalar().To <T>()); } }
private int GetIdentity(IDbConnection connection) { using (var command = new SqlCeCommand("SELECT @@IDENTITY", connection as SqlCeConnection)) { command.Transaction = CurrentTransaction as SqlCeTransaction; object id = command.ExecuteScalar(); return(Convert.ToInt32(id)); } }
internal static string getMatchTypeName(int matchTypeId) { string style = ""; SqlCeCommand com = new SqlCeCommand("SELECT Name FROM tblmatchType WHERE matchTypeId=@p1", connection.CON); com.Parameters.AddWithValue("@p1", matchTypeId); style = com.ExecuteScalar().ToString(); return(style); }
internal static int getNextShotCode() { int code = 0; SqlCeCommand com = new SqlCeCommand("SELECT MAX(ID)+1 FROM tblBattingShots", connection.CON); int.TryParse(com.ExecuteScalar().ToString(), out code); com.Dispose(); return(code); }
private int GetRowCount(string tableName) { var books = new List <Book>(); using (SqlCeCommand cmd = new SqlCeCommand(string.Format("SELECT COUNT(*) FROM {0}", tableName), Connection)) { return((int)cmd.ExecuteScalar()); } }
internal static clsUser.userRole getUserRole(int userId) { int role = 0; SqlCeCommand com = new SqlCeCommand("SELECT role FROM tblUser WHERE userId=@p1", connection.CON); com.Parameters.AddWithValue("@p1", userId); int.TryParse(com.ExecuteScalar().ToString(), out role); return((clsUser.userRole)role); }
private void button1_Click(object sender, EventArgs e) { if (textBox1.Text.Length != 0) { var numePrenume = textBox1.Text.Trim().Split(' '); if (numePrenume.Count() > 1) { var nume = numePrenume[0]; var prenume = numePrenume[1]; var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Angajati.sdf"); using (var conn = new SqlCeConnection(connString)) { try { conn.Open(); var query = "SELECT COUNT(*) FROM info WHERE Nume='" + nume + "' AND Prenume='" + prenume + "'"; var command = new SqlCeCommand(query, conn); var dataAdapter = new SqlCeDataAdapter(command); var dataTable = new DataTable(); dataAdapter.Fill(dataTable); //checks if there's the searched record is in the db. int infoCount = (int)command.ExecuteScalar(); if (infoCount > 0) { Info form = new Info(nume, prenume); form.Show(); } else { MessageBox.Show("Nu exista un angajat cu acest nume"); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } else { MessageBox.Show("Nu ai introdus prenumele"); } } else { MessageBox.Show("Nu ai introdus nici un nume!"); } }
public int GetApplicationInstanceCountByAssetName(string aPublisher, string aApplicationName, int aAssetId) { string commandText = String.Format( "SELECT COUNT(*) " + "FROM APPLICATIONS ap " + "LEFT JOIN APPLICATION_INSTANCES ai ON (ai._applicationid = ap._applicationid) " + "WHERE ai._assetid = '{0}' " + "AND ap._publisher = '{1}' " + "AND ap._name = '{2}'", aAssetId, aPublisher, aApplicationName); int returnValue = 0; try { if (compactDatabaseType) { using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection()) { using (SqlCeCommand command = new SqlCeCommand(commandText, conn)) { returnValue = (int)command.ExecuteScalar(); } } } else { using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection()) { using (SqlCommand command = new SqlCommand(commandText, conn)) { returnValue = (int)command.ExecuteScalar(); } } } } catch (SqlException ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } catch (SqlCeException ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } catch (Exception ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } return(returnValue); }
public static long GetIdByBarcode(string caseBarcode) { using (SqlCeCommand query = dbWorker.NewQuery("SELECT Id FROM Cases WHERE BarCode=@BarCode")) { query.AddParameter("BarCode", caseBarcode); object result = query.ExecuteScalar(); return result == null ? 0 : Convert.ToInt64(result); } }
internal static string getShotName(int shotId) { string style = ""; SqlCeCommand com = new SqlCeCommand("SELECT Name FROM tblBattingShots WHERE ID=@p1", connection.CON); com.Parameters.AddWithValue("@p1", shotId); style = com.ExecuteScalar().ToString(); return(style); }
internal static int getNextMatchTypeCode() { int code = 0; SqlCeCommand com = new SqlCeCommand("SELECT MAX(matchTypeId)+1 FROM tblmatchType", connection.CON); int.TryParse(com.ExecuteScalar().ToString(), out code); com.Dispose(); return(code); }
private void IrsaliyeBaslikKaydet() { Int64 vade = 0; try { vade = Convert.ToInt64(txtIrsaliyeNo.Text); } catch { MessageBox.Show("Irsaliye No alanýna sayýsal bir deðer girilmelidir"); return; } SqlCeConnection CeConn = new SqlCeConnection("DataSource=ankara.sdf"); CeConn.Open(); DateTime IrsaliyeTarihi = dc.Getdate; Int64 SipNo = 0; string dt = DateTime.Now.ToString("MM/dd/yyyy"); string Irsdt = IrsaliyeTarihi.ToString("MM/dd/yyyy"); string sql = "insert into " + tabloAd + "(Musteri_Kodu, Belge_No, Tur, Musteri_Adi, Plasiyer_Kodu,Siparis_Tarihi, Teslim_Tarihi, " + " Vade_Gunu, Odeme_Sekli, Teslim_Alan)" + " VALUES (" + " '" + carino.Text + "'," + " '" + txtIrsaliyeNo.Text + "'," + " '" + Tur + "'," + " '" + label7.Text + "'," + " '" + label8.Text + "'," + " '" + dt + "'," + " '" + Irsdt + "',1,'k'," + " '" + comboBox2.Text + "')"; try { SqlCeCommand cmd = new SqlCeCommand(sql, CeConn); cmd.ExecuteNonQuery(); cmd.CommandText = "select @@identity"; SipNo = Convert.ToInt64(cmd.ExecuteScalar().ToString()); SiparisNo = SipNo; } finally { CeConn.Close(); } DetayKaydet dk = new DetayKaydet(SipNo, label8.Text, tabloAd, false, Tur, Convert.ToInt64(txtIrsaliyeNo.Text)); dk.Show(); this.Close(); }
/// <summary> /// Return the database index of the specified Supplier Record /// </summary> /// <param name="name"></param> /// <returns></returns> public int SupplierFind(string aName) { if (isDebugEnabled) { logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in"); } int lItemId = 0; if (compactDatabaseType) { try { using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection()) { string commandText = "SELECT _SUPPLIERID FROM SUPPLIERS WHERE _name = @cName"; using (SqlCeCommand commandReturnValue = new SqlCeCommand(commandText, conn)) { commandReturnValue.Parameters.AddWithValue("@cName", aName); object result = commandReturnValue.ExecuteScalar(); if ((result != null) && (result.GetType() != typeof(DBNull))) { lItemId = Convert.ToInt32(result); } } } } catch (SqlCeException ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } catch (Exception ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } } else { AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess(); lItemId = lAuditWizardDataAccess.SupplierFind(aName); } if (isDebugEnabled) { logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out"); } return(lItemId); }
public override long insert_version_and_get_version_id(string repository_path, string repository_version, bool is_dry_run) { string version = get_version(null); long lVersion = version == null ? -1 : Int64.Parse(version); long lNewVersion = Int64.Parse(repository_version); SqlCeConnection conn = null; try { using (conn = new SqlCeConnection(connection_string)) { conn.Open(); DateTime now = DateTime.Now; if (lNewVersion > lVersion && !is_dry_run) { using (SqlCeCommand cmd = conn.CreateCommand()) { cmd.Parameters.AddWithValue("repository_version", repository_version); cmd.Parameters.AddWithValue("repository_path", ((object)repository_path) ?? DBNull.Value); cmd.Parameters.AddWithValue("now", now); cmd.Parameters.AddWithValue("currentUser", GetCurrentUser()); cmd.CommandText = "INSERT INTO [RoundhousE_Version]" + "([repository_path]" + ",[version]" + ",[entry_date]" + ",[modified_date]" + ",[entered_by])" + " VALUES(" + "@repository_path " + ", @repository_version " + ", @now " + ", @now " + ", @currentUser)"; cmd.ExecuteNonQuery(); } } using (SqlCeCommand cmdLatestVersionId = conn.CreateCommand()) { cmdLatestVersionId.CommandText = "SELECT TOP 1 [id] FROM [RoundhousE_Version] ORDER BY entry_date DESC"; return(Int64.Parse(cmdLatestVersionId.ExecuteScalar().ToString())); } } } catch (Exception ex) { throw new ApplicationException(string.Format("Unable to insert new version in RoundhousE_Version table. Error was {0}", ex.Message)); } }
private void GroupByUnionFillCells(Excel.Range cells, DateTime time) { // 填写前四行 cells[2, 1] = "各县(市)区总工会经费收解返计算表"; string[] labels = new string[] { "序号", "单位名称", "地税机关代收金额", "应上解市总经费", "实际上解", "实际返拨经费" }; cells.Range[cells[4, 1], cells[4, 6]].Value = labels; string sql = @"SELECT SUM(f.Received) FROM CompanyInfo c INNER JOIN Funds f ON c.CompanyId = f.CompanyId WHERE f.Time = @time AND c.[Union] = @union"; int lastRow = 6; // 填写中间部分 using (var cmd = new SqlCeCommand(sql, database_.GetConnection())) { cmd.Parameters.AddWithValue("@time", time); cmd.Parameters.Add("@union", SqlDbType.NVarChar); string ratio = GetSetting("应上解市总经费的比例"); foreach (string union in categories_["Union"]) { cmd.Parameters[1].Value = union; int row; if (union == "三门峡市总工会") { row = 5; // 这两个工会必须在其他工会前面 } else if (union == "开发区工会办事处") { row = 6; } else { row = ++lastRow; } cells[row, 2] = union; cells[row, 3] = Math.Round((double)cmd.ExecuteScalar(), 2); if (row >= 7) { cells[row, 4].Formula = "=C" + row + "*" + ratio; cells[row, 6].Formula = "=C" + row + "-E" + row; } } } // 填写后两行 cells[lastRow + 1, 2] = "2-" + (lastRow - 4) + "小计"; cells[lastRow + 2, 2] = "合计"; for (int col = 3; col <= 6; col++) { char charCol = (char)(col + 64); cells[lastRow + 1, col].Formula = "=SUM(" + charCol + "6:" + charCol + lastRow + ")"; cells[lastRow + 2, col].Formula = "=" + charCol + "5" + "+" + charCol + (lastRow + 1); } // 填写第一列 for (int row = 5; row <= lastRow + 2; row++) { cells[row, 1] = row - 4; } }
private bool checkIncludeMapOrInfo(long id) { using (SqlCeCommand query = dbWorker.NewQuery("SELECT Count(1) FROM Maps WHERE ParentId=@Id")) { query.AddParameter("Id", id); object countObj = query.ExecuteScalar(); return(Convert.ToInt32(countObj) != 0); } }
private bool RouteAlreadyExists(string routeIdent, SqlCeConnection conn) { SqlCeCommand existsCmd = new SqlCeCommand(); existsCmd.Connection = _dataConn; existsCmd.CommandText = "Select count(*) from RouteIdents where RouteId = '" + routeIdent + "'"; int count = (int)existsCmd.ExecuteScalar(); return(count > 0); }
/// <summary>Чи вже існує штрихкод?</summary> /// <param name="barcode">Штрихкод</param> public static bool IsBarcodeExist(string barcode) { using (SqlCeCommand query = dbWorker.NewQuery(string.Format(ACCESSORY_QUERY_COMMAND, "1"))) { query.AddParameter("Barcode", barcode); object result = query.ExecuteScalar(); return(result != null); } }
public void SGetDEntriesTotalsAll(out int iMinutesAll, out int iCountAll) { object oResults; oResults = _sqcGetDEntriesMinutesAll.ExecuteScalar(); iMinutesAll = (oResults != null) ? (int)oResults : 0; oResults = _sqcGetDEntriesCountAll.ExecuteScalar(); iCountAll = (oResults != null) ? (int)oResults : 0; }
private object ExecuteScalar(string commandText) { object val; using (var cmd = new SqlCeCommand(commandText, _cn)) { val = cmd.ExecuteScalar(); } return(val); }
public static object ExecuteScalar(string connectionString, SqlCeCommand command) { using (var conn = new SqlCeConnection(connectionString)) { conn.Open(); ResetParameterNullValue(command); command.Connection = conn; return(command.ExecuteScalar()); } }
public void pv_Update_RegistrBase(String ls_UpdateParam, String ls_UpdateData, String ls_WhereParam, String ls_WhereData, String ls_BasePath) { sql_SQL_Engine = new SqlCeEngine("Data Source='" + ls_BasePath + "';"); sql_SQL_Connection = new SqlCeConnection(sql_SQL_Engine.LocalConnectionString); sql_SQL_Connection.Open(); sql_SQL_Command = sql_SQL_Connection.CreateCommand(); sql_SQL_Command.CommandText = "UPDATE Registration SET " + ls_UpdateParam + "='" + ls_UpdateData + "' WHERE " + ls_WhereParam + " = '" + ls_WhereData + "'"; sql_SQL_Command.ExecuteScalar(); sql_SQL_Connection.Close(); }
float GetPrice(string urunKodu, string field) { CeConn.Open(); string sql = "select " + field + " from stok where SICIL_KODU = '" + urunKodu + "'"; SqlCeCommand cmd = new SqlCeCommand(sql, CeConn); float i = Convert.ToSingle(cmd.ExecuteScalar()); CeConn.Close(); return(i); }
public void count() { SqlCeCommand cm2 = new SqlCeCommand("SELECT COUNT(*) FROM UploadHist", cn3); Int32 count = (Int32)cm2.ExecuteScalar(); count++; string s = "UP00" + count.ToString(); label8.Text = s.ToUpper(); }
float SetIskonto1(string Field) { CeConn.Open(); string sql = "select " + Field + " from stok where SICIL_KODU = '" + SicilAdlaricomboBox.SelectedValue.ToString() + "'"; SqlCeCommand cmd = new SqlCeCommand(sql, CeConn); float i = Convert.ToSingle(cmd.ExecuteScalar()); CeConn.Close(); return(i); }
public void save1() { SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.conne); SqlCeCommand cmd = new SqlCeCommand(); Double r = dataGridView1.RowCount; conn.Open(); try { cmd.Connection = conn; cmd.CommandText = "INSERT INTO Estimates ([Cust_name],[Ref],[Date],[Amount]) VALUES('" + salution.Text + "','" + textBox2.Text + "','" + dateTimePicker1.Text + "','" + textBox8.Text + "')"; cmd.ExecuteNonQuery(); foreach (ListViewItem li in listView.Items) { string it = li.SubItems[0].Text; string hsn = li.SubItems[1].Text; string qt = li.SubItems[2].Text; string rat = li.SubItems[3].Text; string mrp = li.SubItems[4].Text; string gst = li.SubItems[5].Text; string tamt = li.SubItems[6].Text; string amt = li.SubItems[7].Text; string q1 = "INSERT INTO Est_Details ([Cust_name],[Ref],[Date],[Expire_date],[Items],[Qty],[Rate],[Amt],[Sub_Total],[Discount],[Adjustment],[Total],[GST],[HSN],[MRP],[tamt])VALUES ('" + salution.Text + "','" + r + "','" + dateTimePicker1.Text + "','" + dateTimePicker2.Text + "','" + it + "','" + qt + "','" + rat + "','" + amt + "','" + textBox3.Text + "','" + textBox5.Text + "','" + textBox7.Text + "','" + textBox8.Text + "','" + gst + "','" + hsn + "','" + mrp + "','" + tamt + "')"; SqlCeCommand cmd2 = new SqlCeCommand(q1, conn); cmd2.ExecuteNonQuery(); } if (checkBox1.Checked) { cmd.CommandText = "select DISTINCT count([Prod_name]) from goods"; int t = (int)cmd.ExecuteScalar(); // MessageBox.Show("" + t); DataRow[] dr = ds18.Tables[0].Select(); for (int j = 0; j < t; j++) { Int32 st1 = Convert.ToInt32(dr[j]["Qty"]); String s = Convert.ToString(dr[j]["Prod_name"]); cmd.CommandText = "UPDATE goods SET [Qty]='" + st1 + "' where [Prod_name]='" + s + "'"; cmd.ExecuteNonQuery(); // MessageBox.Show(s); // MessageBox.Show("" + st1); } } conn.Close(); MessageBox.Show("Inserted Data Successfully"); son = textBox11.Text; reload(); cle(); inno(); panel1.Visible = false; } catch (Exception o) { MessageBox.Show("Erorr " + o); } }
private void BtnAñadir_Click_1(object sender, EventArgs e) { try { if (cnn.State == ConnectionState.Closed) { cnn.Open(); } var cmd = new SqlCeCommand("select count(1) from inventario where lote= @lote", cnn); cmd.Parameters.AddWithValue("@lote", txtCB.Text); if ((int)cmd.ExecuteScalar() > 0) { if (MessageBox.Show("Ya existe un registro con ese codigo de lote \n ¿Desea agregar uno nuevo?", "Alerta", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1) == DialogResult.No) { this.Clean(); return; } } var idInventarioServer = "0"; if (lblIdArt.Text != "") { idInventarioServer = lblIdArt.Text; } var sqlCeCommand = new SqlCeCommand("INSERT INTO Inventario VALUES(@CB,@Cantidad,@Medida,@Almacen,@Lote,@Longitud,@Norma,@Espesor,@Desc,@ubicacion,@idUsuario,@idArticulo,@IdCon)", this.cnn); sqlCeCommand.Parameters.AddWithValue("@IdCon", this.IdConexion); sqlCeCommand.Parameters.AddWithValue("@CB", txtCB.Text); sqlCeCommand.Parameters.AddWithValue("@Cantidad", 1); sqlCeCommand.Parameters.AddWithValue("@Medida", txtMedida.Text); sqlCeCommand.Parameters.AddWithValue("@Lote", txtLote.Text); sqlCeCommand.Parameters.AddWithValue("@Longitud", txtLongitud.Text); sqlCeCommand.Parameters.AddWithValue("@Norma", txtNorma.Text); sqlCeCommand.Parameters.AddWithValue("@Espesor", txtEspesor.Text); sqlCeCommand.Parameters.AddWithValue("@Desc", txtDesc.Text); sqlCeCommand.Parameters.AddWithValue("@Almacen", txtAlmacen.Text); sqlCeCommand.Parameters.AddWithValue("@ubicacion", txtUbicacion.Text); sqlCeCommand.Parameters.AddWithValue("@idArticulo", idInventarioServer); sqlCeCommand.Parameters.AddWithValue("@idUsuario", idusuario); sqlCeCommand.ExecuteReader(); this.CargarInventario(); this.Clean(); } catch (Exception ex) { int num = (int)MessageBox.Show(ex.Message); } finally { this.cnn.Close(); } }
/// <summary> /// Get the database index of the last operation in the database /// </summary> /// <returns></returns> public int OperationGetLastIndex() { if (isDebugEnabled) { logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in"); } int lReturnID = 0; if (compactDatabaseType) { try { using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection()) { string commandText = "SELECT max(_OPERATIONID) FROM OPERATIONS"; using (SqlCeCommand command = new SqlCeCommand(commandText, conn)) { object result = command.ExecuteScalar(); if ((result != null) && (result.GetType() != typeof(DBNull))) { lReturnID = (int)result; } } } } catch (SqlCeException ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } catch (Exception ex) { Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine + "Please see the log file for further details."); logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex); } } else { AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess(); lReturnID = lAuditWizardDataAccess.OperationGetLastIndex(); } if (isDebugEnabled) { logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out with id : " + lReturnID); } return(lReturnID); }
public static bool CheckEmployee(Employee employee, out string error) { error = null; if (string.IsNullOrEmpty(employee.INN) && string.IsNullOrEmpty(employee.Passport)) { return(true); } using (SqlCeConnection connection = new SqlCeConnection(GetConnectionString())) { connection.Open(); using (SqlCeCommand command = new SqlCeCommand("select count(1) from Employee where INN = @INN", connection)) { if (!string.IsNullOrEmpty(employee.INN)) { command.Parameters.Add("INN", System.Data.SqlDbType.NChar).Value = employee.INN; if (Convert.ToInt32(command.ExecuteScalar()) > 0) { error = $"Ошибка ИНН '{employee.INN}' принадлежит другому сотруднику"; return(false); } } if (!string.IsNullOrEmpty(employee.Passport)) { command.Parameters.Clear(); command.CommandText = "select count(1) from Employee where PASSPORT = @PASSPORT"; command.Parameters.Add("PASSPORT", System.Data.SqlDbType.NChar).Value = employee.Passport; if (Convert.ToInt32(command.ExecuteScalar()) > 0) { error = $"Ошибка номер паспорта '{employee.Passport}' принадлежит другому сотруднику"; return(false); } } } } return(true); }
//Block Memory Leak public static bool TableExists(SqlCeConnection connection, string tableName) { using (var command = new SqlCeCommand()) { command.Connection = connection; var sql = string.Format("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '{0}'", tableName); command.CommandText = sql; var count = Convert.ToInt32(command.ExecuteScalar()); return (count > 0); } }
public static long GetNewId(string tableName) { string command = string.Format("SELECT [{0}]+1 Id FROM {1} ORDER BY [{0}] DESC", IDENTIFIER_NAME, tableName); using (SqlCeCommand query = dbWorker.NewQuery(command)) { object newId = query.ExecuteScalar(); return(Convert.ToInt64((newId ?? 1))); } }
public static long FindCaseId(long accessoryId, TypeOfAccessories typeOfAccessories) { string sql = string.Format(@"select Id from cases where {0} = @accessoryId", typeOfAccessories); using (SqlCeCommand query = dbWorker.NewQuery(sql)) { query.AddParameter("accessoryId", accessoryId); object idObj = query.ExecuteScalar(); return(idObj == null ? 0 : Convert.ToInt64(idObj)); } }
private static int GetFoundRowsForExistingForm(string formName, SqlCeConnection conn) { int returnCode = -1; try { SqlCeCommand command = new SqlCeCommand(Form_Updated_SQL.commandGetFormCount, conn); command.Parameters.Add("@formName", formName); returnCode = Convert.ToInt32(command.ExecuteScalar()); } catch (Exception ex) { } return returnCode; }
private void button10_Click(object sender, EventArgs e) { if (!(Library.IsNullOrEmpty(municipioTextBox.Text))) { string m = Library.removeChar(municipioTextBox.Text); try { if (conn.State == ConnectionState.Closed) conn.Open(); command = new SqlCeCommand("insert into cidade (nome) values (@cidade)", conn); command.Parameters.Add("cidade", m); command.ExecuteScalar(); command = new SqlCeCommand("select nome from cidade", conn); adapter = new SqlCeDataAdapter(command); DataTable t = new DataTable(); adapter.Fill(t); Usuario.Municipios.Clear(); foreach (DataRow r in t.Rows) { object c = r["nome"]; Usuario.Municipios.Add(c); //municipioComboBox.Items.Add(c); } if (conn.State == ConnectionState.Open) conn.Close(); //this.cidadeTableAdapter.Insert(municipioTextBox.Text); //this.cipDatabaseDataSet.AcceptChanges(); //this.cipDatabaseDataSet.Cidade.AcceptChanges(); //this.cidadeTableAdapter.Fill(cipDatabaseDataSet.Cidade); municipioTextBox.Text = ""; //MessageBox.Show("Cidade inserida com sucesso!"); this.Close(); } catch (Exception ex) { if (ex.Message.Contains("duplicate")) MessageBox.Show("Cidade já cadastrada."); else MessageBox.Show(ex.Message); } } else { MessageBox.Show("Informe o nome da cidade"); municipioTextBox.Focus(); } }
public void ChangeCountAdd(Int32 aId_PlacementActivityLine, Int32 aCount) { using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString)) { sqlConnection.Open(); using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction()) { try { Int32 id_ActivityLine = -1; using (SqlCeCommand sqlCommand = new SqlCeCommand("SELECT al.Id_ActivityLine FROM ActivityLine AS al " + "WHERE al.Id_PlacementActivityLine = @Id_PlacementActivityLine AND al.BinCode = N''", sqlConnection, sqlTran)) { sqlCommand.CommandType = CommandType.Text; sqlCommand.Parameters.Add(new SqlCeParameter("@Id_PlacementActivityLine", aId_PlacementActivityLine)); object obj = sqlCommand.ExecuteScalar(); if (obj != null) { id_ActivityLine = (Int32)obj; } } if (id_ActivityLine == -1) { using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO ActivityLine(Id_PlacementActivityLine, BinCode, ProcessedQty) " + "VALUES (@Id_PlacementActivityLine, N'', @Count)", sqlConnection, sqlTran)) { sqlCommand.CommandType = CommandType.Text; sqlCommand.Parameters.Add(new SqlCeParameter("@Id_PlacementActivityLine", aId_PlacementActivityLine)); sqlCommand.Parameters.Add(new SqlCeParameter("@Count", aCount)); sqlCommand.ExecuteNonQuery(); } } else { using (SqlCeCommand sqlCommand = new SqlCeCommand("UPDATE ActivityLine SET ProcessedQty = ProcessedQty + @Count " + "WHERE Id_ActivityLine = @Id_ActivityLine", sqlConnection, sqlTran)) { sqlCommand.CommandType = CommandType.Text; sqlCommand.Parameters.Add(new SqlCeParameter("@Id_ActivityLine", id_ActivityLine)); sqlCommand.Parameters.Add(new SqlCeParameter("@Count", aCount)); sqlCommand.ExecuteNonQuery(); } } sqlTran.Commit(); } catch { sqlTran.Rollback(); throw; } } } }
public static string GetDescription(string name) { using (var connection = new SqlCeConnection(_connectionString)) { connection.Open(); string sql = "select description from Foo where name=@name"; var command = new SqlCeCommand(sql, connection); command.Parameters.AddWithValue("@name", name); object result = command.ExecuteScalar(); return result != null ? result.ToString() : null; } }
public static int InsertNewUserSession(SqlCeConnection conn) { int value = 0; SqlCeCommand command = new SqlCeCommand(User_Sessions_SQL.commandInsertNewUserSession, conn); command.Parameters.Add("@userName", MonitorObject.username); command.Parameters.Add("@loginTime", MonitorObject.loginTime); command.Parameters.Add("@logoutTime", MonitorObject.logoutTime); value = Convert.ToInt32(command.ExecuteNonQuery()); SqlCeCommand commandMaxId = new SqlCeCommand(User_Sessions_SQL.commandMaxId, conn); value = Convert.ToInt32(commandMaxId.ExecuteScalar()); return value; }
public long Count(FieldCriteria[] criterias) { try { connection.Open(); SqlCeCommand command = new SqlCeCommand(SqlQueryBuilder.BuildCountQuery("Entity", criterias), connection); object o = command.ExecuteScalar(); return Convert.ToInt64(o); } finally { connection.Close(); } }
public static object GetAppConfigValueByKey(ApplicationConfigKeys applicationConfigKeys) { SqlCeConnection conn = BackEndUtils.GetSqlConnection(); SqlCeCommand command = new SqlCeCommand(Application_Settings_SQL.commandSelectFromAppConfigByKey, conn); try { conn.Open(); command.Parameters.Add("@id", applicationConfigKeys); return command.ExecuteScalar(); } finally { conn.Close(); } return null; }
public static Guid GetApplicationId(string connectionString, string applicationName) { using (SqlCeConnection conn = new SqlCeConnection(connectionString)) { using (SqlCeCommand cmd = new SqlCeCommand("SELECT ApplicationId FROM [aspnet_Applications] " + "WHERE ApplicationName = @ApplicationName", conn)) { cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName; conn.Open(); return (Guid)(cmd.ExecuteScalar()); } } }
private static int GetSimpleRecIdByName(string optionName) { SqlCeConnection conn = BackEndUtils.GetSqlConnection(); int value = 0; try { conn.Open(); SqlCeCommand commandRecIdByName = new SqlCeCommand(Simple_Recommendation_SQL.commandGetSimpleRecIdByName, conn); commandRecIdByName.Parameters.Add("@SR_name", optionName); value = Convert.ToInt32(commandRecIdByName.ExecuteScalar()); } finally { conn.Close(); } return value; }
public static int GetTotalAdvanceReplacementUsageCount(int captureEventId) { int total = 0; SqlCeConnection conn = BackEndUtils.GetSqlConnection(); SqlCeCommand command = new SqlCeCommand(Advanced_Replacements_SQL.commandSelectSumOfAllReplacementUsage, conn); try { conn.Open(); command.Parameters.AddWithValue("@capturePointId",captureEventId); total = Convert.ToInt32(command.ExecuteScalar()); } finally { conn.Close(); } return total; }
public static int GetLoginCountByUserId(int userId) { int selectedLoginCount = -1; SqlCeConnection conn = BackEndUtils.GetSqlConnection(); try { conn.Open(); SqlCeCommand command = new SqlCeCommand(UserStatus_SQL.commandGetLoginCountByUserId, conn); command.Parameters.Add("@id", userId); selectedLoginCount = Convert.ToInt32(command.ExecuteScalar()); } finally { conn.Close(); } return selectedLoginCount; }
public static int GetUserIdByUsername(string username) { int selectedUserId = -1; SqlCeConnection conn = BackEndUtils.GetSqlConnection(); try { conn.Open(); SqlCeCommand command = new SqlCeCommand(UserStatus_SQL.commandGetUserIdByUserName, conn); command.Parameters.Add("@username", username); selectedUserId = Convert.ToInt32(command.ExecuteScalar()); } finally { conn.Close(); } return selectedUserId; }
public Boolean ActiveLineIsBinCodeEmpty() { Object obj; using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString)) { sqlConnection.Open(); using (SqlCeCommand sqlCommand = new SqlCeCommand("SELECT TOP (1) al.Id_ActivityLine FROM ActivityLine AS al " + "WHERE al.BinCode = N''", sqlConnection)) { sqlCommand.CommandType = CommandType.Text; obj = sqlCommand.ExecuteScalar(); } } return (obj != null); }
public bool didMemberAttendSingleMeeting(Meeting meeting, Member member) { string query = "SELECT COUNT(*) FROM attendance WHERE member_id = " + member.Id + " AND meeting_id = " + meeting.Id + ""; int returnCount = 0; using (SqlCeConnection con = this.connectionManager.getConnection()) { con.Open(); using (SqlCeCommand command = new SqlCeCommand(query, con)) { command.CommandType = CommandType.Text; returnCount = (Int32)command.ExecuteScalar(); } } return returnCount > 0 ? true : false; }
public string ExecuteScalar(string sql, params SqlCeParameter[] sps) { SqlCeConnection conn = CreateConn(); SqlCeCommand cmd = new SqlCeCommand(sql); cmd.Connection = conn; if (sps != null) { cmd.Parameters.AddRange(sps); } string r = ""; object l = cmd.ExecuteScalar(); if (l != null) r = l.ToString(); cmd.Connection.Close(); return r; }
public void TestSqlCe() { var insertSql = "insert into Test (TestColumn) values ('foo')"; ExecuteCommand(insertSql); var query = "select count(*) from Test"; int count = 0; using (var connection = new SqlCeConnection(_connectionString)) { connection.Open(); var command = new SqlCeCommand(query, connection); count = (int)command.ExecuteScalar(); } Assert.AreEqual<int>(1, count); }
/* void fillCombo(){ var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Grupe.sdf"); using (var conn = new SqlCeConnection(connString)) { try { conn.Open(); var query = "SELECT * FROM grupe"; var command = new SqlCeCommand(query, conn); SqlCeDataReader myReader; myReader = command.ExecuteReader(); while (myReader.Read()) { string sName = myReader.GetString(0); comboBox1.Items.Add(sName); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } */ private void button1_Click(object sender, EventArgs e) { if (textBox1.Text.Length != 0) { var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Grupe.sdf"); using (var conn = new SqlCeConnection(connString)) { try { conn.Open(); var query = "SELECT COUNT(*) FROM grupe "; var command = new SqlCeCommand(query, conn); var dataAdapter = new SqlCeDataAdapter(command); var dataTable = new DataTable(); dataAdapter.Fill(dataTable); var nume = textBox1.Text; //checks if there's the searched record is in the db. int infoCount = (int)command.ExecuteScalar(); if (infoCount > 0) { Grupa form = new Grupa(nume); form.Show(); } else { MessageBox.Show("Nu exista un angajat cu acest nume"); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } else { MessageBox.Show("Nu ai introdus nimic pentru a fi cautat !"); } }