string connectionString = null;
SqlConnection cnn;
connectionString = "Server = " + ondegerler.SQLBAGLANTISI_KAYNAK + "; Database = " + ondegerler.SQLBAGLANTISI_VERITABANI + "; User Id = " + ondegerler.SQLBAGLANTISI_KULLANICI + "; Password = " + ondegerler.SQLBAGLANTISI_PAROLA;
FileInfo file = new FileInfo(Application.StartupPath + "\\tbl_cariler.txt");
string script = file.OpenText().ReadToEnd();
cnn = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter(script, cnn);
DataTable dt = new DataTable();
da.Fill(dt);
gridControl1.DataSource = dt;
gridView1.Columns[1].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;
gridView1.Columns[1].SummaryItem.DisplayFormat = "Adet = {0}";
gridView1.Columns["Bakiye"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gridView1.Columns["Bakiye"].SummaryItem.DisplayFormat = "Toplam = {0}";
gridView1.BestFitColumns();
Category: Sql
Python ms sql servere bağlan
sql server kütüphanesi yüklü deilse yükle pip install pyodbc
import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.1.200;DATABASE=TIGER;UID=testu;PWD=testp')
cursor = cnxn.cursor()
cursor.execute("select * from LG_INVOICE")
rows = cursor.fetchall()
for row in rows:
print row.DATE_
vb net devexpress grid data bind from sqlserver
Önce Forma bir adet GridControl ekle sonra forma tıkla ve aşağıdaki gibi olsun
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim Reader As StreamReader = File.OpenText("sql.txt")
Dim FileText As String = Reader.ReadToEnd()
Reader.Close()
MemoEdit1.Text = FileText
Dim connectionString As String = "Data Source=KLCTRCK;Initial Catalog=LKSDB;User ID=semsem;Password=xaaxpass"
Dim sql As String = MemoEdit1.Text
Dim connection As New SqlConnection(connectionString)
Dim dataadapter As New SqlDataAdapter(sql, connection)
Dim ds As New DataSet()
connection.Open()
dataadapter.Fill(ds, "Firma")
connection.Close()
GridControl1.DataSource = ds
GridControl1.DataMember = "Firma"
GridView1.OptionsView.ColumnAutoWidth = False 'Gridviewei otomatik genişlik ayarı
GridView1.OptionsView.BestFitMaxRowCount = -1
GridView1.BestFitColumns()
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'xls olarak kaydet
SaveFileDialog1.Filter = "XLS Files (*.xls*)|*.xls"
If SaveFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK _
Then
GridControl1.ExportToXls(SaveFileDialog1.FileName)
End If
End Sub
End Class
Delphi Get SQL SERVER INSTANCE NAMES
1er tane listbox, adodataset ve buton
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, IdBaseComponent, IdComponent, IdUDPBase, IdUDPClient, ActiveX,DB, ComObj, AdoInt,oleDB,
ADODB, Menus, cxLookAndFeelPainters, StdCtrls, cxButtons, cxControls,
cxContainer, cxEdit, cxTextEdit, cxMemo;
type
TForm1 = class(TForm)
ADODataSet1: TADODataSet;
cxButton1: TcxButton;
cboServers: TListBox;
procedure cxButton1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure ListAvailableSQLServers(Names : TStrings);
var
RSCon: ADORecordsetConstruction;
Rowset: IRowset;
SourcesRowset: ISourcesRowset;
SourcesRecordset: _Recordset;
SourcesName, SourcesType: TField;
function PtCreateADOObject(const ClassID: TGUID): IUnknown;
var
Status: HResult;
FPUControlWord: Word;
begin
asm
FNSTCW FPUControlWord
end;
Status := CoCreateInstance(
CLASS_Recordset,
nil,
CLSCTX_INPROC_SERVER or CLSCTX_LOCAL_SERVER,
IUnknown,
Result);
asm
FNCLEX
FLDCW FPUControlWord
end;
OleCheck(Status);
end;
begin
SourcesRecordset := PtCreateADOObject(CLASS_Recordset) as _Recordset;
RSCon := SourcesRecordset as ADORecordsetConstruction;
SourcesRowset := CreateComObject(ProgIDToClassID('SQLOLEDB Enumerator')) as ISourcesRowset;
OleCheck(SourcesRowset.GetSourcesRowset(nil, IRowset, 0, nil, IUnknown(Rowset)));
RSCon.Rowset := RowSet;
with TADODataSet.Create(nil) do
try
Recordset := SourcesRecordset;
SourcesName := FieldByName('SOURCES_NAME'); { do not localize }
SourcesType := FieldByName('SOURCES_TYPE'); { do not localize }
Names.BeginUpdate;
try
while not EOF do
begin
if (SourcesType.AsInteger = DBSOURCETYPE_DATASOURCE) and (SourcesName.AsString '') then
Names.Add(SourcesName.AsString);
Next;
end;
finally
Names.EndUpdate;
end;
finally
Free;
end;
End;
procedure TForm1.cxButton1Click(Sender: TObject);
begin
Screen.Cursor := crSQLWait;
cboServers.Items.Clear;
try
ListAvailableSQLServers(cboServers.Items);
finally
Screen.Cursor := crDefault;
end;
if cboServers.Items.Count>0 Then
cboServers.Itemindex:=0;
end;
end.
EXCEL 2013 CONNECT DBF FILE and QUERY BY TODAY
EXCELDE DATA->FROM OTHER SOURCES->OTHER ADVANCED->MICROSOFT JET 4 ÇİFT TIKLA-> ALL TABINA GEÇ->DATASOURCEU DBF DOSYASININ BULUNDUGU KLASORU EKLE->EXTENDED PROPERTIES=dBASE IV YAP-> OKE BAS->DBF DOSYASI CIKACAK LISTEDE CIFT TIKLA->FINISHE BAS->PROPERTIESA BAS DEFINITIONA TIKLA->COMMAND TYPE=SQL->SELECT * FROM DOSYAADI.DBF
select * from DATA
WHERE TARIH>=date()
DELPHIDE DBF VERITABANI DOSYASIYLA ISLEM YAPMA
ADOCONNECTION ADODATASET VE DATASOURCE EKLE
DATASOURCA TIKLA VE DATASET AYARINI ADODATASET1 YAP
ADODATASET1IN CONNECTIONINIDA ADOCONNECTION1 YAP
BUTTON VE GRID EKLE
KODLAR BU :
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData, cxDataStorage,
cxEdit, DB, cxDBData, Menus, cxLookAndFeelPainters, StdCtrls, cxButtons,
cxGridLevel, cxClasses, cxControls, cxGridCustomView, cxGridCustomTableView,
cxGridTableView, cxGridDBTableView, cxGrid, ADODB, Grids, DBGrids;
type
TForm1 = class(TForm)
ADODataSet1: TADODataSet;
ADOConnection1: TADOConnection;
DataSource1: TDataSource;
cxGrid1DBTableView1: TcxGridDBTableView;
cxGrid1Level1: TcxGridLevel;
cxGrid1: TcxGrid;
cxButton1: TcxButton;
procedure cxButton1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.cxButton1Click(Sender: TObject);
var
dbf_folder : string;
begin
dbf_folder:='C:\Users\JJJ\Downloads\Compressed';//set your dbf folder location here
ADOConnection1.LoginPrompt:=false;
ADOConnection1.ConnectionString:=Format('Provider=Microsoft.JET.OLEDB.4.0;Data Source=%s;Extended Properties=dBase IV;',[dbf_folder]);
try
ADOConnection1.Connected:=True;
ADODataSet1.CommandText:='Select * from SSS.dbf'; //make your SQL query using the name of the dbf file
ADODataSet1.Open;
while not ADODataSet1.eof do
begin
//do your stuff here
//ADODataSet1.FieldByName('').AsString
ADODataSet1.Next;
end;
except
on E : Exception do
ShowMessage(E.Message);
end;
cxGrid1DBTableView1.BeginUpdate;
cxGrid1DBTableView1.ClearItems;
cxGrid1DBTableView1.DataController.CreateAllItems;
cxGrid1DBTableView1.EndUpdate;
cxGrid1DBTableView1.Columns[0].Summary.FooterKind:=skCount;
cxGrid1DBTableView1.ApplyBestFit();
end;
end.
sqlde kusuratlı rakamlarda nokta ve sonrasını silme
SELECT
cast(((SUM(CASHTOT.DEBIT)-SUM(CASHTOT.CREDIT)))as int) AS NAKITTAHSILAT,CASHTOT.CARDREF AS AMBAR
FROM
LG_114_01_CSHTOTS CASHTOT WITH(NOLOCK, INDEX = I114_01_CSHTOTS_I2)
WHERE
(CASHTOT.CARDREF = 80) AND (CASHTOT.TOTTYPE = 2) AND (CASHTOT.DAY_ > -1)
GROUP BY CASHTOT.CARDREF
Tablo isimlerini listboxa almak delphi
procedure TForm1.cxButton1Click(Sender: TObject);
var
tablolar:Tstrings;
begin
tablolar := TStringList.Create;
try
ADOConnection1.GetTableNames(tablolar);
listbox1.Items.Assign(tablolar);
finally
tablolar.Free
end
end;
Calisan veritabanını durdurmak (dosya kullanımda hatası)
http://www.4shared.com/zip/mZdLnQCK/ProcessExplorer.html?
File handle search de calisan dosyayı ve bulunca cift tıkla kapat ve sağ tıkla o isme ve close de
Cxgrid filter by code
procedure TForm1.Button3Click(Sender: TObject);
var
AItemList: TcxFilterCriteriaItemList;
begin
drIlaclistesiDBTableView1.DataController.Filter.BeginUpdate;
try
drIlaclistesiDBTableView1.DataController.Filter.Root.Clear;
AItemList := drIlaclistesiDBTableView1.DataController.Filter.Root.AddItemList(fboAnd);
AItemList.AddItem(drIlaclistesiDBTableView1ILACADI, foLike, '*%', '*%');
AItemList.AddItem(drIlaclistesiDBTableView1MIKTARI, foGreater, '0', '0');
finally
drIlaclistesiDBTableView1.DataController.Filter.EndUpdate;
drIlaclistesiDBTableView1.DataController.Filter.Active := true;
end;
end;
Blog of Sem 
